如何执行拼接的sql语句，并把查询结果赋值给变量

[fly5520]

@NewTicket是我声明的变量，sql语句是我拼接的，我当前是这么写的，并不能赋值给变量@NewTicket
exec ('select @NewTicket = count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1')
各位大师指教一下吧！

[szm341]

declare @sql nvarchar(1000)
set @sql='select @NewTicket = count(t.tid) from Ticket as t ' + @StrWhere + ' and t.TState = 1'
exec sp_executesql @sql,N'@NewTicket int out',@NewTicket out
select @NewTicket

[Mr_Nice]

使用sp_executesql output 返回， 参考http://msdn.microsoft.com/zh-cn/library/ms188001.aspx

[SQL77]

exec('declare @a int;select @a=1;select @a') declare @a int exec sp_executesql N'select @b= a from(select a=1)t',N' @b int output ',@a output select @a

[seusoftware]

变量输出，得借助sp_executesql啦, exec只能用来运行

[fly5520]

引用 1 楼 szm341 的回复:

SQL code?1234declare @sql nvarchar(1000)set @sql='select @NewTicket = count(t.tid) from Ticket as t ' + @StrWhere + ' and t.TState = 1'exec sp_executesql @sql,N'@NewTicket int out',@NewTi……

我需要把查询结果插到临时表，提示“未声明标量变量”

[szm341]

晕。。你@NewTicket这个是临时表？ insert into @NewTicket exec ('select count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1')

[fly5520]

引用 6 楼 szm341 的回复:

晕。。你@NewTicket这个是临时表？ insert into @NewTicket exec ('select count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1')

sorry 是我没表达清楚，这是我的源码

/****** Object:  StoredProcedure [dbo].[sp_TicketStatistical]    Script Date: 04/07/2013 08:37:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TicketStatistical]
@QueryTimeStr datetime,   
@QueryTimeStr2 datetime,    
@QuerySGroup int = 0 ,                                    
@QuerySStaff int = 0    

AS
	
DECLARE	@NewTicket int,			
		@ResolvedTicket int,	
		@AcceptTicket int,			
		@ResponseTime float,			
		@ResolvedTime float,			
		@StrWhere nvarchar(500)    		
	
SET  @StrWhere = ' where t.TCreateTime >= ' + convert(varchar(10),@QueryTimeStr,120) + ' and t.TCreateTime <= ' + convert(varchar(10),@QueryTimeStr2,120)

IF (@QuerySGroup > 0 and @QuerySStaff = 0)
BEGIN
	set @strwhere =  @StrWhere + ' and t.SStaffID = select ss.SStaffID from TicketSStaff as ss where ss.SGroupID = ' + cast(@QuerySGroup as nvarchar(4))
END
ELSE IF @QuerySStaff > 0
BEGIN
	set  @strwhere = @StrWhere + ' and t.SStaffID = ' + cast(@QuerySStaff as nvarchar(4))
END
declare @NewTsql nvarchar(500),
		@ResolvedTsql nvarchar(500),
		@AcceptTsql nvarchar(500),
		@ResponseTsql nvarchar(500),
		@ResolvedTsql nvarchar(500)
		
	set @NewTsql = 'select count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1'
	set @ResolvedTsql = 'select count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 3'
	set @AcceptTsql = 'select count(t.tid) from ticket as t' + @StrWhere + ' and t.TState = 2'
	set @ResponseTsql = 'select round(avg(DATEDIFF(n,t.TCreateTime,t.TResponseTime))/60.0,1) from Ticket as t ' + @StrWhere
	set @ResolvedTsql = 'select round(avg(DATEDIFF(n,t.TCreateTime,t.TLastReplyTime))/60.0,1) from Ticket as t ' + @StrWhere + ' and t.TState = 3'
	
	exec sp_executesql @NewTsql,N'@NewTicket int out',@NewTicket out
	select @NewTicket
	exec sp_executesql @ResolvedTsql,N'@ResolvedTicket int out',@ResolvedTicket out
	select @ResolvedTicket
	exec sp_executesql @AcceptTsql,N'@AcceptTicket int out',@AcceptTicket out
	select @AcceptTicket
	exec sp_executesql @ResponseTsql,N'@ResponseTime int out',@ResponseTime out
	select @ResponseTime
	exec sp_executesql @ResolvedTsql,N'@ResolvedTime int out',@ResolvedTime out
	select @ResolvedTime

Begin Tran

If @@ERROR <> 0 Begin
  Rollback Tran
  RaisError('数据库执行失败，请检查数据后重试！', 16, 1) Return -1
End
     
CREATE TABLE #TableName(
                        NewTicket int,
                        ResolvedTicket int,
                        AcceptTicket int,
                        ResponseTime float,
                        ResolvedTime float
                        )

If @@ERROR <> 0 Begin
  Rollback Tran
  RaisError('数据库执行失败，请检查数据后重试！', 16, 1) Return -2
End

insert #TableName(NewTicket, ResolvedTicket, AcceptTicket, ResponseTime, ResolvedTime)
Values(@NewTicket, @ResolvedTicket, @AcceptTicket, @ResponseTime, @ResolvedTime)

If @@ERROR <> 0 Begin
  Rollback Tran
  RaisError('数据库执行失败，请检查数据后重试！', 16, 1) Return -3
End

select * from #TableName

Drop Table #TableName

Commit Tran

[szm341]

上面例子写的好好的，你就没看出你写的有什么差异？ set @sql='select @内部变量 = count(t.tid) from Ticket as t ' + @StrWhere + ' and t.TState = 1' exec sp_executesql @sql,N'@内部变量 int out',@NewTicket out