Solved

procedure with OUTPUT

Posted on 2006-11-07
27
351 Views
Last Modified: 2008-02-01
i've got a proc with output parameters within.  it does a bunch of calculations, and returns data under certain conditions.  recently i received help revising that procedure such that it can invoke an email, IF output = 1 is returned.  

i'm trying to do that again, but am unable to because the proc i am working with now has parameters.  there were no parameters with the other one, i fire it like this:  

DECLARE @spcount BIT
EXEC db.dbo.procname @spcount OUTPUT

if data is to be returned, @output is set to 1.  so, if the proc has results, OUTPUT = 1
i'm trying to make a similar version of this with a new procedure, but this one has parameters and i'm having difficulty.  see, it's  a 3-step process

1) run the procedure
2) update a to-be-mailed flag ONLY if the procedure returns results
3) send the email, where the flag = 1 (email includes resultset)

so, if my proc has parameters, and is fired like this:   exec procname param1, param2, param3
how do i handle the OUTPUT?

i'm not sure if i voiced myself adequately.  any thoughts?
0
Comment
Question by:dbaSQL
  • 21
  • 6
27 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
not sure what you exactly need, but I guess it's this:

DECLARE @spcount BIT
exec procname param1, param2, param3 , @spcount OUTPUT

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
i tried that, angel, no good.  i get this:

Server: Msg 8144, Level 16, State 2, Procedure usp_recon, Line 0
Procedure or function procedure1 has too many arguments specified.

it's a driver that fires another procedure based on the value passed in

procedure1 fires procedure2,3,4,5 or 6, based on the endpoint passed in
i changed procedure 2 to handle the output, i just hadn't thought about doing it to the driver, too.  but that makes sense

i'm not sure how to put the output into procedure1 (driver)

here it is:

CREATE PROCEDURE procname (
 @sourceserver varchar(25),
 @sourcedb varchar(25),
 @endpointserver varchar(25),
 @endpointdb varchar(25),
 @endpoint varchar(10)
)
AS

SET NOCOUNT ON

IF @endpoint = 'aaaa'
      EXEC usp_aaaa @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint
ELSE IF @endpoint = 'bbbb'
      EXEC usp_bbbb @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint
ELSE IF @endpoint = 'cccc'
      EXEC usp_cccc @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint
ELSE IF @endpoint = 'dddd'
      EXEC usp_dddd @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint
ELSE IF @endpoint = 'eeee'
      EXEC usp_eeee @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint
ELSE IF @endpoint = 'ffff'
      EXEC usp_ffff @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint

SET NOCOUNT OFF
GO




0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
and then, after i alter the driver (@output bit output up top, return output down bottom), it fails with this:

Server: Msg 201, Level 16, State 4, Procedure usp_ISLD, Line 0
Procedure 'procedure2' expects parameter '@output', which was not supplied.
The 'procedure1' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

remember, procedure1 is the driver proc, it fires procedure2,3,4,5 or 6, based on the endpoint passed in
(of course, the procedure names are changed, but you know what i mean)
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
and, in this case we're talking about procedure2.  it's a recon proc.  it does comparisons between two datasources - if countA <> countB, it returns the discrepancies.  i did this:

CREATE PROC dbo.procedure2(
  @variable1 varchar(25),
  @variable2 varchar(25),
  @variable2 varchar(25),
  @variable3 varchar(25),
  @variable4 varchar(10),
  @output bit output
)
AS
SET NOCOUNT ON
SET @OUTPUT = 0

IF (SELECT COUNT(*) FROM '+@sourcesrv+'.'+@sourcedb+'......)
<>
(SELECT COUNT(*) FROM '+@destinationsrv+'.'+@destinationdb+'........)
BEGIN
SET @OUTPUT = 1
....
....... move data to tmp table so we can later review.......
..........
EXEC (@sql1 + @sql2)
RETURN @OUTPUT
GO
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
and where is that famous parameter @spcount OUTPUT in there?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
i was just going to say 'oh my'  and turn around and do it.
but i'm not sure, angel.  i thought i could just declare & call it when i'm firing the proc.  no?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
wait, rephrase that.  i modified the driver such that it includes @spcount
should it also be within the other procs that the driver will call?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
maybe it only needs to be in the driver?  both @spcount and @output?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
ok, angel, i didn't realize the time until just now.  i have to run.  i'll be back at this later, i am sure.  i am anxious to see what you suggest
and thanks, angel, for your input
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
angel, in the proc that i've done where this works, @spcount is only done when I call it.  like this:

DECLARE @spcount BIT
exec procname @spcount OUTPUT

