>-- ============================================= >-- Create basic stored procedure template >-- ============================================= > >-- Drop stored procedure if it already exists >IF EXISTS ( > SELECT * > FROM INFORMATION_SCHEMA.ROUTINES > WHERE SPECIFIC_SCHEMA = N'dbo' > AND SPECIFIC_NAME = N'USP_RestoreState' >) > DROP PROCEDURE dbo.USP_RestoreState >GO > >CREATE PROCEDURE dbo.USP_RestoreState >AS > declare @filename varchar(30); > DECLARE @NOW DATETIME; > set @NOW = getdate(); > declare @yymmdd varchar(8) > set @yymmdd = right(convert(varchar(4),year(@now)),2) > + right('00'+convert(varchar(2),month(@now)),2) > + right('00'+convert(varchar(2),day(@now)),2) > declare @sql varchar(200) > EXEC sp_DropForeignkeys > set @filename = 'Origins' > set @sql = 'drop table dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > set @filename = 'Parcels' > set @sql = 'DROP TABLE dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > set @filename = 'Transactionheaders' > set @sql = 'DROP TABLE dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > set @filename = 'Transactions' > set @sql = 'DROP TABLE dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > exec USP_CreateForeignkeys >GO > >-- ============================================= >-- Example to execute the stored procedure >-- ============================================= >EXECUTE dbo.USP_RestoreState >GO >>
>USE [D060] >GO >/****** Object: StoredProcedure [dbo].[USP_RestoreState] Script Date: 9/7/2015 7:22:41 PM ******/ >SET ANSI_NULLS ON >GO >SET QUOTED_IDENTIFIER ON >GO > >ALTER PROCEDURE [dbo].[USP_RestoreState] >AS > declare @filename varchar(30); > DECLARE @NOW DATETIME; > set @NOW = getdate(); > declare @yymmdd varchar(8) > set @yymmdd = right(convert(varchar(4),year(@now)),2) > + right('00'+convert(varchar(2),month(@now)),2) > + right('00'+convert(varchar(2),day(@now)),2) > declare @sql varchar(200) > EXEC sp_DropForeignkeys > set @filename = 'Origins' > set @sql = 'drop table dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > set @filename = 'Parcels' > set @sql = 'DROP TABLE dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > set @filename = 'Transactionheaders' > set @sql = 'DROP TABLE dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > set @filename = 'Transactions' > set @sql = 'DROP TABLE dbo.' + @filename > print @sql > exec (@sql) > set @sql = 'select * into dbo.'+@filename + ' from dbo._' + @filename > print @sql > exec (@sql) > set @sql = 'alter table dbo.'+@filename + ' add constraint PK_' + @filename + ' primary key (' + @filename + '_Id)' > print @sql > exec (@sql) > exec USP_CreateForeignkeys >>and when I execute it, for obvious reasons nothing happens.