Hi Naomi,

>The procedure makes little sense as it's written. Why do you need dynamic sql here at all? Do you plan to pass @fileName as parameter?

It's just a one time thing. I could have structured it with multiple sp's (there are no subroutines), but did not want to clutter the db.

It seemed easy to generate for the 4 tables and just pass the name of the table as a variable.

Thanks for caring.

My problem is more basic in fact. I don't understand why the first sql generates and "alter procedure" statement, where I was hoping that it would generate an executing sql.




>>I have this in my project
>>

>>-- =============================================
>>-- 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
>>

>>
>>When I execute it, it works fine.
>>
>>No I look at the stored procedure in studio via Modify and I get
>>
>>

>>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.
>>What am I missing?
>
>The procedure makes little sense as it's written. Why do you need dynamic sql here at all? Do you plan to pass @fileName as parameter?