wouldn't i do the same here?  and of the @output, would it be in the driver AND in the procs the driver calls?  here's the driver:

CREATE PROCEDURE procname (
 @sourceserver varchar(25),
 @sourcedb varchar(25),
 @endpointserver varchar(25),
 @endpointdb varchar(25),
 @endpoint varchar(10),
 @output BIT output
)
AS

SET NOCOUNT ON
DECLARE @spcount BIT

IF @endpoint = 'aaaa'
     EXEC usp_aaaa @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'bbbb'
     EXEC usp_bbbb @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'cccc'
     EXEC usp_cccc @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'dddd'
     EXEC usp_dddd @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'eeee'
     EXEC usp_eeee @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'ffff'
     EXEC usp_ffff @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT

SET NOCOUNT OFF
RETURN @output
GO
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
A small correction:

CREATE PROCEDURE procname (
 @sourceserver varchar(25),
 @sourcedb varchar(25),
 @endpointserver varchar(25),
 @endpointdb varchar(25),
 @endpoint varchar(10),
 @output BIT output
)
AS

SET NOCOUNT ON
DECLARE @spcount BIT

IF @endpoint = 'aaaa'
     EXEC usp_aaaa @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'bbbb'
     EXEC usp_bbbb @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'cccc'
     EXEC usp_cccc @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'dddd'
     EXEC usp_dddd @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'eeee'
     EXEC usp_eeee @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT
ELSE IF @endpoint = 'ffff'
     EXEC usp_ffff @sourceserver, @sourcedb, @endpointserver, @endpointdb, @endpoint, @spcount OUTPUT

SET NOCOUNT OFF
SET @output = @spcount
GO
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
but when i try to invoke, i receive:

Server: Msg 8144, Level 16, State 2, Procedure PROCEDURE2, Line 0
Procedure or function PROCEDURE2 has too many arguments specified.

i'm calling it like this:

DECLARE @spcount BIT
exec procedure1 'param1','param2','param3','param4', @spcount OUTPUT

remember, procedure1 is the driver, it invokes procedure2, procedure3, procedure4, etc., based on the endpoint passed in
on that note, i first modified procedure 2 w/the @output and SET @output, but i pulled that thinking i only needed to revise the driver
is that incorrect?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
well, do the procedures PROCEDURE2 ... do have that additional output parameter?
this looks like they don't
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
no, not anymore.  (i tried that yesterday, to no avail)
so, is it akin to what i've just done in the driver?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@output'.

now included w/parameters:   @output bit output

then, after SET NOCOUNT ON (dynamic proc), SET @output = 0
then, within the proc, if this exists, SET @output = 1,  then do this.....
then, after EXEC (@sql1 + @sql2), RETURN @output

that's it, that's all i've done to modify procedure 2.  obviously i'm missing something
what do you think, angel?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
possibly because the proc is dynamic?  it's not seeing me set the param if/when something exists
maybe do you want to see it, angel?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
i've tried everything, angel, it's not happening.  please let me know what you think, if you can
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
can anybody possibly suggest the right way for me to add this parameter to procedure2 ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
now:
if you have a PARAMETER of type OUTPUT, you do NOT need the RETURN for that value.
simply assign the parameter value (SET) and that's it

now,
>Must declare the variable '@output'.

this looks like you are trying to use the variable @output inside the dynamic sql, where it is not awailable unless you use sp_executesql instead of exec:

declare @output bit
exec sp_executesql @sql1 + @sql2, N'@output BIT output' , @output OUTPUT

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
ok, angel, i've pulled the RETURN from procedure2.   also, same proc, i've replaced this:  EXEC (@sql1 + @sql2)
w/this:  EXEC sp_executesql @sql1 + @sql2, N'@output BIT OUTPUT', @output OUTPUT

but creation fails syntactically:  
Server: Msg 170, Level 15, State 1, Procedure procedure2, Line 111
Line 111: Incorrect syntax near '+'.

