declare @LCode varchar(3) declare @AutoContn bit declare @AutoAIMin tinyint declare @LikeStr varchar(10) declare @SqlStr as varchar(200) declare @tdate as datetime/* 取引导码、是否年续号、流水号位数 */ set @LCode='123'--(SELECT LeadCode FROM System_BBills WHERE (BBillNO = @NOName) ) set @SqlStr='@LCode + Right(Year(@Tdate),2)'--从表中取到的 sql语句(我在一表中存储了一段SQL语句如:@LCode + Right(Year(@Tdate),2)) set @tdate = '2003-02-10'DECLARE @strSQL NVARCHAR(1000),@COUNT INT
SET @strSQL = N'SELECT @COUNT= '+ @SqlStr EXEC SP_EXECUTESQL @strSQL,N'@COUNT INT OUT,@Lcode varchar(3),@tdate datetime',@COUNT OUT,@lcode,@tdate SELECT @COUNT /*----------- 12303 */
set @SqlStr= replace(replace(@SqlStr,'@LCode',''''+@LCode+''''),'@Tdate',''''+convert(varchar(10),@Tdate,120)+'''') set @SqlStr=@SqlStr+' INTO ##tmp00' exec sp_executesql @SqlStr set @LikeStr=(Select * from ##tmp00) Drop table ##tmp0000
EXECUTE
执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。同时支持 Transact-SQL 批处理内的字符串的执行 若要唤醒调用函数,请使用 EXECUTE stored_procedure 中描述的语法。
===>
set @LikeStr= @SqlStr
declare @s nvarchar(4000)
set @s=cast(@likestr as nvarchar)
exec sp_executesql @s,N'@lcode varchar(3),@tdate datetime',@lcode varchar(3),@tdate datetime
SET @strSQL = N'SELECT '+ @SqlStr
EXEC SP_EXECUTESQL @strSQL,N'@COUNT INT OUT',@COUNT OUT
SELECT @COUNT
2.可以试试:set @LikeStr=replace(replace(@SqlStr,'@LCode',@lcode),'@Tdate',convert(varchar(10),@Tdate,120))
无法运行To:happyflystone(没枪的狙击手) 效果一样,没有声明@LCode变量
declare @AutoContn bit
declare @AutoAIMin tinyint
declare @LikeStr varchar(10)
declare @SqlStr as varchar(200)
declare @tdate as datetime/* 取引导码、是否年续号、流水号位数 */
set @LCode='123'--(SELECT LeadCode FROM System_BBills WHERE (BBillNO = @NOName) )
set @SqlStr='@LCode + Right(Year(@Tdate),2)'--从表中取到的 sql语句(我在一表中存储了一段SQL语句如:@LCode + Right(Year(@Tdate),2))
set @tdate = '2003-02-10'DECLARE @strSQL NVARCHAR(1000),@COUNT INT
SET @strSQL = N'SELECT @COUNT= '+ @SqlStr
EXEC SP_EXECUTESQL @strSQL,N'@COUNT INT OUT,@Lcode varchar(3),@tdate datetime',@COUNT OUT,@lcode,@tdate
SELECT @COUNT
/*-----------
12303
*/
set @SqlStr=@SqlStr+' INTO ##tmp00'
exec sp_executesql @SqlStr
set @LikeStr=(Select * from ##tmp00)
Drop table ##tmp0000