Assigning results of stored procedure with parameter to variable

  • Hi all

    We've got a stored procedure that gives a list of email addresses based on a parameter and we want to assign this list (a single field) to a variable so it can be used elsewhere.
    The code for the stored procedure is (this is the current version):-
    CREATE PROCEDURE [dbo].[usp_Email_Recipients]

        -- Add the parameters for the stored procedure here
        @field    VARCHAR(100)
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;

            -- Insert statements for procedure here
                @localfield VARCHAR(100)
                ,@sql VARCHAR(MAX);

            IF @field IS NOT NULL
                    SET @localfield = @field;
                    SET @sql = 'SELECT
        CAST(LEFT(Recipient,LEN(Recipient) - 1) AS VARCHAR(2000)) AS Recipients
                Recipient = (SELECT
                        EmailAddress + '';''
                        [DW-LANDING\CASESENSITIVE].[Utilities].[dbo].[tbl_Email_Recipients] e1
                        [' + @localfield + '] = ''y''
                        and CAST(GETDATE() AS DATE) NOT BETWEEN OutOfOfficeStart AND OutOfOfficeEnd
                    FOR XML PATH (''''))) AS recip';

                    IF @localfield IN
                        ('DataWarehouseStagingFailure','DataWarehouseFailure', 'DataWarehouseCubeFailure', 'LookupsComplete', 'LookupsFailure')
                            SET @sql = REPLACE(@sql, @localfield, @localfield + '_' + RIGHT(@@SERVERNAME, LEN(@@SERVERNAME) - CHARINDEX('-', @@SERVERNAME)));

                    EXEC (@sql)


    What I need to do is assign the "Recipients" field that is the end-product of the stored procedure to a variable so it can be used in a trigger.

    I've seen the OUTPUT clause for pushing stored procedure results to variables (with examples) but I'm struggling to get it to work.
    I think it's something to do with the fact that I'm having to use dynamic SQL to get the results but I'm not sure.

    Any help on this would be appreciated.

  • You can execute the proc into a temp table, then use that to parse the string.

    Drop procedure if exists sp_test

    create proc sp_test as select 'string1, string2, string3' n

    drop table if exists #test

    create table #test (N varchar(max))
    insert into #test
    execute sp_test

    select * from #test

    For better, quicker answers, click on the following...

    For better answers on performance questions, click on the following...

  • Completely forgot about doing it that way :blush:
    That will probably solve my problem.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply