Problem returning a value from EXEC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem returning a value from EXEC

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-27 : 08:19:23

Jeffrey writes "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

The output I expect to receive is a datetime field. In this case, I got a NULL which is fine:
------------------------------------------------------
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? I have also tried using the sp_executesql stored procedure but still no luck. Any help would be appreciated.

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... "

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-27 : 08:38:43

Take a look at nr's site under sp_executesql you'll find an example on how to return variables.

http://www.nigelrivett.com/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-27 : 10:26:01

Also consider that you may not need dynamic SQL if there aren't too many different log tables.

SELECT * FROM Table1 WHERE @LogTable = 'Table1'
UNION ALL
SELECT * FROM Table2 WHERE @LogTable = 'Table2'
UNION ALL
..etc ....

Just an idea, if the # of different tables is constant. And if there are dozens or hundreds of tables with the exact same structure, maybe ask yourself "why?" and consider putting them all in 1 big table.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources