Storing result of EXEC into a variable

[Dipak Patel]

Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000

I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)

declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_select)
set @mycount=exec(@tmp_select)

[Yury Jhol]

Try to use:
set @tmp_select = (select count(*) from dptest)

Yury Jhol
Chief Systems Architect
Manager of Database Department
Database Migration Tool
www.ispirer.com
"Dipak Patel" <di*****@hotmail.com> wrote in message
news:30**************************@posting.google.c om...

Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000

I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)

declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_select)
set @mycount=exec(@tmp_select)

[Ross Presser]

On 10 Jan 2005 04:39:44 -0800, Dipak Patel wrote:

Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000

I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)

declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_select)
set @mycount=exec(@tmp_select)

The resultset from an exec can be put into a table directly:

declare @tmp_select char(500)
set @tmp_select = 'select count(*) from dptest'

CREATE TABLE ResultSet (mycount int)

INSERT INTO ResultSet
EXEC @tmp_select

To put it into a variable from there, you'd have to add another select.

For this specific example, though, it would be far easier to do

declare @tmp_select char(500)
declare @mycount int
select @mycount=count(*) from dptest

But I suspect you knew that.

[Simon Hayes]

"Dipak Patel" <di*****@hotmail.com> wrote in message
news:30**************************@posting.google.c om...

Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000

I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)

declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_select)
set @mycount=exec(@tmp_select)

Is there some reason you can't do this?

select @tmp_select = count(*)
from dptest

If you really need dynamic SQL (and normally you should avoid it), then this
is one way:

create table #t (num int)
exec('insert into #t select count(*) from dbo.sysobjects')
select num from #t

But before using this solution, see here for all the reasons why dynamic SQL
is a bad idea:

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

Simon

[Erland Sommarskog]

Dipak Patel (di*****@hotmail.com) writes:

Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000

I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)

declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_select)
set @mycount=exec(@tmp_select)

Use sp_executesql. For details
http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Don't use the method with a temp table suggested by others. That's
an extreme kludge.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[dipakpatel]

Erland Sommarskog wrote:

Dipak Patel (di*****@hotmail.com) writes:

Platform: MS SQL 7.00 - 7.00.1063 Standard Edition / Win2000

I wish to store the numeric result of a query into a variable, as
described below. This does not work, it fails on the last line with
syntax error. Is there a way to do it?
(The reason I want to do this is to store the count into a table,
therefore eventually do an update table with the variable.)

declare @tmp_select char(500)
declare @mycount int
set @tmp_select = 'select count(*) from dptest'
exec(@tmp_select)
set @mycount=exec(@tmp_select)

Use sp_executesql. For details
http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Don't use the method with a temp table suggested by others. That's
an extreme kludge.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks for the help guys, i got it to work with sp_executesql.

[nejem666]

[quote=Yury Jhol]Try to use:
set @tmp_select = (select count(*) from dptest)
================

That worked for me, thanks Yury!