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 |
|
blakmk
Starting Member
45 Posts |
 Posted - 2003-08-15 : 11:15:14
|
| I have a stored procedure which contains the following sql statement:IF ( 1 <> (SELECT count(name) FROM DatabaseInfo WHERE Name = 'StaticDataType' AND Version IN (@l_StaticDataType)))BEGIN SELECT 'incorrect static data type'ENDThe problem is that this statment always behaves as if the output is not 1. According to the debugger the value of @l_StaticDataType is:'EC' so when I run the same query from query analyzer replacing @l_staticDataType with the correct value 'EC' , the query returns a value of 1 as expected. Is there are problem with using variable substitution in this way in T-Sql? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-15 : 11:20:03
|
| Why isn't it written as:IF ( 1 <> (SELECT count(name) FROM DatabaseInfo WHERE Name = 'StaticDataType' AND Version = @l_StaticDataType ))BEGIN SELECT 'incorrect static data type'END???? Why are you using "IN" ?- Jeff |
 |
|
|
blakmk
Starting Member
45 Posts |
Posted - 2003-08-15 : 11:26:50
|
| Im using in because the variable @l_StaticDataType could contain one or more values. These would be stored in the format: 'EC','DB','TR' |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-15 : 11:58:17
|
| There's your problem. THe IN operator does not work that way with a single variable.Search this site (esp. the articles) for using the IN operator with a CSV (comma seperated values).do you see thatDeclare @sset @s= '1,2,3,4'is 1 single value, NOT a list of 4 values? Thus, IN (@s) will not work ?I really wish SQL would give a warning when you use the IN operator without a list ....- Jeff |
|
|
|
|
|
|
Stored procedure query and local variable problem