September 8, 2009 at 3:18 pm
Hi All,
I'm very new to this topic and am having an aweful time getting this to work. I have a stored proc that takes two table name in, both tables are the same structure and I wish to simple move a subset of table A to table B. There are going to be many tables in this structure (it's test data for C# components). Currently I have this in the sproc:
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 ' + @TargetTable + '(
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
)
SELECT
@FilePath,
@CurrentCategory,
@CurrentSubCategory,
@CurrentBpm,
@OldCategory,
@OldSubCategory,
@OldBpm
FROM ' + @SourceTable +
'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
But I get an "Incorrect syntax near 'sp_ExecuteSQL'." error message that also says "Expected STRING, TEXT_LEX, VARIABLE or GLOBAL_VAR" when I hover over sp_ExecuteSQL.
Any pointers would be great. Thank you.
James.
September 9, 2009 at 4:24 am
EXEC sp_executesql ...
Syntax is in BOL.
September 10, 2009 at 11:41 am
Why can't you do it this way? I am not sure why you are using variables in the select statement:
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 = 'INSERT INTO ' + @TargetTable + '(
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
)
SELECT
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
FROM ' + @SourceTable +
'WHERE CurrentCategory OldCategory'
EXEC (@zSQL)
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply