February 24, 2009 at 11:43 am
Converting some complex VB embedded SQL to a stored procedure, I ran across a small problem that seems to elude me. Embedded in several cursor loops (can't be avoided) is a bit of code that is simple in VB but somewhat difficult with T-SQL. I need to use a composite column name consisting of the letter 'q' with an integer value from a variable to return the results of the q-numbered column. As an example, if the number variable (@qNum) value is 2, I need the value stored in column q2 from a particular table. However,
SELECT ('q' + cast(@qNum as varchar) FROM table_name where ID = @ID
only returns the literal 'q2' and not the contents of column q2. Likewise creating a third variable to contain the column name and inserting it into the SELECT only produces the same result. What is the easiest way to dynamically get the expected results.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
February 24, 2009 at 11:49 am
You'll need to use dynamic SQL to accomplish what you're talking about. Build the query string, execute it.
For example:
declare @SQL varchar(max);
select @SQL = 'select q' + @Col + ' from dbo.MyTable';
exec(@SQL);
There are things to watch out for in this case, since dynamic SQL can open up all kinds of security issues if you don't handle it correctly. Make sure to research it first, before you use it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2009 at 1:11 pm
Thanks! That works, but now I need to know how to embed it within some static SQL code such as in:
SELECT @SQL = 'max(rtrim(select q' + cast(@qQuestion as varchar) + ')) from mshq_ivr1
where uniqueid = ' + cast(@curMaster as varchar)
SET @PAns = (select dis
from mshq_ivr_a1
where plannbr = @qPlanNum
and question = @qQuestion
and rtrim(answer) = (exec @sql)
Which produces an Incorrect syntax near the keyword 'exec'.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
February 24, 2009 at 1:24 pm
Your open parenthesis is in the wrong place. (exec @sql) should be exec (@sql).
Greg
February 24, 2009 at 1:31 pm
That was a typo -- sorry. However ... = exec (@sql) still causes the stored procedure parser to produce the same Incorrect syntax near the keyword 'exec' error.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
February 24, 2009 at 1:59 pm
Try replacing all of that with:
SELECT @SQL =
'select
dis
from
mshq_ivr_a1
where
plannbr = ' + cast(@qPlanNum as varchar(10)) + '
and
question = ' + cast(@qQuestion as varchar(10)) + '
and
rtrim(answer) =
(select max(rtrim(q' + cast(@qQuestion as varchar) + '))
from mshq_ivr1
where uniqueid = ' + cast(@curMaster as varchar) + ')';
exec (@SQL);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 24, 2009 at 2:22 pm
No good. The code I sent is run many times within a loop in which the result of the code is used to feed other code. The SQL Sever parser does not seem to accept any statement assigning the resultset of exec(@SQL) to a variable or other SQL statements. However, I am considering using a call to a second stored procedure, which may resolve this issue.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
February 24, 2009 at 2:26 pm
Check out sp_executesql. You can return values as output parameters from dynamic SQL with that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 8:01 am
Thanks, the sp_executesql was what I needed. Actually my real problem was that I was trying to do something like: @var = execute sp_executesql @sql to assign the resultant to a variable, when what I needed was execute @var = sp_executesql @sql. the second variation works where the first created the syntax error.
However, now I have a new problem when testing the code, that is each interation of the code creates seperate result set for each Execute statement causing an error in exceeding the maximum number of result sets that can be displayed. I do not need a result set generated for each iteration of the execute when I am assigning the text returned to variable which is added to the final result set.
This is very simple with static SQL assignments, so how do I get the resultant text of a dynamic SQL without generating a seperate result set?
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
February 25, 2009 at 8:03 am
I guess I'm not clear on what you're asking. If you execute a select statement, you'll get a resultset on the screen.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 8:16 am
If I understand correctly, you want one total result set instead all of the individual ones. If your iteration is happening in the stored procedure, you can insert them into a temp table instead of selecting them. Then at the end of the stored procedure SELECT * FROM TempTable. If the iteration is happening in your application, you would have to something similar in the application like put the results into a data table.
Greg
February 25, 2009 at 8:17 am
In static SQL I can declare a variable assign it to the results of a SELECT statement then use then employ the variable within another SELECT, WITHOUT generating a displayable result set for the inner SELECT. i.e.,
@var = (SELECT textcol FROM tablename WHERE id = ###)
However, because of the comosite column name used in the inner SELECT I have to resort to using a dynamic SQL call using an EXECUTE statement which generates a displayed result set for each call.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
February 25, 2009 at 8:24 am
I see what you are saying. When you keep going down the same road it get difficult to shift gears and swith gears to try a different approach. And what you describe is exactly what I am doing by creating a temp table with serveral thousand records with a single column based on the results of this inner query. I will look at reworking it.
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply