T-SQL: How Do I Create A "Private" Function Inside A Stored Procedure
I first tried to create another, temporary SP, from within an existing SP - which didn't work, but after experimenting a bit I think you could go with something like this (if you don't mind dynamic SQL):
CREATE PROCEDURE sp_myTest_v1_0(@firstName NVARCHAR(255)) AS
BEGIN
-- declare private method
DECLARE @privateMethod NVARCHAR(255), @privateMethodSig NVARCHAR(255)
SELECT @privateMethod =
'DECLARE @x INT' + CHAR(10) +
'WHILE ISNULL(@x,0) < 10 BEGIN' + CHAR(10) +
'PRINT @param1 + CAST(@x AS VARCHAR)' + CHAR(10) +
'SET @x = ISNULL(@x,0)+1' + CHAR(10) +
'END', @privateMethodSig = '@param1 NVARCHAR(255)'
-- call privateMethod
EXEC sp_executesql @privateMethod, @privateMethodSig, @param1 = @firstName
END
GO
RPM1984
~ Past ~: Mainframes (Model 204, JCL) Java (J2SE, J2EE) Oracle VB.NET ASP.NET Web Forms/MVC ~ Present ~ .NET Core TDD, DDD (all the DDs!) Microservices Containers
Updated on August 23, 2021Comments
-
RPM1984 over 2 years
Okay so I'm 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, I've 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 (doesn't 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 I've used it when I get an error =)
Can anyone help?
EDIT
Ok so I ended up creating a scalar UDF function (seems only way).
However, I have awarded the correct answer to Fredrik as although I don't plan to implement this, it is both a correct answer and a creative one at that.
Thanks for all the advice/help.