CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
SuicideSheep
SuicideSheep

Reputation: 5550

Understanding of SQL SErver Stored Procedure

Create  PROCEDURE alertCount 
(
@field1 VARCHAR(200),
@field2 INT,
@field3 INT,
@field4 INT,
@field5 INT,
@field6 INT,

@noOfCount INT  OUT        
)
AS
BEGIN
SELECT @noOfCount = COUNT(*) from tableA
END

I'm very new to stored procedure and based on some tutorial, the code above helps me to create a procedure (I hope it works fine). 

Declare @noOfCount as INT
Exec alertCount asd, 1, 1, 1, 1, 1, @noOfCount
select @noOfCount

Now the above code suppose to return me 9 as there are 9 rows of records in my tableA but its returning me null instead. May I know what's wrong with it?

P/S: Please don't bother about the logic. I'm just trying to understand a very simple stored procedure. Thank You.

Upvotes: 1

Views: 100

Answers (1)

roman
roman

Reputation: 117550

you have to pass parameter with out (or output keyword)

Declare @noOfCount as INT
Exec usp_AlertCount asd, 1, 1, 1, 1, 1, @noOfCount out
select @noOfCount

From my expirience, it's also better to pass parameters by names (easier to maintain):

declare @noOfCount int

exec usp_AlertCount
    @field1 = 'asd',
    @field2 = 1,
    @field3 = 1,
    @field4 = 1,
    @field5 = 1,
    @field6 = 1, 
    @noOfCount = @noOfCount output

select @noOfCount

sql fiddle demo

Upvotes: 6

Related Questions