CREATE PROC proc_test @total INT OUTPUT AS SELECT @total=5 GODECLARE @i INT EXEC proc_test @total=@i output SELECT @i
@total是存储过程的输出参数,
declare @num int declare @sql varchar(200) select @num=count(*) from salesinfos print @num 以上是正确的, 但是我希望以上是通过exec(@sql)这样的方式执行select语句的,因为我实际的语句是字符串拼接的
set @totalSql='select @Total=count(*) from这句有错 set @totalSql='select ‘+@Total+’=count(*) from
ALTER PROCEDURE [dbo].[ZSalesInfosByTypeDayPage] @StartTime datetime, @EndTime datetime, @Total int output, @PageSize INT=10, @PageNum INT=1, @Type varchar(50)='A'AS BEGIN SET NOCOUNT ON;declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([Time])+'=max(case when [Time]='+quotename([Time],'''')+' then [num] else 0 end)' from ZTempTable(@StartTime,@EndTime,@Type) group by [Time]declare @newSql varchar(4000) declare @totalSql varchar(4000) set @newSql='select PatternNumBer,proimg, ShopType, Size,Color'+@s+',[总计]=sum([num]) from ZTempTable('''+convert(varchar,@StartTime)+''','''+convert(varchar,@EndTime)+''','''+convert(varchar,@Type)+''') group by PatternNumBer,proimg, ShopType, Size, Color order by PatternNumBer,proimg, ShopType, Size, Color asc' set @totalSql='select @tempNum=count(*) from ZTempTable('''+convert(varchar,@StartTime)+''','''+convert(varchar,@EndTime)+''','''+convert(varchar,@Type)+''') group by PatternNumBer,proimg, ShopType, Size, Color order by PatternNumBer,proimg, ShopType, Size, Color asc' exec (@newSql) exec sp_executesql @totalSql,N'@tempNum int output',@Total output END go 这样为什么不能得到输出参数@total declare @aaa int exec ZSalesInfosByTypeDayPage '2010-12-1','2010-12-7',@aaa output select @aaa 消息 214,级别 16,状态 2,过程 sp_executesql,第 24 行 过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。
@total INT OUTPUT
AS
SELECT @total=5
GODECLARE @i INT
EXEC proc_test @total=@i output
SELECT @i
declare @sql varchar(200)
select @num=count(*) from salesinfos
print @num
以上是正确的,
但是我希望以上是通过exec(@sql)这样的方式执行select语句的,因为我实际的语句是字符串拼接的
set @totalSql='select ‘+@Total+’=count(*) from
ALTER PROCEDURE [dbo].[ZSalesInfosByTypeDayPage]
@StartTime datetime,
@EndTime datetime,
@Total int output,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGIN
SET NOCOUNT ON;declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Time])+'=max(case when [Time]='+quotename([Time],'''')+' then [num] else 0 end)'
from ZTempTable(@StartTime,@EndTime,@Type) group by [Time]declare @newSql varchar(4000)
declare @totalSql varchar(4000)
set @newSql='select PatternNumBer,proimg, ShopType, Size,Color'+@s+',[总计]=sum([num]) from ZTempTable('''+convert(varchar,@StartTime)+''','''+convert(varchar,@EndTime)+''','''+convert(varchar,@Type)+''') group by PatternNumBer,proimg, ShopType, Size, Color order by PatternNumBer,proimg, ShopType, Size, Color asc'
set @totalSql='select @tempNum=count(*) from ZTempTable('''+convert(varchar,@StartTime)+''','''+convert(varchar,@EndTime)+''','''+convert(varchar,@Type)+''') group by PatternNumBer,proimg, ShopType, Size, Color order by PatternNumBer,proimg, ShopType, Size, Color asc'
exec (@newSql)
exec sp_executesql @totalSql,N'@tempNum int output',@Total output END
go
这样为什么不能得到输出参数@total
declare @aaa int
exec ZSalesInfosByTypeDayPage '2010-12-1','2010-12-7',@aaa output
select @aaa
消息 214,级别 16,状态 2,过程 sp_executesql,第 24 行
过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。