OUTPUT variables to sp_executesql

Question

Background: SQL Server 2008 R2

Having issues with the following. Been given a usp to "finish off", author is unavailable. It extracts data from source table, copies to target table of same name + datetime stamp in an archive db then truncates source table. Want to confirm that no errors have occurred & row counts are the same before I truncate source. As I'm using DDL a TRY CATCH combo won't work.

Following code works:

DECLARE @HostName           VARCHAR(30)     -- Name of server running script
DECLARE @dbName             VARCHAR(30)     -- Database currently connected to
DECLARE @LogText            VARCHAR(255)            -- Text to be logged to ArchLog table
DECLARE @NewUTMetaData          VARCHAR(255)        -- New tablename for UTMetaData
DECLARE @NewOutboundMessagePending  VARCHAR(255)        -- New tablename for OutboundMessagePending
DECLARE @NewOutboundMessageStatus   VARCHAR(255)        -- New tablename for OutboundMessageStatus
DECLARE @NewOutboundMessageResult   VARCHAR(255)        -- New tablename for OutboundMessageResult
DECLARE @NewFileMessageNonSequence  VARCHAR(255)        -- New tablename for FileMessageNonSequence
DECLARE @NewOutboundMessageRequest  VARCHAR(255)        -- New tablename for OutboundMessageRequest
DECLARE @NewOutboundMessage         VARCHAR(255)        -- New tablename for OutboundMessage
DECLARE @SQLQuery                   NVARCHAR(500)
DECLARE @return_value               INT
DECLARE @Err                        INT = 0
DECLARE @ErrFin                     INT = 0
DECLARE @SQLCount                   NVARCHAR(255) = 'SELECT @TargetCountOUT = COUNT(*) FROM ' 
DECLARE @ParmDefinition             nvarchar(50)  = N'@TargetCountOUT int OUTPUT';;
DECLARE @SourceCount                INT = 0
DECLARE @TargetCount                INT = 0

--  Log message that delete is starting
SELECT  @HostName = host_name()
SELECT  @dbName   = db_name()
SELECT  @LogText  = 'Procedure ArchiveMuleDBMetrix_NEW starting database= ' + @dbname + ' host= ' + @HostName 
EXEC xp_logevent 50001, @LogText, 'INFORMATIONAL'

INSERT INTO [MuleDBArch].[dbo].[ArchLog]
       ([LogEntryDateTime]
       ,[LogEntry])
    VALUES (GETDATE()
    ,@LogText)

BEGIN
    SELECT @NewUTMetaData             = (SELECT '[MuleDBArch].[dbo].UTMetaDataA'             + convert(varchar(50),GetDate(),112) + replace(convert(varchar, GetDate(),108),':',''))
    SET @err = @@error;
    SET @SQLQuery = 'select * into ' + @NewUTMetaData             + ' from [MuleDB].[dbo].[SASITUTMetaData]'
    EXECUTE sp_executesql @SQLQuery
    SET @SourceCount = @@ROWCOUNT

    SELECT  @LogText = 'Rows archived into ' + @NewUTMetaData + ' by Procedure ArchiveMuleDBMetrix_NEW = ' + CAST(@SourceCount as VARCHAR(6))
    EXEC xp_logevent 50002, @LogText, 'INFORMATIONAL'
    INSERT INTO [MuleDBArch].[dbo].[ArchLog]
      ([LogEntryDateTime]
      ,[LogEntry])
    VALUES (GETDATE()
           ,@LogText)

    IF @err = 0
      BEGIN
        SET @sqlcount = @sqlcount + @NewUTMetaData
        EXECUTE sp_executesql @sqlcount, @ParmDefinition, @TargetCountOUT=@TargetCount OUTPUT;
        IF @SourceCount = @TargetCount
          TRUNCATE TABLE [MuleDB].[dbo].[SASITUTMetaData]
        ELSE
          SELECT  @LogText = 'Post archive, row counts differ between [MuleDB].[dbo].[SASITUTMetaData] and' + @NewUTMetaData 
          EXEC xp_logevent 50003, @LogText, 'Error'
          Set @errfin = 1
          SET @err = 0
      END    
    ELSE
      BEGIN
        SELECT  @LogText = 'Procedure ArchiveMuleDBMetrix_NEW failed archiving into ' + @NewUTMetaData 
        EXEC xp_logevent 50004, @LogText, 'Error'
        Set @errfin = 1
        SET @err = 0
      END
END

However, if I repeat the block to work on another table (or indeed the same one) query completes with error and I get the message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

I think I've narrowed it down to the line:

EXECUTE sp_executesql @sqlcount, @ParmDefinition, @TargetCountOUT=@TargetCount OUTPUT;

Any help appreciated

score 0 · Answer 1 · answered Feb 14 '18 at 15:00

In the below code

EXECUTE sp_executesql @sqlcount, @ParmDefinition, @TargetCountOUT=@TargetCount OUTPUT;

You are trying to pass parameter values without specifying the parameter names except the output parameter while calling the stored procedure. Either specify the parameter name for all or none. Try to execute the code as follows

EXECUTE sp_executesql @sqlcount, @ParmDefinition, @TargetCount OUTPUT;

OUTPUT variables to sp_executesql

1 Answers1