CODEWITHSUNDEEP
sql-servervisual-studiosql-server-2005stored-procedures
kk.
kk.

Reputation: 687

Syntax Prob: Using Variables in Stored Procedures

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 :

whereas the code below works fine

ALTER PROCEDURE spROWCOUNTER
(
    @rowCountVal int OUTPUT
)

AS

SELECT @rowCountVal=COUNT(*) FROM DEFECT_LOG

RETURN @rowCountVal

Upvotes: 1

Views: 6515

Answers (6)

kk.
kk.

Reputation: 687

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

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

EXEC(@strQuery)

the query doesnt return nor displays any value.

By the way, I thought of calling this proc from other stored-procedures to get rowcounts. Will the following statement work :

set @rCount = exec spROWCOUNTER('DEFECT_LOG')

Upvotes: 0

Mitkins
Mitkins

Reputation: 4341

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

Upvotes: 1

kristof
kristof

Reputation: 53834

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.

Upvotes: 1

Ian1971
Ian1971

Reputation: 3696

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

Upvotes: 1

Thuglife
Thuglife

Reputation: 404

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

Upvotes: 3

splattne
splattne

Reputation: 104040

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.

Upvotes: 1

Related Questions