By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Storing result of EXEC into a variable

P: n/a
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)
Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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)

Jul 23 '05 #2

P: n/a
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.
Jul 23 '05 #3

P: n/a

"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
Jul 23 '05 #4

P: n/a
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
Jul 23 '05 #5

P: n/a

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.

Jul 23 '05 #6

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

That worked for me, thanks Yury!
Apr 7 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.