ALTER procedure [dbo].[mProcForQueryMM]
@everyTable varchar(50),
@temTable varchar(50)
as
set nocount on
declare @sql nvarchar(2000)
declare @count varchar(500)
declare @itm varchar(500)
declare @i int
if (exists(select * from sysobjects where name=@everyTable and xtype='U') and @everyTable !=@temTable)
begin
if (not exists(select * from sysobjects where name =@temTable))
begin
set @sql='select [序号],[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] into '
set @sql=@sql+@temTable+' from '+@everyTable+' where 1=0 '
exec(@sql)
end set @sql='select @count=count(*) from '+@everyTable+' where [P/M]=''P'' or [P/M]=''M'''
exec sp_executesql @sql,N'@count int output',@count output set @i=1
while @i<=@count
begin
set @sql='insert into '+@temTable+' select * from (select rn=row_number() over(order by [ItemNumber]),@itm=[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable+' )tb where rn='+cast(@i as varchar)
exec sp_executesql @sql,N'@itm varchar(500) output',@itm output
print @itm set @i=@i+1
end
end出错语句:
set @sql='insert into '+@temTable+' select * from (select rn=row_number() over(order by [ItemNumber]),@itm=[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable+' )tb where rn='+cast(@i as varchar)'@itm=' 附近有语法错误。什么原因啊?
@everyTable varchar(50),
@temTable varchar(50)
as
set nocount on
declare @sql nvarchar(2000)
declare @count varchar(500)
declare @itm varchar(500)
declare @i int
if (exists(select * from sysobjects where name=@everyTable and xtype='U') and @everyTable !=@temTable)
begin
if (not exists(select * from sysobjects where name =@temTable))
begin
set @sql='select [序号],[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] into '
set @sql=@sql+@temTable+' from '+@everyTable+' where 1=0 '
exec(@sql)
end set @sql='select @count=count(*) from '+@everyTable+' where [P/M]=''P'' or [P/M]=''M'''
exec sp_executesql @sql,N'@count int output',@count output set @i=1
while @i<=@count
begin
set @sql='insert into '+@temTable+' select * from (select rn=row_number() over(order by [ItemNumber]),@itm=[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable+' )tb where rn='+cast(@i as varchar)
exec sp_executesql @sql,N'@itm varchar(500) output',@itm output
print @itm set @i=@i+1
end
end出错语句:
set @sql='insert into '+@temTable+' select * from (select rn=row_number() over(order by [ItemNumber]),@itm=[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable+' )tb where rn='+cast(@i as varchar)'@itm=' 附近有语法错误。什么原因啊?
你将这段SQL打印出来,这么执行肯定是不行的。说说需求吧!
或者将值存到一个变量中
set @str = 'select @itm=... from ()t where ...'
exec sp_executesql ...
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
@everyTable varchar(50),
@temTable varchar(50)
as
set nocount on
declare @sql nvarchar(2000)
declare @count varchar(500)
declare @itm varchar(500)
declare @i int
if (exists(select * from sysobjects where name=@everyTable and xtype='U') and @everyTable !=@temTable)
begin
if (not exists(select * from sysobjects where name =@temTable))
begin
set @sql='select [序号],[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] into '
set @sql=@sql+@temTable+' from '+@everyTable+' where 1=0 '
exec(@sql)
end set @sql='select @count=count(*) from '+@everyTable+' where [P/M]=''P'' or [P/M]=''M'''
exec sp_executesql @sql,N'@count int output',@count output set @i=1
while @i<=@count
begin
set @sql='insert into '+@temTable+' select * from (select rn=row_number() over(order by [ItemNumber]),[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable+' )tb where rn='+cast(@i as varchar)
exec sp_executesql @sql
set @sql='select rn=row_number() over(order by [ItemNumber]),@itm=[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable
exec sp_executesql @sql,N'@itm varchar(500) output',@itm output
print @itm set @i=@i+1
end
end
????????
怎么另行赋值啊?
”
@everyTable varchar(50),
@temTable varchar(50)
as
set nocount on
declare @sql nvarchar(2000)
declare @count varchar(500)
declare @itm varchar(500)
declare @i int
if (exists(select * from sysobjects where name=@everyTable and xtype='U') and @everyTable !=@temTable)
begin
if (not exists(select * from sysobjects where name =@temTable))
begin
set @sql='select [序号],[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] into '
set @sql=@sql+@temTable+' from '+@everyTable+' where 1=0 '
exec(@sql)
end set @sql='select @count=count(*) from '+@everyTable+' where [P/M]=''P'' or [P/M]=''M'''
exec sp_executesql @sql,N'@count int output',@count output set @i=1
while @i<=@count
begin
set @sql='insert into '+@temTable+' select * from (select rn=row_number() over(order by [ItemNumber]),[ItemNumber],[Description],[quantity],[um],[Res],[P/M],[Supplier],[BTO/BTS],[交货周期] from '+@everyTable+' )tb where rn='+cast(@i as varchar)
exec sp_executesql @sql
set @sql='select @itm=[ItemNumber] from '+@everyTable
exec sp_executesql @sql,N'@itm varchar(500) output',@itm output
print @itm set @i=@i+1
end
end