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...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.|||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.|||I have been unable to test this as I don't have the table definitions or data, but give this a go.

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.|||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:)|||Thanks for everyone's help. Your tips helped me solve the problem.sql