I have two sprocs: I want the output char(1) value of first to pass to the second. When I run the second, it runs the first sproc successfully, but output value of first isn't passed to second one's internal parameters for decision making. How to get the output of one to be used for @received_value logic?
FIRST ONE checks if a report executed or not (checks ssrs execution logs)
If it did, will insert row into a temp table.
will check rowcount of temp table
will assign value 'Y' to @success param
will assign value of @success param AS@output OUTPUT param of FIRST stored procedure
SECOND SPROC is supposed to execute the first one and pass the value of @output OUTPUT of first sproc to param in the second sproc called @input.
Based on what value was passed, either 'Y' or 'N' have IF logic to do two different things.
The first and second sprocs run fine but the output of the first is not passed as input to the second.
--first sproc
USE MYDB
GO
/****** Object: StoredProcedure [dbo].[SSRS_check] Script Date: 9/7/2019 9:49:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SSRS_check] (@pid int, @date datetime, @output char(1) OUTPUT)
AS
---CHECK SSRS EXECUTION LOGS TO VERIFY IF REPORT SUCCESSFULY RAN OR NOT. If it was successful a row will be inserted into #ssrsresults table
SELECT @success = (select case when count(*) > 0 then 'Y' else 'N' end as success from #ssrsresults)
SELECT @recordsCount = (select count(*) from #ssrsresults)
IF @recordsCount = '0' and @success = 'N'
BEGIN
SET @body_N = 'Report: ''' + @reportname + ''' has NOT yet sent as of ' + cast(DATENAME(DW,@date) as varchar(25)) + ', ' + cast(@date as varchar(25)) + '. Last executed: ' + cast(datename(dw,max(@actualtime)) as varchar) + ', ' + cast(@actualtime as varchar(50)) + '. Scheduled execution: ' + CASE WHEN @schedule = 'Daily' then @schedule WHEN @schedule <> 'Daily' then concat('every ', @schedule) else '' end + ', ' + cast(@starttime as varchar(10))
PRINT @body_N
--EMAIL CLIENT THAT REPORT FAILED
SELECT @output = @success
END
IF @recordsCount = '1' and @success = 'Y'
BEGIN
SET @body_Y = 'Report: ''' + @reportname + ''' was sent today. Last executed: ' + cast(datename(dw,max(@actualtime)) as varchar(25)) + ', ' + cast(@actualtime as varchar(50)) + '. Scheduled execution: ' + CASE WHEN @schedule = 'Daily' then @schedule WHEN @schedule <> 'Daily' then concat('every ', @schedule) else '' end + ', ' + cast(@starttime as varchar(10))
PRINT @body_Y
SELECT @output = @success
END
--SECOND SPROC
declare @received_value char(1),
@input char(1),
@date datetime,
@id int
set @date = getdate()
set @id = 2639
EXEC @received_value = MYDB.dbo.SSRS_check1 @id, @date , @received_value OUTPUT;
SET @input = @received_value
IF @input = 'Y'
BEGIN
PRINT 'TABLE WILL BE UPDATED'
END
IF @input = 'N'
BEGIN
PRINT 'ALREADY SENT EMAIL TO NOTIFY'
END
GO
--Quote me
September 8, 2019 at 5:19 am
<accidental 3rd post > not used to new sqlservercentral format
--Quote me
September 8, 2019 at 9:55 pm
One reason the first proc is not functional is because the temporary table is out of scope. Where does #ssrsresults get created? If a temp table only has 1 '#' sign, then it is of local scope. If it has two '##' then it has global scope. Regarding how you're executing the procs. This could be looked at in different ways... it could be looked at like there are 2 ways to execute stored procedures: 1) the "regular way" which may or may not have output, and 2) the "scalar executed" way which must specify a single integer return value in the proc. Your code uses a nonfunctional combination of scalar execution and regular execution which is (I think one of the reasons) why it's not producing expected outcome. Have a look at this code:
scalar example:
-- scalar execution must explicitly return an integer
-- return value is whatever is specified in the procedure
drop proc if exists test_proc1;
go
create proc test_proc1
@var1int
as
begin
if @var1>0
return 9999;
else
return -1;
end
go
declare
@proc_integer_outcomeint;
exec @proc_integer_outcome = test_proc1 1;
print ('Scalar executed output: '+cast(@proc_integer_outcome as varchar(9)));
Produces this outcome:
Scalar executed output: 9999
Regular execution:
-- regular execution does not specify a return value
-- return values is 0 or ?? (some integer) depending on if the procedure executes (meaning without a fatal SQL Server malfuction or error).
-- if the return value is not 0 something went terribly wrong.
drop proc if exists test_proc2;
go
create proc test_proc2
@var1int,
@var2int output
as
begin
select @var2=@var1;
end
go
declare
@proc_integer_outcomeint,
@var2_outcomeint;
exec @proc_integer_outcome = test_proc2 5555, @var2=@var2_outcome output;
print ('Scalar executed output: '+cast(@proc_integer_outcome as varchar(9)));
print ('Procedure variable output: '+cast(@var2_outcome as varchar(9)));
Produces this outcome:
Scalar executed output: 0
Procedure variable output: 5555
Both scalar and regular execution always return an integer. Basically, scalar execution overrides the default behavior (which is strictly for error catching). AFAIK most people don't bother to check if the integer return value=0 if they're normally executing the procedure.
If the second proc is modified to divide by zero, the procedure variable will not be returned (because there was an error). However, the procedure will still (always) return a scalar value other than 0.
This code:
-- regular execution does not specify a return value
-- return values is 0 or ?? (some integer) depending on if the procedure executes "normally" (meaning without a fatal SQL Server malfuction or error).
-- if the return value is not 0 something went terribly wrong.
drop proc if exists test_proc2;
go
create proc test_proc2
@var1int,
@var2int output
as
begin
select @var2=@var1/0;
end
go
declare
@proc_integer_outcomeint,
@var2_outcomeint;
exec @proc_integer_outcome = test_proc2 5555, @var2=@var2_outcome output;
print ('Scalar executed output: '+cast(@proc_integer_outcome as varchar(9)));
print ('Procedure variable output: '+cast(@var2_outcome as varchar(9)));
Produces this outcome:
Msg 8134, Level 16, State 1, Procedure test_proc2, Line 6 [Batch Start Line 110]
Divide by zero error encountered.
Scalar executed output: -6
General advice FWIW: instead of first checking to see if something happened and then doing something, why not do something and then check to see if the outcome was good? Why send email to a client that something went wrong? If (all you do is) inform people there is "a problem" then they will associated you with the problem (and not the solution).
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 9, 2019 at 5:20 am
Thank you very much! Having one last issue: in calling procedure I'm getting error: Must declare the scalar variable "@output".
changes made
1 . moved sp_send_dbmail to the calling sproc, just because it DOES make more sense
2. set @output = to a select statement
3. did not change @output to int because many sites say output can be char datatype. http://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/ and https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-2017
4. changed # to ##ssrsresults....though I don't understand..by end of procedure the variables are loaded. shouldn't the ##ssrsresults be torn down in either event?
First procedure
USE MyDB
GO
/****** Object: StoredProcedure [dbo].[SSRS_check1] Script Date: 9/8/2019 10:59:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SSRS_check1] (@id int, @date datetime, @output char(1) OUTPUT)
as
set nocount on;
declare @recordscount int,
@reportname varchar(50),
@starttime varchar(15),
@interval smallint,
@maxstarttime time(0),
@scheduletype varchar(20),
@schedule varchar(15),
@body_N varchar(max),
@body_Y varchar(max),
@today varchar(20),
@actualtime datetime,
@actualday varchar(15)
set @id = 231
set @date = getdate()
set @reportname = (select name from ProcessEngine.dbo.P_Process where id = @id)
--@starttimes is plural, below considers multiple starttimes on same day
set @starttime = (select
distinct
stuff((
select ',' + cast(st.starttime as varchar(15))
from [ProcessEngine].[dbo].[P_StartTime] st
where id = @id
and cast(st.starttime as varchar(15)) = cast(st.starttime as varchar(15))
order by cast(st.starttime as varchar(15))
for xml path('')
),1,1,'') as starttime_list
from [ProcessEngine].[dbo].[P_StartTime]
where id = @id
group by cast(starttime as varchar(15)) )
--@maxstarttime is singular, finds the latest possible time a report can run that is less than current time; in case there is more than one execution per day
set @maxstarttime = ( select case when count(*) > 1 then ( select max(starttime) from [ProcessEngine].[dbo].[P_StartTime] where id = @id having max(starttime) < CONVERT(VARCHAR(5),getdate(),108) ) else starttime end as starttime
from [ProcessEngine].[dbo].[P_StartTime]
where id = @id
group by starttime )
--@scheduletype is singular, only one possible scheduletype
set @scheduletype = ( select ScheduleType from ProcessEngine.dbo.P_Schedule where id = @id )
--@schedule is singular, only one possible schedule
set @schedule = ( select case when (select schedule from ProcessEngine.dbo.P_Schedule where id = @id) is null then 'Daily'
when (select schedule from ProcessEngine.dbo.P_Schedule where id = @id) is not null then
(select schedule from ProcessEngine.dbo.P_Schedule where id = @id) end )
--@interval is singular, only one possible interval
set @interval = ( select interval from ProcessEngine.dbo.P_Schedule where id = @id )
set @actualtime = ( SELECT
max(e.timestart)
FROM MyDB.[dbo].[SSRS_Catalog] c join
MyDB.[dbo].[SSRS_ExecutionLog] e on c.itemid = e.ReportID join
ProcessEngine.dbo.P_Process p on
cast(c.Name as nvarchar) COLLATE DATABASE_DEFAULT= P.Name COLLATE DATABASE_DEFAULT join
[ProcessEngine].[dbo].[P_StartTime] s on p.id=s.id
WHERE p.Type = 'Reporting Services Report'
AND ( e.Format = 'EXCELOPENXML' --excel attached
OR e.Format = 'MHTML' ) --embedded
AND e.RequestType = 1 --subscription
AND p.id = @id
)
set @actualday = ( SELECT
datename(dw,max(e.timestart))
FROM MyDB.[dbo].[SSRS_Catalog] c join
MyDB.[dbo].[SSRS_ExecutionLog] e on c.itemid = e.ReportID join
ProcessEngine.dbo.P_Process p on
cast(c.Name as nvarchar) COLLATE DATABASE_DEFAULT= P.Name COLLATE DATABASE_DEFAULT join
[ProcessEngine].[dbo].[P_StartTime] s on p.id=s.id
WHERE p.Type = 'Reporting Services Report'
AND p.id = @id
)
IF (OBJECT_ID('TempDB.dbo.##SSRSRESULTS') IS NOT NULL)
DROP TABLE ##ssrsresults;
SELECT
P.name,
P.ID AS pid,
P.RptGrp1,
e.timestart as ActualStartTime,
e.timeend as ActualEndTime,
FormatDelivered = case when e.format = 'EXCELOPENXML' then 'Email with excel attached' else e.format end ,
s.StartTime as ExpectedStartTime,
output = case when count(*) > 0 then 'Y'
when count(*) = 0 then 'N' end
INTO ##ssrsresults
FROM MyDB.[dbo].[SSRS_Catalog] c join
MyDB.[dbo].[SSRS_ExecutionLog] e on c.itemid = e.ReportID join
ProcessEngine.dbo.P_Process p on
cast(c.Name as nvarchar) COLLATE DATABASE_DEFAULT= P.Name COLLATE DATABASE_DEFAULT join
[ProcessEngine].[dbo].[P_StartTime] s on p.id=s.id
WHERE p.Type = 'Reporting Services Report'
AND p.id = @id
AND cast(TimeStart as date)= cast(@date as date)
AND cast(TimeEnd as date) = cast(@date as date)
GROUP BY
P.name,
P.ID ,
P.RptGrp1,
e.timestart ,
e.timeend ,
e.format ,
s.StartTime ;
SELECT @output = ( select case when count(*) > 0 then 'Y' when count(*) = 0 then 'N' end as success from ##ssrsresults )
print @output
IF @output = 'N'
BEGIN
SET @body_N = 'Report: ''' + @reportname + ''' has NOT yet sent as of ' + cast(DATENAME(DW,@date) as varchar(25))+ ', ' + cast(@date as varchar(25)) + '.
Last executed: ' + cast(datename(dw,max(@actualtime)) as varchar(25))+ ', ' + cast(@actualtime as varchar(50)) + '.
Scheduled execution: ' + CASE WHEN @scheduletype is null and @schedule = 'Daily' then @schedule
WHEN @scheduletype is null and @schedule <> 'Daily' then concat('every ', @schedule)
WHEN @scheduletype = 'Continuous' and @schedule = 'Minutes' then concat('every ', @interval, ' ', @schedule)
WHEN @scheduletype = 'DayOfWeek' and @schedule in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') then concat('every ', @schedule)
WHEN @scheduletype = 'Weekdays' and @schedule IS NULL then concat('exclusively on ', @schedule)
WHEN @scheduletype = 'Weekly' and @schedule IS NULL then @schedule
else '' end + ', ' + @starttime
PRINT @body_N
END
IF @output = 'Y' and @starttime >= @maxstarttime
/* where max(starttime) that is < getdate() is = cast(getdate() as date)
could to be expanded to account for possibility of more than once scheduled execution per day. While currently not happening in any scopsbi reports, it may be in future
*/
BEGIN
SET @body_Y = 'Report: ''' + @reportname + ''' was sent today.
Last executed: ' + cast(datename(dw,max(@actualtime)) as varchar(25)) + ', ' + cast(@actualtime as varchar(50)) + '.
Scheduled execution: ' + CASE WHEN @scheduletype is null and @schedule = 'Daily' then @schedule
WHEN @scheduletype is null and @schedule <> 'Daily' then concat('every ', @schedule)
WHEN @scheduletype = 'Continuous' and @schedule = 'Minutes' then concat('every ', @interval, ' ', @schedule)
WHEN @scheduletype = 'DayOfWeek' and @schedule in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') then concat('every ', @schedule)
WHEN @scheduletype = 'Weekdays' and @schedule IS NULL then concat('exclusively on ', @schedule)
WHEN @scheduletype = 'Weekly' and @schedule IS NULL then @schedule
else '' end + ', ' + @starttime
PRINT @body_Y
END
drop table ##ssrsresults
GO
Calling PROCEDURE
--use MyDB
--go
--create procedure Update_DSTC (@pid int, @date datetime)
--as
declare
@date datetime,
@id int,
@new_output char(1) --variable to receive output value of called procedure
set @date = getdate()
set @id = 231
EXEC MyDB.dbo.SSRS_check @id, @date , @new_output = @output output; --save output to new_output
print @new_output
IF @new_output = 'Y'
BEGIN
PRINT 'CLAIMS TABLE WILL BE UPDATED'
--run claims table update statement
END
IF @new_output = 'N'
BEGIN
PRINT 'SEND EMAIL TO BIZ'
-- EXEC msdb.dbo.sp_send_dbmail
--@recipients =
----,@copy_recipients =
--,@from_address =
--,@subject = 'SSRS Alert'
--,@body = @body_N
END
--Quote me
September 9, 2019 at 12:21 pm
Hey alright, now the code is tl;dr but it seems like you're on your way!
#3, you're correct. The proc output variable(s) can be of any type. However, the RETURN type of the proc must be integer. Well, there is potentially more to explain if you're using ADO.NET to execute the proc. The ADO.NET methods to return single values are 'ExecuteScalar' and 'ExecuteScalarAsync'. Per the docs these methods: "Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored." If you're not using ADO.NET then just ignore this. When executing procedures the system design goal is to always avoid (if possible) allocating a 'SqlDataReader' object to access results data.
#4, we're both right. The way you had originally posted the code the creation of the temporary table was not done inside the proc. The code now contains the CREATE TABLE statement so you could use 1 # instead of ##. I had suspected your original post was probably trimmed down for readability. When posting on the forums it's nice to make it so the other person can just copy and paste from the site into SSMS.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
The specific issue of: Must declare the scalar variable "@output". This is because when you specify the output variable(s) in the execute statement the target variable needs to be on the right-hand side of the equals sign.
Instead of:
EXEC MyDB.dbo.SSRS_check @id, @date , @new_output = @output output; --save output to new_output
Try:
exec MyDB.dbo.SSRS_check @id, @date, @output=@new_output output;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 9, 2019 at 4:55 pm
thank you so much.
yes, i confused matters by using name @return_value for desired output variable.
Thank you for info on ado.net. Have made note of it.
by the time i wrote this sproc I felt I'd run a marathon and I just hoped someone would be able to help without me providing all DDL. I was worried pasting in whol sproc would make people want to avert eyes so cut out the details.
Thank you so much for looking at my work and helping me!
What does tl:dr mean? I think i just need to organize my code for readability, format text output for client readability, and finish the business logic. But I think it looks alright now.
--Quote me
September 9, 2019 at 7:09 pm
tl;dr stands for "too long; didn't read" so your worries were well founded! But seriously, what you posted worked and I'm very glad to have helped. Readability is always a consideration and sometimes it matters and sometimes not at all. For posting on the forum probably more sql is better than less but always less if less is possible 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply