Help with Stored Procedure

Question

Post reply

Help with Stored Procedure

Lebanese

SSC-Addicted

Points: 461
More actions
April 24, 2010 at 6:47 pm

#231986

Hi all,
this is my first time writing a stored procedure.
all i want is to get the count of records in a table and assign it to an output variable. the table name is a variable, so everytime i want to execute this statement, i need to input the name of the table. so far the stored procedure works good but don't know how to assign the value of the count to an output. would it be possible to help me out?
thank you,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetMessageCount]
-- Add the parameters for the stored procedure here
@table_name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SET @table_name = RTRIM(@table_name)
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT count(*) FROM ' + @table_name
EXEC sp_executesql @cmd
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

Use the statement like this

DECLARE @OUTPUT_COUNT INT

EXEC @OUTPUT_COUNT = sp_GetMessageCount '<Your Table Name>'

SELECT @OUTPUT_COUNT

Lebanese SSC-Addicted Points: 461 More actions · Answer 2

Thanks ColdCoffee for the reply.

I don't see the sql statement that says 'select count(*) from' in your statement.

when i execute your statement it gives me an error that says 'invalid object name sp_GetMessageCount'

Would you please let me know what needs to be done or paste the whole sp so i know what you meant?

thank you so much

John Barnett SSCrazy Points: 2086 More actions · Answer 3

ColdCoffee's code runs your stored procedure from your posting. It assumes it is in the same database as your code and has the same owner.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

I guess your SP needs a bit more work than that, would this help?

--Create the procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_GetMessageCount]

-- Add the parameters for the stored procedure here

@table_name varchar(50) ,

@Return_Count int output

AS

BEGIN

SET NOCOUNT ON;

SET @table_name = RTRIM(@table_name)

DECLARE @cmd AS NVARCHAR(max)

DECLARE @param AS NVARCHAR(500)

SET @cmd = N'SELECT @return_count_Out = count(*) FROM ' + @table_name

SET @param = N'@return_count_Out int OUTPUT'

EXEC sp_executesql @cmd, @param,@return_count_Out= @Return_Count OUTPUT

END

--Test it

declare @C int

Exec [sp_GetMessageCount] '<tablename>', @C output

Select @C

---------------------------------------------------------------------------------

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 5

john barnett (4/25/2010)
ColdCoffee's code runs your stored procedure from your posting. It assumes it is in the same database as your code and has the same owner.

Spot On, John..

And Nabha's code is better version of your original SP.. I advice you to chage your proc as in Nabha's code above 🙂