syntax error in sql server store prodedure

Question

1.00/5 (1 vote)

See more:

hii all
i am getting error in my sql stored procedure.. please help me..

SQL

SET @vQuery = 'select dropout_count, NE_count from (select count(Child.cname) as dropout_count from ' + @ChildTblName + ' As Child WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON
Child.barcode = Family.barcode where Child.distt = ' + @DistrictID + ' and droupout between 01 and 23)a
CROSS JOIN (select count(Child.cname) as NE_count from ' + @ChildTblName + ' As Child WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON
Child.barcode = Family.barcode where Child.distt = ' + @DistrictID + ' and nereason between 01 and 24)b'

in this both field nereason and droupout are nvarchar.. when i run this as a query not in sp.. then nereason value 1 and 24 needs to quoted like this.. '01' and '24'.. but in sp it's not working..

it's beyond my mind.. and give me correct syntax..

the error is:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AND'.

thanks in advance

Posted 28-Apr-14 1:58am

bindash

Updated 29-Apr-14 4:27am

phil.o

v4

Add a Solution

Comments

thatraja 28-Apr-14 8:05am

what's the error?

Mike Meinz 28-Apr-14 8:15am

Is droupout the correct name of a column or should it be dropout?

bindash 28-Apr-14 8:18am

yes droupout is correct..

Mike Meinz 28-Apr-14 8:19am

You haven't provided the exact error message. That might help!

bindash 28-Apr-14 8:20am

sorry .. the error is:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AND'.

[no name] 28-Apr-14 8:24am

and "and 23)a"

2 solutions

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)

Mike Meinz · Answer 1 · 2014-04-28T02:26:00

Solution 2

REVISED SOLUTION - Revision #1

You should Cast Varchar values as Integers so that they can be compared to integer literal values.

SQL

SET @vQuery = 'select dropout_count,NE_count from (select count(Child.cname) as dropout_count from ' + @ChildTblName + ' As Child WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON
  Child.barcode=Family.barcode  where  Child.distt = ' + @DistrictID + ' and  CAST(droupout as INT) between 01 and 23)a
  CROSS JOIN (select count(Child.cname) as NE_count from ' + @ChildTblName + ' As Child WITH (NOLOCK) INNER JOIN ' + @FamilyTblName + ' AS Family WITH (NOLOCK) ON
  Child.barcode=Family.barcode  where  Child.distt = ' + @DistrictID + ' and CAST(nereason as INT) BETWEEN 01 and 24)b'

Posted 28-Apr-14 2:26am

Mike Meinz

Updated 29-Apr-14 4:27am

v7

Comments

bindash 29-Apr-14 8:16am

thanks for reply.. but it's not working...

Mike Meinz 29-Apr-14 9:09am

Please explain in more detail. Just saying "Not working" does not provide any information about an error message or incorrect results.

Mike Meinz 29-Apr-14 10:13am

I revised Solution 2 to show use of CAST function.

Using CAST(nereason as INT) instead of just nereason will ensure a numeric compare instead of an alphanumeric compare. Also, CAST(dropout as INT) instead of just dropout

phil.o 29-Apr-14 10:30am

'Not working' is not a valid issue description. Please indicate:
- the new code you have used
- the result (eventually error message, exception, etc...)

phil.o 29-Apr-14 10:30am

Upvoted as this is a perfect valid answer.

_Asif_ · Answer 2 · 2014-04-28T04:02:00

Solution 3

It seems that @DistrictID is empty. Check this variable. You can also debug your sql by simply printing the @vQuery variable and check the generated sql.

Posted 28-Apr-14 4:02am

_Asif_

Comments

bindash 29-Apr-14 8:23am

i have check..the query is working in like this seperately like this nereason between '01' and '24'.. it's not working in stored procdure

Mike Meinz 29-Apr-14 9:49am

Based on your comment, I revised Solution 2 to show use of CAST function to convert a Varchar column value to an Integer so that the value can be properly compared to integer literals.

_Asif_ 29-Apr-14 9:11am

Can you print the query that is value of @vQuery and show us the result