Feb 15 2006

Dynamic Sql Query with Output Parameters

Category: Databases & SQL — Bil@l @ 14:13

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 = @GroupId
END
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

Tags:

Comments are closed