Dynamic Sql Query with Output Parameters
I had a previous post on how to use Dynamic Sql Queries.
Today I had the need to create a Stored Procedure that returns an Output Parameter, where the value of the output parameter is set inside the Dynamic Query.
So here is a sample Stored Procedure that shows how to do s:
CREATE PROCEDURE GetNotificationIdByCustomerId
(
@CustomerId INT,
@GroupId INT,
@NotificationId INT OUTPUT
)
AS
BEGIN
DECLARE @strQuery NVARCHAR(4000)
DECLARE @parameterList NVARCHAR(4000)
SELECT @strQuery =
N'SELECT
TOP 1 @NotificationId = NotificationId
FROM
Notifications
WHERE (CustomerId = @CustomerId) AND (Processed = 0) '
IF ( @GroupId <> -1 )
BEGIN
SELECT @strQuery = @strQuery + N' AND (GroupId = @GroupId)'
END
SELECT @parameterList = N'@NotificationId INT OUTPUT, @CustomerId INT, @GroupId INT' EXECUTE SP_EXECUTESQL @strQuery, @parameterList, @NotificationId OUTPUT,
@CustomerId = @CustomerId, @GroupId = @GroupIdEND
GO
I need to get @NotificationId, which is set inside the dynamic query, I then supply a list of parameters inside the @parameterList, where I specify the output parameter.
Hope that helps,
Regards