<原创>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