How to output the count value within this SP

[douggy]

I have the following procedure which works fine. It give a count from a given table (variable @tablename). Not sure how to fit in an output variable to present back the count result.

Can anyone help?
============
CREATE PROC sp_tablecount
(@tablename varchar(128))

AS

EXEC ('SELECT count(*) FROM ' + @tablename )
GO

=========


Many thanks
Mark

 

[tlbroadbent]

Here is an example using sp_executeSQL to return a value

CREATE PROC sp_tablecount
@tablename nvarchar(128),
@count OUTPUT
AS

Declare @sql nvarchar(100)
Set @sql=N'SELECT @count=count(*) FROM ' + @tablename

EXEC sp_executesql
@SQLString,
N'@count int Output',
@count=@count Output
GO

Execute the SP like this.

Declare @return_count int
Exec sp_tablecount N'table_name', @return_count Output
Print @return_count Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.