sql server中应该如何使用动态sql语句呢？接下来的SQL server内容就为您详细介绍sql server中动态sql语句的应用，希望可以让您对动态sql语句有更多的了解。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insertMdfalarmInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[insertMdfalarmInfo] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE insertMdfalarmInfo @alarmID int, -- 告警器ID @monitorEquID varchar(16) AS begin --drop table #table_tmp set @alarmID = 38 create table #table_tmp ( [id] int ) set @monitorEquID = 6 declare @selectContainerIDsql NVARCHAR(130) set @selectContainerIDsql= 'select monitorSourceID from v_mdfAlarmPortInfo where monitorEquPort in (1,3,5) and monitorEquID = 6 group by monitorSourceID' insert into #table_tmp ([id]) EXECUTE sp_executesql @selectContainerIDsql declare countMonitorSourceID cursor for select id from #table_tmp open countMonitorSourceID declare @monitorSourceID int fetch next from countMonitorSourceID into @monitorSourceID while @@fetch_status = 0 begin print @monitorSourceID fetch next from countMonitorSourceID into @monitorSourceID end close countMonitorSourceID drop table #table_tmp deallocate countMonitorSourceID end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
相信大家都比较了解select * from tablename where aa=bb的用法和exec('select * from tablename where aa=bb')的用法 ,但是仍然有很多人不知道sp_executesql的用法，它可以让动态sql接收参数且把查询结果返回到一个参数
--接收整个条件描述的简单动态sql
declare @where varchar(100) set @where='object_name(id)=''sysobjects''' exec('select name from sysobjects where '+@where) go
--接收整个条件描述，且把查询返回到变量参数的复杂动态sql
declare @where nvarchar(100) set @where=N'object_name(id)=''sysobjects''' declare @ret varchar(100) declare @sql nvarchar(1000) set @sql=N'select @ret=name from sysobjects where '+ @where exec sp_executesql @sql,N'@ret varchar(100) output' ,@ret=@ret output select @ret go