Dynamic SQL Help

[Jammer 0]

Hi All, I'm having some troubles working on my first stored proc using dynamic SQL and I've hit a brick wall and can't work out what I'm doing wrong. I have two tables that are always the same structure, however I wish to write a re-useable stored proc to copy records to a new table based on some value comparison. I have this in my stored proc at the moment:

ALTER PROCEDURE [dbo].[sp_CopyFileScorerChanges]
-- Add the parameters for the stored procedure here
@SourceTable NVARCHAR(100),
@TargetTable NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @zSQL NVARCHAR(4000)

SET @zSQL = N'INSERT INTO ' + @SourceTable  + '(
				\[FilePath\],
				\[CurrentCategory\],
				\[CurrentSubCategory\],
				\[CurrentBpm\],
				\[OldCategory\],
				\[OldSubCategory\],
				\[OldBpm\]
				) 
				SELECT 
					@FilePath,
					@CurrentCategory,
					@CurrentSubCategory,
					@CurrentBpm,
					@OldCategory,
					@OldSubCategory,
					@OldBpm
				FROM  ' + @TargetTable +
				'WHERE CurrentCategory <> OldCategory'
				
			
EXEC( sp\_ExecuteSQL @zSQL, N'@FilePath NVARCHAR(4000),	
							 @CurrentCategory NVARCHAR(100),
							 @CurrentSubCategory NVARCHAR(100),
							 @CurrentBpm INT,
							 @OldCategory NVARCHAR(100),
							 @OldSubCategory NVARCHAR(100),
							 @OldBpm INT',
							 @FilePath,	
							 @CurrentCategory, 
							 @CurrentSubCategory, 
							 @CurrentBpm, 
							 @OldCategory, 
							 @OldSubCategory, 
							 @OldBpm
	)

END

I'm currently getting a 'Incorrect syntax near 'sp_ExecuteSQL' - Expecting STRING, TEXT_LEX, VARIABLE or GLOBAL_VAR. I just want to call the stored proc with two table names, the source table and the target table. Any help would be really appreciated. Thanks,

Jammer My Blog | Articles

[Mycroft Holmes]

You are trying to use a varable in dynamic sql, you cannot. Everywhere you are using @Var needs to be turned into a literal (like you do for target table). When building dynamic sql I do a print @SQL and try and run the results, this will then give you more detailed results. It will also show that you are trying to insert @FilePath int a varchar without surrounding singles '

Never underestimate the power of human stupidity RAH

[Jammer 0]

Gotcha. All working now. Thank you!

Jammer My Blog | Articles

[J4amieC]

Mycroft Holmes wrote:

You are trying to use a varable in dynamic sql, you cannot. Everywhere you are using @Var needs to be turned into a literal (like you do for target table).

Wrong! sp_executesql will allow you to pass parameters into the SQL. He's using string concatenation for the table names because this is one place in SQL you cant use a variable.

[Mycroft Holmes]

You're right, I never use sp_execute, just execute(sql). What was wrong with his proc then?

Never underestimate the power of human stupidity RAH