1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
|
USE [master]
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 07/26/2011 10:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[test]
@dbname sysname
AS
BEGIN
SET NOCOUNT ON
IF @dbname IS NULL
BEGIN
RAISERROR('A database name has to be passed', 16, 1)
RETURN
END
IF NOT EXISTS
(
SELECT *
FROM sys.DATABASES
WHERE name = @dbname
AND state_desc <> 'ONLINE'
)
BEGIN
RAISERROR('The %s database does not exist or is not available',6, 1,@dbname)
RETURN
END
--DECLARE @sql nvarchar(max)
-- SET @sql = 'SELECT * FROM ' + @dbname + '.dbo.Clients'
-- EXEC sp_executesql @sql
DECLARE @mail char(50);
DECLARE @dbEnCours char(200);
SET @dbEnCours = @dbname + 'dbo.Clients';
DECLARE c_clientsDbSs CURSOR FOR SELECT Mail,AccordNewsletterAgence,DateDerniereModification,DerniereModificationPar FROM @dbEnCours
CREATE TABLE #tmp_client(mail char(50));
OPEN c_clientsDbSs;
FETCH c_clientsDbSs INTO @mail;
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #tmp_client(mail) VALUES (@mail);
FETCH c_clientsDbSs INTO @mail;
END
CLOSE c_clientsDbSs;
DEALLOCATE c_clientsDbSs;
SELECT #tmp_client.mail FROM #tmp_client;
DROP TABLE #tmp_client;
END |
Partager