so, i changed it to this:  EXEC sp_executesql @sql1,@sql2, N'@output BIT OUTPUT', @output OUTPUT   (don't feel very good, just reaching)
it creates fine, but when i run this:

DECLARE @spcount BIT
exec server.database.dbo.driverproc 'param1','param2','param3','param4', @spcount OUTPUT

i get this:

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 111
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

i know this is getting burden-ish.  i apologize
i really am thankful for whatever direction you can provide
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you need to declare the variables @sql1 and @sql2 as nvarchar
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
fails:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near ')'.
Server: Msg 137, Level 15, State 1, Line 19
Must declare the variable '@output'.

do you have time to look at it real quick, angel?  
(the @wclause isn't in use in this one right now, but that shouldn't have anything to do with this)

>>>>>>>>
CREATE PROC dbo.procedure2 (
  @sourcesrv varchar(25),
  @sourcedb varchar(25),
  @endpointsrv varchar(25),
  @endpointdb varchar(25),
  @endpoint varchar(10),
  @output bit output
)
AS
SET NOCOUNT ON
DECLARE @spcount BIT


declare @sql1 NVARCHAR(2000)
declare @sql2 NVARCHAR(2000)
declare @wclause VARCHAR(1000)

SET @output = 0

IF @sourcesrv = 'SERVER1'
SET @wclause = 's.ordernumber =
Right(d.ordernumber, Len(d.ordernumber) - (Patindex(''%[^0]%'',d.ordernumber) - 1)))'
ELSE
IF @sourcesrv IN ('SERVER2','SERVER3','SERVER4')
SET @wclause = 'Right(s.ordernumber, Len(s.ordernumber) - (Patindex(''%[^0]%'',s.ordernumber) - 1)))=
d.ordernumber'

--VALIDATE WHETHER THERE IS A DISCREPANCY / INSERT INTO SEQUENCE TABLE
SET @sql1 = 'DELETE FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.Sequence
IF (SELECT COUNT(*) FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.database
WHERE tradetime BETWEEN LEFT(getdate()-0, 11) AND LEFT(DATEADD(dy, 1, GETDATE()),11)
AND endpoint = '''+@endpoint+''')<>
(SELECT COUNT(*) FROM '+@endpointsrv+'.'+@endpointdb+'.dbo.database
WHERE tradetime BETWEEN LEFT(getdate()-0, 11) AND LEFT(DATEADD(dy, 1, GETDATE()),11)
AND endpoint = '''+@endpoint+''')
BEGIN
SET @output = 1
INSERT '+@sourcesrv+'.'+@sourcedb+'.dbo.Sequence
SELECT snumber FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.database s
WHERE tradetime BETWEEN LEFT(getdate()-0, 11) AND LEFT(DATEADD(dy, 1, GETDATE()),11)
AND endpoint = '''+@endpoint+'''
AND NOT EXISTS
(SELECT 1 FROM '+@endpointsrv+'.'+@endpointdb+'.dbo.database d
WHERE tradetime BETWEEN LEFT(getdate()-0, 11) AND LEFT(DATEADD(dy, 1, GETDATE()),11)
AND endpoint = '''+@endpoint+'''
AND d.side = s.side
AND s.orderid=d.orderid
OR

STUFF(s.orderid, 11, PATINDEX(''%[^0]%'', SUBSTRING(s.orderid, 11, 50)) - 1, '''')=
STUFF(d.orderid, 11, PATINDEX(''%[^0]%'', SUBSTRING(d.orderid, 11, 50)) - 1, '''')

)
END
ELSE
BEGIN
PRINT ''No discrepancies have been found.''
END
'

--REVIEW THEM
SET @sql2 = 'IF (SELECT COUNT(*) FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence) > 0
BEGIN
SELECT tradetime,userID,AcctID,endpoint,Symbol,side,Quantity,Price,RTRIM(ordernumber) AS Ordernumber,SNumber,orderid,Host
FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.database s
WHERE tradetime BETWEEN LEFT(getdate()-0, 11) AND LEFT(DATEADD(dy, 1, GETDATE()),11)
AND endpoint = '''+@endpoint+'''
AND EXISTS
(SELECT 1 FROM '+@sourcesrv+'.'+@sourcedb+'.dbo.sequence d WHERE s.snumber=d.snumber)
END '

--EXEC (@sql1 + @sql2)
EXEC sp_executesql @sql1, @sql2, N'@output BIT OUTPUT', @output OUTPUT
GO
>>>>>>>>>>>
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
i've put the @wclause back in...still no go

....
.......
AND endpoint = '''+@endpoint+'''
AND s.side = d.side
AND s.orderID = d.orderID
OR '+@wclause+'
)

END
ELSE
BEGIN
PRINT ''No discrepancies have been found.''
END
'
....
...
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
i am at a loss, any guidance truly is appreciated
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
the problem persists badly.   i am maybe going to suggest that it be closed, as i don't want to leave it open indefinitely
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
is anyone able to advise?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
the problem persists, angel, but i will award and close, as i don't want to leave this inquiry open indefinitely.
the priority has subsided just a bit, so maybe i can just take it back to the board and start over
thank you very much for having taken a look
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now