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
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
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 + '';''
[' + @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)));
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.