capturing dynamically returned integer

Hello there.
I have a dynamic SQL query (I had to use it - I swear!) that returns a
single integer value. However, I'm having trouble capturing the value in a
local variable. Any ideas what I should do with my syntax (to get it working
I mean...)?
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select max(Column1) from table1'
--execute sp_executesql @.testSQL --this returns an integer value
--the following gives errors
select @.IntegerVar = (execute sp_executesql @.testSQL)
--the following assigns 0 to @.IntegerVar rather than the query result
execute @.IntegerVar = sp_executesql @.testSQLhttp://www.aspfaq.com/2492
"len" <len@.discussions.microsoft.com> wrote in message
news:B254F6C4-E94F-48B6-BB9C-7C03066D6998@.microsoft.com...
> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it
> working
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Try,
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = N'select @.IntegerVar = max(Column1) from table1'
execute sp_executesql @.testSQL, N'@.IntegerVar int output', @.IntegerVar outpu
t
select @.IntegerVar
go
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/defaul...B;EN-US;q262499
AMB
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Hi Len
Try something like:
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select @.MaxVar = max(Column1) from table1'
execute sp_executesql @.SQLQuery, N'@.MaxVar int OUTPUT', @.IntegerVar OUTPUT
SELECT @.IntegerVar
John
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL