dynamic SQL query contains where claues with operator in

Question

1.00/5 (1 vote)

See more:

i have query like which is shown below

SELECT studentname from [Student]
where [id ] in + '(' + @rollnumber + ')' .

But while executing i am getting the error as invalid syntax near @rollnumber
can u help me

Posted 20-Apr-14 19:21pm

babege

Updated 20-Apr-14 19:22pm

v3

Add a Solution

Comments

Peter Leow 21-Apr-14 1:51am

What are the data type and value for @rollnumber?

babege 21-Apr-14 2:08am

@roll number is data type string of values separated by comma
example
@rollnumber ='4197','4195'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hitesh Varde · Answer 1 · 2014-04-20T19:34:00

Solution 1

Hi,

Please try below query, Let me know if it helps.

SQL

declare @rollnumber int
declare @str nvarchar(max)
set @rollnumber =1

set @str= 'SELECT studentname from [Student] 
where [id ] in  (' + cast(@rollnumber as varchar)+ ')'

exec sp_executesql @str

Thanks,
Hitesh Varde

Posted 20-Apr-14 19:34pm

Hitesh Varde

Comments

babege 21-Apr-14 2:37am

Thanks for the reply
hi i can run this query but result set contains count=zero instead of count =1

babege 21-Apr-14 3:20am

i am giving the input like this
@rollnumber ='4197','4195'

babege 21-Apr-14 3:51am

incorrect syntax
4197 is in between double quote or two single quotes .
if i use two single quotes then syntax error
SET @rollnumber =' ''4197'' ' + ',' + ' ''4195'' ' error

if we use double quotes then no result instead of two

SET @rollnumber =' "4197" ' + ',' + ' "4195" ' gives zero result set instead of two

ccalma 21-Apr-14 4:56am

I tried the code and it runs correctly.

declare @rollnumber VARCHAR(max)
declare @str nvarchar(max)
set @rollnumber ='''12345''' + ',' +'''54321'''

set @str= 'SELECT last_name from tbl_student
where [student_id ] in (' + cast(@rollnumber as varchar)+ ')'

exec sp_executesql @str

ccalma 21-Apr-14 5:00am

yes, 2 rows has been returned.

babege 21-Apr-14 5:16am

but i try to call the query from front end like java code with dynamic parameter for the @rollnumber='''12345''' + ',' +'''54321''' result set goes to zero

ccalma 21-Apr-14 5:28am

Try to declare a string then pass it as a parameter.
test = "'12345','54321'";

babege 21-Apr-14 5:42am

no result count=zero

babege 22-Apr-14 1:24am

i got the resilt thanks for the help..

ccalma 22-Apr-14 2:58am

what did you do?

babege 22-Apr-14 10:05am

connection mistake in database

Hitesh Varde 21-Apr-14 3:10am

Hi,

declare @rollnumber varchar(max)
declare @str nvarchar(max)
set @rollnumber ='4197'',''4195'

set @str= 'SELECT studentname from [Student]
where [id ] in (''' + @rollnumber + ''')'

exec sp_executesql @str
Try above query, let me know if it helps?
Can you please provide some sample data and expected result to provide you with suitable solution?
Thanks,
Hitesh Varde

babege 21-Apr-14 3:19am

The input value for the rollnumber given from the front end( like from java)
@rollnumber ='4197','4195'

the out put in query editor is

Studentname
------------------------------

(0 row(s) affected)

insterd of two rows

Hitesh Varde 21-Apr-14 4:40am

Can you provide insert script for your data and expected result (Not the result you are getting in query editor)?

babege 21-Apr-14 5:44am

declare @str nvarchar(max)

set @str= 'SELECT [studentname] from [student]
where [rollNumber] in (' + cast( @rollnumber as varchar) + ')'

exec sp_executesql @str

babege 21-Apr-14 5:45am

"'12345','54321'" as roll number
expecting result as
shaan
isac

Hitesh Varde 21-Apr-14 7:22am

Got what you want?

babege 21-Apr-14 7:43am

no didn't got

babege 22-Apr-14 1:22am

i got the result...