sql server store prodedure中的语法错误 [英] syntax error in sql server store prodedure

查看:125
本文介绍了sql server store prodedure中的语法错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

hii all

i在我的sql存储过程中收到错误..请帮帮我..



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

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'





这两个字段的nereason和droupout是nvarchar ..当我把它作为查询运行而不是在sp ..然后nereason值1和24需要引用像这样的''01'和'24'..但在sp它不工作..



超出我的想法..并给我正确的语法..



错误是:



消息156,等级15,状态1,行4

关键字'AND'附近的语法不正确。







t预先hanks



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

推荐答案

修订后的解决方案 - 修订版#1



你应该将Varchar值转换为整数,以便可以将它们与整数文字值进行比较。



REVISED SOLUTION - Revision #1

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

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'


似乎@DistrictID为空。检查此变量。您也可以通过打印@vQuery变量并检查生成的sql来调试您的sql。
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.


这篇关于sql server store prodedure中的语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