我写了一个存储过程
定义的参数
CREATE PROCEDURE LS_JJK
@day int,
@time varchar(10),
@QStime varchar(10),
@JStime varchar(10),
@QSKno varchar(16),
@JSKno varchar (16),
@where varchar(200)
AS里面有这样一句话
insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj from ##tmp_tt
where 1=1 +@where 我执行的语句
exec LS_jjk 31,'2009-01-31','20090101','20090131','6226905400022216','6226905400022294','and sd>15'我每次执行的时候他都报错
将 varchar 值 'and sd>15' 转换为数据类型为 int 的列时发生语法错误。
为什么呢
定义的参数
CREATE PROCEDURE LS_JJK
@day int,
@time varchar(10),
@QStime varchar(10),
@JStime varchar(10),
@QSKno varchar(16),
@JSKno varchar (16),
@where varchar(200)
AS里面有这样一句话
insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj from ##tmp_tt
where 1=1 +@where 我执行的语句
exec LS_jjk 31,'2009-01-31','20090101','20090131','6226905400022216','6226905400022294','and sd>15'我每次执行的时候他都报错
将 varchar 值 'and sd>15' 转换为数据类型为 int 的列时发生语法错误。
为什么呢
--1=1后面缺少空格吧,改成这样看看
insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj from ##tmp_tt
where 1=1+' '+@where
(
filed1 type1,
...
filedn,typen
)declare @sql varchar(max)
set @sql = 'insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj from ##tmp_tt
where 1=1' + @whereexec(@sql)这样可以吧?
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj from ##tmp_tt
where 1=1' +@where
类似这样来做,不然sql会把你的语义理解为1=1+@where,一个数值型的等式,把@where理解为数字。就不对了。所以你必须在存储过程里拼接字符串,然后执行
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj from ##tmp_tt
where 1=1 +@where
,然后exce(@str)
注意空格,1=1后面要有空格
exec LS_jjk 31,'2009-01-31','20090101','20090131','6226905400022216','6226905400022294',' and sd>15'
--你的存储过程的创建有问题 带参数的存储过程要主义格式 尤其exec (@strSQL) 要有括号
--存储过程的创建
create PROCEDURE Test_p
@where varchar(100)
AS
declare @strSQL NvarChar(200)
set @strSQL = N'select * Table where 1=1 ' + @where
exec (@strSQL) --执行的语句
exec Test_p 'and sd>15'
报错
第 73 行: 'insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh' 附近有语法错误。我在存储过程里是这么写的
exec 'insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj
from ##tmp_tt
where 1=1 ' +@atwhere
--注,日期和天数应该是动态的,在TT表中再做条件。
CREATE PROCEDURE LS_JJK
@day int,
@time varchar(10),
@QStime varchar(10),
@JStime varchar(10),
@QSKno varchar(16),
@JSKno varchar (16),
@atwhere varchar(200)
AS
IF OBJECT_ID(N'tempdb..##Tmp_TT') IS NOT NULL Drop Table ##Tmp_TT
IF OBJECT_ID(N'tempdb..##Tmp_TTL') IS NOT NULL Drop Table ##Tmp_TTL Create Table ##Tmp_TT--基本表
(
Card_No varchar(200),
Kh_Code varchar(200),
Kh_Name varchar(200),
Kh_Tel varchar(200),
Kh_Sex varchar(200),
Kh_Address varchar(200),Sd decimal(18,2), --时点
Rj decimal(18,2), --日均
)
Create Table ##Tmp_TTL--最终表
(
Card_NoL varchar(200),
Kh_CodeL varchar(200),
Kh_NameL varchar(200),
Kh_TelL varchar(200),
Kh_SexL varchar(200),
Kh_AddressL varchar(200),SdL decimal(18,2), --时点
RjL decimal(18,2), --日均
)
--日均注,31是天数,应该是动态的
Insert Into ##Tmp_TT(Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Rj) SELECT sascno,Zd_Kh.Kh_Code,Zd_Kh.Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,sum(zhyermb)/@day
from Zd_Kh,DsCkRjb,Zd_Yw2,Zd_Yw3,Mis_Zx_Impo.dbo.scsaa
where Zd_Kh.Kh_Code=DsCkRjb.DSCUNO
and saacno=DsCkRjb.DSZH
and Dsckrjb.YWZL=Zd_Yw2.Xl_Code
and Zd_Yw2.lb_Code='102'
and Zd_Yw2.Xl_Code=Zd_Yw3.Xl_Code
And Rq Between @QStime And @JStime
and sascno between @QSKno and @JSKno
and Substring(Dsckrjb.DSZH,9,3)=Zd_Yw3.Km_Code
Group by Zd_Kh.Kh_Code,Zd_Kh.Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,sascno
--时点
Update ##Tmp_TT Set ##Tmp_TT.Sd=A.Sd from (SELECT sascno,sum(zhyermb) as Sd
from Zd_Kh,DsCkRjb,Zd_Yw2,Zd_Yw3,Mis_Zx_Impo.dbo.scsaa
where Zd_Kh.Kh_Code=DsCkRjb.DSCUNO
and saacno=DsCkRjb.DSZH
and Dsckrjb.YWZL=Zd_Yw2.Xl_Code
and Zd_Yw2.lb_Code='102'
and Zd_Yw2.Xl_Code=Zd_Yw3.Xl_Code
and sascno between @QSKno and @JSKno
and Substring(Dsckrjb.DSZH,9,3)=Zd_Yw3.Km_Code
And Convert(SmallDateTime,Convert(Varchar(10), Rq),126) =@time Group By sascno ) As A,##Tmp_TT
Where A.sascno=##Tmp_TT.Card_Noinsert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj
from ##tmp_tt
where 1=1 +@atwhere insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select '','99999999','总计','','','',sum(sdL),sum(rjL) from ##tmp_TTLselect * From ##Tmp_TTLgo
exec LS_jjk 31,'2009-01-31','20090101','20090131','6226905400022216','6226905400022294',
' and sd>15'
...
where 1=1 +@where
where 1=1 +@where 系统会认为是 where 1 = "1 + @where"
把后面这一块运算,字符+数字肯定就是提示你的那一个错误了啊
exec 当执行的只是一个字符串时候,要用exec(...),而不能使用 exec ...我在7楼写出来了
insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj
from ##tmp_tt
where 1=1 +@atwhere
--改成 在where后面(加上括号)
insert into ##Tmp_TTL(Card_NoL,Kh_CodeL,Kh_NameL,Kh_TelL,Kh_SexL,Kh_AddressL,SdL,RjL)
select Card_No,Kh_Code,Kh_Name,Kh_Tel,Kh_Sex,Kh_Address,Sd,Rj
from ##tmp_tt
where (1=1 +@atwhere )
不同意这个观点
怎么可能是 1="1 + @where"
它应该是把@where 转换成 int 然后和1相加,将这个所得和再和 =号前面的1进行比较