sql server中应该如何使用动态sql语句呢？下面就为您详细介绍sql server中动态sql语句的应用，希望可以让您对动态sql语句有更多的了解。

    ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[insertMdfalarmInfo]')andOBJECTPROPERTY(id,N'IsProcedure')=1) dropprocedure[dbo].[insertMdfalarmInfo] GO SETQUOTED_IDENTIFIEROFF GO SETANSI_NULLSOFF GO CREATEPROCEDUREinsertMdfalarmInfo @alarmIDint,--告警器ID @monitorEquIDvarchar(16) AS begin --droptable#table_tmp set@alarmID=38createtable#table_tmp ( [id]int ) set@monitorEquID=6 declare@selectContainerIDsqlNVARCHAR(130) set@selectContainerIDsql='selectmonitorSourceIDfromv_mdfAlarmPortInfowheremonitorEquPortin(1,3,5)andmonitorEquID=6groupbymonitorSourceID'  insertinto#table_tmp([id])EXECUTEsp_executesql@selectContainerIDsql declarecountMonitorSourceIDcursorforselectidfrom#table_tmp  opencountMonitorSourceID declare@monitorSourceIDint fetchnextfromcountMonitorSourceIDinto@monitorSourceID while@@fetch_status=0begin print@monitorSourceID fetchnextfromcountMonitorSourceIDinto@monitorSourceID end closecountMonitorSourceID droptable#table_tmp deallocatecountMonitorSourceID end GO SETQUOTED_IDENTIFIEROFF GO SETANSI_NULLSON GO

相信大家都比较了解select * from tablename where aa=bb的用法和exec(‘select * from tablename where aa=bb’)的用法 ,但是仍然有很多人不知道sp_executesql的用法，它可以让动态sql接收参数且把查询结果返回到一个参数

–接收条件值参数的静态sql

    declare@namevarchar(100) set@name='sysobjects' selectnamefromsysobjectswhereobject_name(id)=@name go

–接收整个条件描述的简单动态sql

    declare@wherevarchar(100) set@where='object_name(id)=''sysobjects''' exec('selectnamefromsysobjectswhere'+@where) go

–接收整个条件描述，且把查询返回到变量参数的复杂动态sql

    declare@wherenvarchar(100) set@where=N'object_name(id)=''sysobjects''' declare@retvarchar(100) declare@sqlnvarchar(1000) set@sql=N'select@ret=namefromsysobjectswhere'+@where execsp_executesql@sql,N'@retvarchar(100)output',@ret=@retoutput select@ret go

【编辑推荐】

教您如何实现MySQL动态视图

SQL动态查询的示例

为您讲解SQL动态语句的语法

DB2数据库对动态游标的使用

DB2动态SQL的查看方法