Variable with multiple values?
-
Hello, I'm using sql server and I want to use a parameter in an IN clause in the command. e.g. select * from tableX where ID in @test (The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') ) No matter how I declare or fill that variable I don't succeed. Can anybody help please? Thank you, Wim
-
Hello, I'm using sql server and I want to use a parameter in an IN clause in the command. e.g. select * from tableX where ID in @test (The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') ) No matter how I declare or fill that variable I don't succeed. Can anybody help please? Thank you, Wim
-
Thanks for your quick reply, but I don't want to use a stored procedure. Just a simple statement with a sql variable. declare @test varchar(600) select @test = and do the select statement like this: select * from tableX where ID in @test (The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') )
-
Hello, I'm using sql server and I want to use a parameter in an IN clause in the command. e.g. select * from tableX where ID in @test (The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') ) No matter how I declare or fill that variable I don't succeed. Can anybody help please? Thank you, Wim
@test is a variable replaced at query execution. Using your 'expanded' example, the resultant query would be looking for all rows where ID = "('ID1','ID2','ID3')". The IN would result to equal because there is only one element in the clause (@test). SQL variables can only replace one element, not a list of more than one. You can get around this with dynamic SQL generation which uses your variable to build a new SQL string but, you indicate you don't want to use a Stored Proc. Solution: Drop the variable and rewrite your code to build the full statement with a comma deliminated IN clause.
-
@test is a variable replaced at query execution. Using your 'expanded' example, the resultant query would be looking for all rows where ID = "('ID1','ID2','ID3')". The IN would result to equal because there is only one element in the clause (@test). SQL variables can only replace one element, not a list of more than one. You can get around this with dynamic SQL generation which uses your variable to build a new SQL string but, you indicate you don't want to use a Stored Proc. Solution: Drop the variable and rewrite your code to build the full statement with a comma deliminated IN clause.
-
Hello, I'm using sql server and I want to use a parameter in an IN clause in the command. e.g. select * from tableX where ID in @test (The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') ) No matter how I declare or fill that variable I don't succeed. Can anybody help please? Thank you, Wim
http://www.projectdmx.com/tsql/sqlarrays.aspx The link above gives you three examples of how you could do this.