February 22, 2010 at 11:36 pm
Hello,
I have a strange problem here
SET @SQL = (SELECT Code
FROM tablecode where ( @COL= 'Y'))
SELECT @SQL
The result I get from this is NULL, where @COL is a variable , however when If i use
SET @SQL = (SELECT Code
FROM tablecode where ( U_key = 'Y'))
SELECT @SQL
with the column name as U_key it gives the correct result. Help me understand what's wrong with @COL ?
February 23, 2010 at 12:02 am
February 23, 2010 at 7:42 am
declare @sql nvarchar (200), @col varchar (100)
set @col = 'acct_id'
SET @SQL = 'SELECT acct_num FROM account where ' + @COL + ' = 1000'
print @SQL
EXEC(@SQL)
see above example , you will get required result if u put ur query dynamically
otherwise "@col" didnt get replaced with column name.
in your case "set @sql " didnt get any thing.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 26, 2010 at 2:20 am
martin.edward (2/22/2010)
Hello,I have a strange problem here
SET @SQL = (SELECT Code
FROM tablecode where ( @COL= 'Y'))
SELECT @SQL
The result I get from this is NULL, where @COL is a variable , however when If i use
SET @SQL = (SELECT Code
FROM tablecode where ( U_key = 'Y'))
SELECT @SQL
with the column name as U_key it gives the correct result. Help me understand what's wrong with @COL ?
It is because you might be having many rows in tablecode and when you pass 'Y' in the variable @col, the condition matches and it returns all the rows from tablecode. Ideally you can store one value of code in @SQL
So, you can try
Declare @col as char(1)
set @col = 'y'
SET @SQL = (SELECT Code
FROM tablecode where U_key = @COL)
SELECT @SQL
This will work only if there is a single record in tablecode for the condition U_key ='Y'. If there are many records you can take Top 1
Declare @col as char(1)
set @col = 'y'
SET @SQL = (SELECT top 1 Code
FROM tablecode where U_key = @COL)
SELECT @SQL
--Divya
February 26, 2010 at 2:22 am
🙂
--Divya
February 27, 2010 at 7:15 am
wschampheleer (2/23/2010)
What are you trying to do? What is the value of @Col? If you want @Col to represent the name of the column on which to filter, you'll either have to use a case statement (preferred) or dynamic SQL.
What would be the advantage of the CASE statement?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2010 at 12:22 pm
Paul White (2/27/2010)
wschampheleer (2/23/2010)
What are you trying to do? What is the value of @Col? If you want @Col to represent the name of the column on which to filter, you'll either have to use a case statement (preferred) or dynamic SQL.What would be the advantage of the CASE statement?
Easier to write and maintain.
February 27, 2010 at 7:37 pm
wschampheleer (2/27/2010)
Easier to write and maintain.
Seriously? That hasn't really been my experience. CASE statements are a great way to avoid using a useful index, of course 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2010 at 12:35 am
Paul White (2/27/2010)
wschampheleer (2/27/2010)
Easier to write and maintain.Seriously? That hasn't really been my experience. CASE statements are a great way to avoid using a useful index, of course 😛
Paul,
I based myself on this article 'Dynamic Search Conditions in T-SQL' written by someone who is far more experienced on this than myself. However, I didn't pay attention to the fact that this question is posted in the SQL 2005 section of the forum whereas the article is specifically targeted at SQL 2008. I should also correct my initial reply where I referred to a case statement. What I actually meant was something like
WHERE (o.OrderID = @orderid OR @orderid IS NULL)
AND (o.OrderDate >= @fromdate OR @fromdate IS NULL)
AND (o.OrderDate <= @todate OR @todate IS NULL)
AND (od.UnitPricwwee >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
...
Anyway, the conclusion of the article is:
You have now seen several ways to implement this kind of searches, both in dynamic SQL and static SQL. You have seen that in SQL 2008, it's possible to get good performance no matter if you use static or dynamic SQL, but to get static SQL to perform well, you have to pay the price of compiling the query each time, which on a busy system could be expensive. You have also seen that for very simple searches with very few search conditions, the best may be to keep it simple and use IF statements.
You have seen that a solution for static SQL can be very compact and easy to maintain. The solution for dynamic SQL is more verbose, and takes some more power to maintain. But you have also gotten glimpses of that if the requirements for the search problem increase in complexity, dynamic SQL is a more viable solution. And again, you need at least CU5 of SQL 2008 SP1 to be able to use OPTION (RECOMPILE) in this way.
February 28, 2010 at 12:52 am
You can find the pre-2008 SP1 CU5 version of Erland's article at another location on his site: Dynamic Search Conditions in T-SQL
The conclusion of the article is:
And let me stress once more that, no matter whether you go for dynamic or static SQL, you should test your procedure for all in input parameters and preferably some combinations too, both for correctness and for performance. And to test the performance, you need data which resembles production data. If you expect to have ten million orders and 50.000 customers in production, you cannot play with a toy database at the size of Northwind or even Northgale.
😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply