T-SQL: How Do I Create A "Private" Function Inside A Stored Procedure
- by RPM1984
Okay so im writing a SQL Server 2008 Stored Procedure (maintenance script).
In doing so, being a good boy i've done plenty of error handling, checking rowcounts, printing output messages, etc
But in doing this, ive found myself writing over and over again something like this:
SELECT @RowsAffected = @@ROWCOUNT
IF @RowsAffected > 0
BEGIN
PRINT CAST(@RowsAffected, NVARCHAR(2)) + 'rows updated.'
END
Or debug messages like this:
PRINT 'User ' + CAST(@UserId AS NVARCHAR(5)) + ' modified successfully'
Is there a way i can create a kind of 'subroutine' inside the stored procedure (like a private method) that can accept something as a parameter (doesnt have to though) and do some logic?
I want to be able to do something like this:
CheckRowCounts
Or this:
PrintUserUpatedMessage(@UserId)
Which would then perform the above logic (check rowcount, print message, etc)
And yes obviously i can create a UDF, but then i would need to create/drop it etc as this logic is only required for the life of the execution of this stored procedure.
Getting sick and tired of writing the same code over and over again, and changing all the different areas ive used it when i get an error =)
Can anyone help?
