How do I select count(*) into a variable in a stored procedure?

Michael Brule

unread,

Apr 2, 1998, 3:00:00 AM4/2/98

to

I've been trying to come up with a way to pass a table name into a
stored procedure, do a "select count(*) from @tablename", then toggle
processing based on the result of the count. No matter how I try it, I
either don't get the desired result or I get a syntax error. Any ideas?

Ken Smith

unread,

Apr 2, 1998, 3:00:00 AM4/2/98

to michae...@phl.com

select @variable = count(*) from tablename

If you want tablename to be a variable, I think you have to exec the select
statement like

execute ("select @variable = count(*) from @tablename")

I think, give it a try

Ken

vcard.vcf

Ricardo Pistarino

unread,

Apr 2, 1998, 3:00:00 AM4/2/98

to

Try something like this:

declare @tableName varchar(30)
declare @sql varchar(255)
select @sql = "select count(*) from " + @tableName

exec (@sql)

Hope this help.
Ricardo.-

Michael Brule escribió en mensaje <352394...@phl.com>...

Ben McEwan

unread,

Apr 2, 1998, 3:00:00 AM4/2/98

to

In article <352394...@phl.com>, michae...@phl.com says...

> I've been trying to come up with a way to pass a table name into a
> stored procedure, do a "select count(*) from @tablename", then toggle
> processing based on the result of the count. No matter how I try it, I
> either don't get the desired result or I get a syntax error. Any ideas?

Table names must be determined before the query (or proc) is run, because
SQL Server takes a parse, optimize, compile execute approach. During the
Parse phase, the parser evaluates whether or not the object names exist.
Since a variable table name is not known at parse time, it can't be
checked, and it can't be optimized.

Now, having said that, you may spawn a separate process with the EXEC
command, like in this example:

declare @tblname sysname, --sysname is varchar(30) not null
@querystring varchar(255)
select @tblname = 'sysobjects'
select @querystring = 'select count(*) from ' + @tblname

exec(@querystring)

... but this doesn't really answer your question, because what you REALLY
wanted to do was get the count of rows in the table. You can't pass the
values of variables out of exec() (at least, AFAIK), so the only way to
do this would be to do all the work (the counts, the action based on the
count, etc.) inside the exec().

--
Ben McEwan, President, Geist, LLC
bmcewan@global2000. -ANTISPAM- .net

Michael Brule

unread,

Apr 2, 1998, 3:00:00 AM4/2/98

to

Michael Brule wrote:
>
> I've been trying to come up with a way to pass a table name into a
> stored procedure, do a "select count(*) from @tablename", then toggle
> processing based on the result of the count. No matter how I try it, I
> either don't get the desired result or I get a syntax error. Any ideas?

Well, I figured it out!!! My thanks to Ricardo, Ben and Ken. See the
attachment to see how it was worked out. Basically, I had to create a
temp table, insert the result of the count into it, then select it back
out. Geez! You'd think there was an easier way!

ca_count.txt

Neil Pike

unread,

Apr 2, 1998, 3:00:00 AM4/2/98

to

Michael,

You'll need to do the select count(*) in an EXEC statement that also
populates a fixed table based on the result - use @@spid to give you a
unique row. Then you can check the table back in the sp and do
something based on that.

Neil Pike MVP/MCSE
Protech Computing Ltd