Syntax Prob: Using Variables in Stored Procedures

Question

Can anyone tell how correct the following code below. Iam tryin to create a stored procedure that returns the rowcount of a table whose name is passed to it.

CREATE PROCEDURE spROWCOUNTER
(
    @tablename nvarchar(20)
    @rowCountVal int OUTPUT
)

AS

DECLARE @strQuery nvarchar(300)

SET @strQuery = 'SELECT @rowCountVal=COUNT(*) FROM '+@tablename

EXEC(@strQuery)

RETURN @rowCountVal

ERROR MESSAGE :

• Incorrect syntax near '@rowCountVal'
• Must declare scalar variable '@tablename'
• Must declare scalar variable '@rowCountVal'

---

whereas the code below works fine

ALTER PROCEDURE spROWCOUNTER
(
    @rowCountVal int OUTPUT
)

AS

SELECT @rowCountVal=COUNT(*) FROM DEFECT_LOG

RETURN @rowCountVal

Answer 1

The syntax problem is easy to solve. There is a missing comma (",") between your parameters. Insert the comma and the stored procedure compiles:

( @tablename nvarchar(20), @rowCountVal int OUTPUT )

Then, there is the major problem: you can't access the @rowCountVal parameter inside the EXEC statement. To solve this problem, you could use the built-in stored procedure sp_executesql.

Read this good article written by the SQL Server MVP Erland Sommarskog.

By the way: you don't have to "return" a variable. Return values are normally used for returning some status values. If you pass an output parameter, it will be automatically returned.

Answer 2

yes, i missed the comma. But even after that,the value doesnt get stored in @rowCountVal.

SET @strQuery = 'SELECT @rowCountVal=COUNT(*) *FROM '+@tablename

EXEC(@strQuery)

the query doesnt return nor displays any value.

---

btw i thought of callin this proc from other stored-procedures to get rowcounts. Will the following statement work :

set @rCount = exec spROWCOUNTER('DEFECT_LOG')

Answer 3

CREATE PROCEDURE spROWCOUNTER   
    @tablename nvarchar(20),    
    @rowCountVal int OUTPUT
AS

SELECT @rowCountVal = ISNULL(SUM(spart.rows), 0)
FROM sys.partitions spart 
WHERE spart.object_id = object_id(@tablename) AND spart.index_id < 2

RETURN @rowCountVal

Answer 4

thanx Thuglife

Answer 5

I would use sp_executesql instead of exec. Then you can pass in @rowCountVal as an output variable into the dynamic sql.

create PROCEDURE spROWCOUNTER
(
    @tablename nvarchar(20),
    @rowCountVal int OUTPUT
)

AS

DECLARE @strQuery nvarchar(300)

SET @strQuery = 'SELECT @rowCountVal = COUNT(*) FROM '+@tablename

exec sp_executesql @strQuery, N'@tablename nvarchar(20), @rowCountVal int OUTPUT',   @tablename = @tablename, @rowCountVal = @rowCountVal output


RETURN @rowCountVal

Answer 6

In an execute statement, you can use a temporary table to share data:

CREATE PROCEDURE spROWCOUNTER   
    @tablename nvarchar(20),    
    @rowCountVal int OUTPUT
AS

CREATE TABLE #Result( Rows INT )
EXEC( 'INSERT INTO #Result( Rows ) SELECT COUNT(*) FROM ' + @tablename )

SELECT @rowCountVal = Rows FROM #Result

RETURN @rowCountVal

Answer 7

if you want the row count as a function you can also check Speeding up the Performance of Table Counts in SQL Server 2005

Provided in the article function is apparently faster than calling count(*) for very big tables.