sql server store prodedure中的语法错误 [英] syntax error in 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屋！