dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
Rea
Try using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>