November 28, 2008 at 4:22 am
i have dynamic sql in my reporing service report. now i want to use "IN" operator in my dynamic query.i wrote the sample query in below.
DECLARE @title VARCHAR(200)
DECLARE @user VARCHAR(200)
DECLARE @Query VARCHAR(4000)
SET @title1=@title
SET @user1=@user
SELECT @Query = 'select * from table1 where titlename='''+ @title1 +''' and username in (' + @user1 + ') '
EXEC(@Query)
here i use parameters namely title(textbox) and user(multivalue). In title i pass title="sometitle" and user as
(user1,user2,user3,user4,user5) etc.
when i pass multivalue parameter as string it shows error like
"Incorrect syntax near ,"
can any one help me how to pass multi value parameter as string inside the dynamic query like this?
reg,
sundar srini
November 28, 2008 at 4:50 am
The string for the IN needs quotes around all the values
(user1,user2,user3,user4,user5)
Not going to work as those are strings. You're also duplicating the brackets.
Pass the user list as ''user1'',''user2'',''user3'',''user4'',''user5''
Two final words: SQL Injection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply