Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
09999
Starting Member
5 Posts |
 Posted - 2011-09-20 : 09:51:31
|
| When i run below query I am getting the required resultselect * from xxxx where Dept IN('a','b','c')but when I change to below code I am not getting any results? I am not sure how the sql execution goes on. Any ideas what is wrong in below code. The value of @department in where condition changes based what is passed. DECLARE @Department VARCHAR(25)='PLANT' IF @Department='PLANT' BEGIN SET @Department='''a'',''b'',''c''' END select * from XXXX whee dept IN (@Department) |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2011-09-20 : 10:04:45
|
| that's because it doesn't work that way.in first case the Dept IN('a','b','c') you have 3 strings but in the second you have just one string with extra ' in it. so in your second case the IN is only evaluating against a single string.read up on table valued parameters in SQL Server 2008 and above to give you an idea of how to tackle this.( http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters ) once you try it out show us what you tried and we'll be able to help you further.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!SQL Server MVP |
 |
|
|
09999
Starting Member
5 Posts |
Posted - 2011-09-20 : 10:33:20
|
| .....second you have just one string with extra ' in it.....I don't see any extra '. when used print statement to what is in @department I got 'a','b','c'.And I am not sure how do we use user defined table value parameter over here? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2011-09-20 : 11:00:23
|
| i'm sorry, i meant to say Table variables, not table valued parameters. http://www.sqlteam.com/article/using-table-variablesyes you see 'a','b','c' when you print but that is a single string in one row and one column.in your first example 'a','b','c' are actually 3 rows in one column.so basically in your first example you're doing where Dept IN (select 'a' union all select 'b' union all select 'c') <- 3 single char valuesand in your second example you're doingwhere Dept IN (select '''a'',''b'',''c''') <- one 11 char value.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!SQL Server MVP |
|
|
|
09999
Starting Member
5 Posts |
Posted - 2011-09-20 : 12:35:06
|
| Thank you for the explaining the difference between the first and second query. |
|
|
|
|
|
|
|
|
query execution