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 |
|
jarobert27
Starting Member
2 Posts |
 Posted - 2011-02-14 : 10:53:14
|
| DECLARE @propertytype varchar(100)Select @propertytype = '''House'',''Condo'''Query 1:select * from transactions where Type in ('House','Condo')Query 2:select * from transactions where Type in (@propertytype)SELECT @propertytype evaluates to 'House','Condo' |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-14 : 11:03:26
|
| The simple answer is that you can't use a variable like that in an IN clause. Many people use the fnParseValues function found on this site to turn a delimited string into a table, which you can then join on.JimEveryday I learn something that somebody else already knew |
 |
|
|
javad_ghasemiro
Starting Member
5 Posts |
Posted - 2011-02-14 : 12:01:14
|
| Try this :DECLARE @propertytype varchar(100)Select @propertytype = '''House'',''Condo'''declare @SQL as nvarchar(200 ) Set @SQL =N'select * from transactions where Type in ('+ @propertytype + ')'exec sp_executeSQL @SQL , N'@propertytype varchar(100)' , @propertytypeJavad Ghasemi |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-02-14 : 13:15:54
|
| Nice piece of sql injection vulnerable code. No, that's not parameterised no matter how it looks. The resultant piece of sQL (in @SQL) has no parameters in it, the declaring and passing of propertytype does nothing and can be removed without changing the behaviour in the slightest.No need for dynamic SQL, lots of split functions are available.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Query 1 returns rows but Query 2 does not. Why?