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

2013-04-07 09:13:22 6.3k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Android车载桌面开发实战：从CarLauncher源码解析到自定义Launcher开发（Android 11）本文深入解析了Android 11中CarLauncher的源码架构与核心模块，并提供了从零开始定制开发车载Launcher的完整实战指南。内容涵盖车载桌面的独特定位、驾驶场景优化、多屏协同、动态内容嵌入等核心挑战，以及如何构建符合安全规范、体验流畅的自定义车载桌面应用，是车载应用开发者提升系统级定制能力的实用参考。阅读详情

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

...全文

Vmware安装windowsServer2022版本全网最详细的Vmware安装windowsServer2022版本阅读详情

fly5520

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

szm341 2013-04-07

打赏
举报

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

fly5520 2013-04-07

打赏
举报

引用 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 2013-04-07

打赏
举报

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

fly5520 2013-04-07

打赏
举报

引用 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……

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

seusoftware 2013-04-07

打赏
举报

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

SQL77 2013-04-07

打赏
举报

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

Mr_Nice 2013-04-07

打赏
举报

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

szm341 2013-04-07

打赏
举报


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

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

相关推荐