Need to insert an integer value into a variable from a query

Question

Post reply

Need to insert an integer value into a variable from a query

M Chapman

SSC Veteran

Points: 212
More actions
June 25, 2004 at 12:27 pm

#108161

Here's my problem...this will not work:
declare @retval int, @tablename char(12), @procedure varchar(1000)
set @tablename = 'ExampleTable'
set @procedure = 'select count(*) from ' + @tablename
set @retval = exec(@procedure)

Any ideas? I need to plug a row count into a variable in a dynamic query, where the tablename is different every time.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vn Right there with Babe Points: 782 More actions · Answer 1

Try this:

DECLARE @tablename char(12), @procedure varchar(1000)

set @tablename = 'Groupings'

set @procedure = 'declare @retval int; select @retval = count(*) from ' + @tablename + '; select @retVal'

exec(@procedure)

The Exec command does not have any return values. Also all the statements inside the Exec command are one batch and the lifetime of the variables declared within the exec statement is only for the duration of the execution of the Exec statement. So, if I gave a "Select @Retval" after the exec call, it would not recognize the @RetVal variable.

M Chapman SSC Veteran Points: 212 More actions · Answer 2

yeah, that works, but i need to return the value or set it to a variable

M Chapman SSC Veteran Points: 212 More actions · Answer 3

i got what i needed...cept i had to select a value from a temp table into a cursor that did a "fetch next into" my variable.

Aaron West SSC Eights! Points: 808 More actions · Answer 4

Use sp_executesql

This gets me the count of records in tbl_clients. I just posted this to microsoft.public.sqlserver.programming as well.

declare @mySQL nvarchar(4000)

declare @CountRec int

SET @mySQL = 'SELECT @CountParm = COUNT(*) FROM tbl_clients'

exec sp_executesql @mySQL, N'@CountParm INT OUTPUT', @CountParm = @CountRec OUTPUT

select @CountRec

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

here's one function I like to use (works only with that have a Primary key):

CREATE FUNCTION [dbo].[CountALL] (@sTableName as varchar(255))

RETURNS int AS

BEGIN

--this is about 6 times faster than count(*) according to the execution plan

--and it sure beats the hell out of full table scan to count all the rows on a big table

return isnull((Select max(rowcnt) as Total from dbo.sysindexes where id = object_id(@sTableName) and indid < 2), 0)

END