22,209
社区成员
发帖
与我相关
我的任务
分享
set @sql = @sql + 'and SFName like ''%'+substring(@name,1,@position-1)+'%'' '--循环构建SQL语句
set @sql = 'select Top 1 MDID From dbo.SF_Name where Quantity='+''''+str(@count)+''''+' '+@sql --此语句查询到数字2
exec sp_executesql @sql,N'@MDID INT output',@MDID--有疑虑的语句
if isnull(@MDID,0)=0
print 'ddd' --测试语句,结果被打印
declare @MDID int
set @sql = @sql + 'and SFName like ''%'+substring(@name,1,@position-1)+'%'' '--循环构建SQL语句
set @sql = 'select Top 1 @MDID=MDID From dbo.SF_Name where Quantity='+''''+str(@count)+''''+' '+@sql --此语句查询到数字2
exec sp_executesql @sql,N'@MDID INT output',@MDID output--有疑虑的语句
if isnull(@MDID,0)=0
print 'ddd' --测试语句,结果被打印
--改
Declare @SaleNumSum int
exec('Select ['+ @SaleNumSum +'] = sum(SaleNum) from [200608] where StoreID = 24')
--如何将exec执行结果放入变量中?
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
--------------------------------------------------------------------------------
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格
当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确
3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
--创建表
CREATE TABLE SF_Name
(
MDID int PRIMARY KEY,
SFName varchar (100),
Quantity int
)
GO
INSERT SF_Name VALUES(1,'胶牙上下拉开尾自动头',5)
INSERT SF_Name VALUES(2,'8#胶牙开尾自动头',4)
INSERT SF_Name VALUES(3,'8#胶牙密尾自动头',4)
GO
--存储过程里面的语句,@MDID本是输出
declare @position int,@quantity int,@sql nvarchar(1000),@count int,@Name varchar(50),@MDID int
select @Name='8#/胶牙/开尾/自动头'
set @sql = ''
set @count = 0
while len(@name) > 0
begin
set @count = @count+1
set @position = charindex('/',@Name)
if @position = 0
set @position = len(@name)+1
set @sql = @sql + 'and SFName like ''%'+substring(@name,1,@position-1)+'%'' '--循环构建SQL语句
set @Name = stuff(@name,1,@position,'')
end
set @sql = 'select Top 1 MDID From dbo.SF_Name where Quantity='+''''+str(@count)+''''+' '+@sql --加上数量
exec sp_executesql @sql,N'@MDID INT output',@MDID output
if isnull(@MDID,0)=0
print 'ddd'
else
print 'mmmm'
exec sp_executesql @sql,N'@MDID INT output',@MDID output