December 5, 2018 at 8:13 am
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)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE
@localfield VARCHAR(100)
,@sql VARCHAR(MAX);
IF @field IS NOT NULL
BEGIN
SET @localfield = @field;
SET @sql = 'SELECT
CAST(LEFT(Recipient,LEN(Recipient) - 1) AS VARCHAR(2000)) AS Recipients
FROM
(SELECT
Recipient = (SELECT
EmailAddress + '';''
FROM
[DW-LANDING\CASESENSITIVE].[Utilities].[dbo].[tbl_Email_Recipients] e1
WHERE
[' + @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')
BEGIN
SET @sql = REPLACE(@sql, @localfield, @localfield + '_' + RIGHT(@@SERVERNAME, LEN(@@SERVERNAME) - CHARINDEX('-', @@SERVERNAME)));
END;
EXEC (@sql)
END;
END;
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.
December 5, 2018 at 8:20 am
Doesn't sp_executesql work for you? https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017
December 5, 2018 at 9:26 am
You can execute the proc into a temp table, then use that to parse the string.
Drop procedure if exists sp_test
go
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
go
select * from #test
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2018 at 9:33 am
Completely forgot about doing it that way :blush:
That will probably solve my problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply