@NewTicket是我声明的变量,sql语句是我拼接的,我当前是这么写的,并不能赋值给变量@NewTicket
exec ('select @NewTicket = count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1')
各位大师指教一下吧!sql拼接sql存储过程存储过程中变量赋值
exec ('select @NewTicket = count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1')
各位大师指教一下吧!sql拼接sql存储过程存储过程中变量赋值
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
参考http://msdn.microsoft.com/zh-cn/library/ms188001.aspx
declare @a int
exec sp_executesql N'select @b= a from(select a=1)t',N' @b int output ',@a output
select @a
insert into @NewTicket
exec ('select count(t.tid) from Ticket as t' + @StrWhere + ' and t.TState = 1')
/****** 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 @ResolvedTimeBegin TranIf @@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
Endinsert #TableName(NewTicket, ResolvedTicket, AcceptTicket, ResponseTime, ResolvedTime)
Values(@NewTicket, @ResolvedTicket, @AcceptTicket, @ResponseTime, @ResolvedTime)If @@ERROR <> 0 Begin
Rollback Tran
RaisError('数据库执行失败,请检查数据后重试!', 16, 1) Return -3
Endselect * from #TableNameDrop Table #TableNameCommit Tran
set @sql='select @内部变量 = count(t.tid) from Ticket as t ' + @StrWhere + ' and t.TState = 1'
exec sp_executesql @sql,N'@内部变量 int out',@NewTicket out