Getting error 'Must declare the variable '@rc'.

Question

Post reply

Getting error 'Must declare the variable '@rc'.

anandsbr

SSC Veteran

Points: 239
More actions
May 14, 2010 at 9:18 am

#221573

Guys,
I am in need of help while running stored procedure in SQL server 2000. I am trying to run T-SQL in order to get table name and record count value but getting the following error.
11:02:44 [DECLARE - 0 row(s), 0.000 secs] [Error Code: 137, SQL State: S1000] Must declare the variable '@rc'.
My code looks like this...
BEGIN
SET @table_name = @table
-- Do the work to get the record count from the "dynamic sql"
SELECT @sql = 'select @rc = count(*) from ' + @table + ' where ' + @where
DECLARE @Param nvarchar(100)
SELECT @Param = '@rc int OUTPUT'
EXEC sp_executesql @sql, @Param, @rc = @record_count output
END
I even tried DECLARE @rc variable but still getting the same error. Please help.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This works for me

DECLARE @table sysname, @where VARCHAR(100), @SQL NVARCHAR(500), @record_count INT

SET @table = 'sysobjects'

SET @Where = 'xtype = ''u'''

-- Do the work to get the record count from the "dynamic sql"

SELECT @sql = 'select @rc = count(*) from ' + @table + ' where ' + @where

DECLARE @Param nvarchar(100)

SELECT @Param = '@rc int OUTPUT'

EXEC sp_executesql @sql, @Param, @rc = @record_count output

SELECT @record_count

Edit: fixed sample values for variables

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 2

As and when you upgrade to a more current version of SQL Server, this is a very fast way to compute row counts without dynamic SQL:

SELECT CA.table_name,

SUM(P.rows) AS row_count

FROM sys.partitions P

JOIN sys.tables T

ON T.[object_id] = P.[object_id]

CROSS APPLY (SELECT QUOTENAME(SCHEMA_NAME(T.schema_id)) + N'.' + QUOTENAME(T.name)) CA (table_name)

WHERE P.index_id IN (0,1)

GROUP BY CA.table_name

ORDER BY row_count DESC;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yeah, but that doesn't allow for a where clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 4

GilaMonster (5/15/2010)
Yeah, but that doesn't allow for a where clause.

True - I missed that in the original question. Oh well.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi