sql server中应该如何使用动态sql语句呢?下面就为您详细介绍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 @name varchar(100)
- set @name='sysobjects'
- select name from sysobjects where object_name(id)=@name
- go
--接收整个条件描述的简单动态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
【编辑推荐】