Storing result of Dynamic query to local variable

23 Mar 2006 2:10 PM

Charlie@CBFC

Hi:

Is there a way to store result of a dynamic query to a local variable? For
example, why doesn't this work?

declare @var varchar (50)
set @var = exec('select customerid from customers where customerid =
''ALFKI''')
select @var

Thanks,
Charlie

23 Mar 2006 2:15 PM

Rogas69

Hi,
look at this good article:

http://www.sommarskog.se/dynamic_sql.html

especially on sp_executesql:
DECLARE @sql nvarchar(4000),   -- nvarchar(MAX) on SQL 2005.
           @col sysname,
           @min varchar(20)
   SELECT @col = N'au_fname'
   SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col +
                 N')) FROM authors'
   EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT
   SELECT @minPeter

23 Mar 2006 2:19 PM

Aaron Bertrand [SQL Server MVP]

http://www.aspfaq.com/2492

Show quote

"Charlie@CBFC" <fineblu***@verizon.net> wrote in message
news:e2aQvOoTGHA.4452@TK2MSFTNGP12.phx.gbl...
> Hi:
>
> Is there a way to store result of a dynamic query to a local variable?
> For example, why doesn't this work?
>
> declare @var varchar (50)
> set @var = exec('select customerid from customers where customerid =
> ''ALFKI''')
> select @var
>
> Thanks,
> Charlie
>

23 Mar 2006 2:37 PM

Charlie@CBFC

Thanks! That worked well.

Charlie
Show quote

"Charlie@CBFC" <fineblu***@verizon.net> wrote in message
news:e2aQvOoTGHA.4452@TK2MSFTNGP12.phx.gbl...
> Hi:
>
> Is there a way to store result of a dynamic query to a local variable?
> For example, why doesn't this work?
>
> declare @var varchar (50)
> set @var = exec('select customerid from customers where customerid =
> ''ALFKI''')
> select @var
>
> Thanks,
> Charlie
>