Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: Return a value from EXEC

    I have the following code:

    DECLARE @StartTimeStamp as datetime
    DECLARE @sqlcmd as varchar(8000)
    DECLARE @LogTable as varchar(20)
    DECLARE @PlantID as varchar(20)
    DECLARE @GroupIdent as varchar(20)
    set @LogTable = 'LOG_APPLE'
    set @PlantID = 'RETY'
    set @GroupIdent = '242'
    BEGIN
    SET @sqlcmd = '
    SET @StartTimeStamp =
    (SELECT TOP 1 timestamp
    FROM ' + @LogTable + '
    WHERE timestamp <
    (SELECT TPMSummaryProcessTime
    FROM tblPRSGroup
    WHERE PlantID = ''' + @PlantID + ''' AND GroupIdent = ''' + @GroupIdent + ''')
    ORDER BY timestamp DESC)
    '
    SELECT @sqlcmd
    EXECUTE (@sqlcmd)
    SELECT @StartTimeStamp
    END


    When I run it from Query Analyzer, I get the following message on the last SELECT statement:

    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@StartTimeStamp'.

    If I copy the value of @sqlcmd into Query Analyzer like the following with a DECLARE stament, I get the correct output:

    DECLARE @StartTimeStamp as datetime
    SET @StartTimeStamp =
    (SELECT TOP 1 timestamp
    FROM LOG_PX90J
    WHERE timestamp <
    (SELECT TPMSummaryProcessTime
    FROM tblPRSGroup
    WHERE PlantID = 'USFL' AND GroupIdent = '242')
    ORDER BY timestamp DESC)
    SELECT @StartTimeStamp

    This is the output I expect to receive:
    ------------------------------------------------------
    NULL

    (1 row(s) affected)

    My question is, since the statement I am creating in @sqlcmd is syntactically correct, how do I return the value from that command to @StartTimeStamp via the EXECUTE statement?

    P.S. I am forcing the variables to values just for testing purposes. I realize I should not be using "timestamp" as a field name also but I am dealing with legacy databases.

    Thanks...

  2. #2
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    This is because when you execute dynamic SQL the script is actually executed in a different process. As such any variables you declare do not exist when you call EXECUTE.

    Look up the system stored proc sp_executesql in Books Online (BOL) to resolve this problem.

    macka.

  3. #3
    Join Date
    Jan 2003
    Posts
    6
    Took your advise and did the following:

    BEGIN
    SET @sqlcmd = N'
    SET @StartTimeStamp =
    (SELECT TOP 1 timestamp
    FROM @LogTable
    WHERE timestamp <
    (SELECT TPMSummaryProcessTime
    FROM tblPRSGroup
    WHERE PlantID = @PlantID AND GroupIdent = @GroupIdent)
    ORDER BY timestamp DESC)
    '
    SELECT @sqlcmd
    EXECUTE sp_executesql @sqlcmd, N'@StartTimeStamp datetime, @LogTable varchar(20), @PlantID varchar(20), @GroupIdent varchar(20)',
    @StartTimeStamp, @LogTable, @PlantID, @GroupIdent
    SELECT @StartTimeStamp
    END

    Now I get the following error:
    Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 21
    Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.

  4. #4
    Join Date
    Jan 2003
    Location
    Leamington Spa, UK
    Posts
    17
    I have been unable to test this as I don't have the table definitions or data, but give this a go.

    Code:
    DECLARE @StartTimeStamp as datetime
    DECLARE @sqlcmd as nvarchar(4000)
    DECLARE @LogTable as varchar(20)
    DECLARE @PlantID as varchar(20)
    DECLARE @GroupIdent as varchar(20)
    set @LogTable = 'LOG_APPLE'
    set @PlantID = 'RETY'
    set @GroupIdent = '242'
    
    SET @sqlcmd = N'
    SET @StartTimeStamp = 
    (SELECT TOP 1 timestamp
    FROM ' + @LogTable + '
    WHERE timestamp <
    (SELECT TPMSummaryProcessTime
    FROM tblPRSGroup
    WHERE PlantID = ''' + @PlantID + ''' AND GroupIdent = ''' + @GroupIdent + ''')
    ORDER BY timestamp DESC)
    '
    
    EXECUTE sp_executesql @sqlcmd, N'@StartTimeStamp datetime', @StartTimeStamp

    macka.

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    MODIFY:

    EXECUTE sp_executesql @sqlcmd, N'@StartTimeStamp datetime', @StartTimeStamp
    SELECT @StartTimeStamp

    TO:

    EXECUTE sp_executesql @sqlcmd, N'@StartTimeStamp datetime', @StartTimeStamp OUTPUT
    SELECT @StartTimeStamp

    Enjoy your query

  6. #6
    Join Date
    Jan 2003
    Posts
    6

    Talking

    Thanks for everyone's help. Your tips helped me solve the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •