SQL Server Performance Forum – Threads Archive
Help on Querylike in VB application, i’ve a recordset having fields field1,field2,field3
and if i say i=1(or any dynamic value) var_str=rs("field"&i) it will fetch field1 or either field2 value from recordset into var_str depending on
the value of i. how the same thing can be implemented in SQL SP as ive done
open cursor cur_marks
fetch next from cur_marks into @field1,@field2,@field3 now as said above
if i=1 then i want @field1 value
depending on value of i I need that corresponding variable value.
but @var_str stores ‘@field1’ and not the value stored in @field1 variable.
Unfortunately T-SQL does not support run-time resolution of field names in a basic SELECT statement. You can of course use dynamic SQL, where you build up the query statement in a VARCHAR variable and then EXEC the statement. But when you need to take a certain value and then use it elsewhere, that isn’t much help. What you can also do is to use a CASE construct using a parameter that decides which column’s value is returned: SELECT field1, field2, CASE @i WHEN 1 THEN field1 WHEN 2 THEN field2 [WHEN … THEN …] END AS SpecialColumn
FROM cur_remarks Main problems are that the construct can become rather long, and if the various columns are of a different type then you have to CAST everything to VARCHAR. You can also have a construct like:
CASE WHEN @1 = 1 then field1 ELSE (CASE WHEN @2 = 1 THEN field2 ELSE field3 END) END Since you mention VB, note that the T-SQL syntax is rather different than VB’s "Select Case" syntax!
I knew its not possible in SQL but still i thought lets give my problem a try by SQL Experts.
If you have to do it in a cursor, then use this:
SET @var_str = CASE i WHEN 1 THEN @field1 WHEN 2 = @field2 WHEN 3 THEN @field3 END