Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

procedure with OUTPUT

Posted on 2006-11-07
27
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 6
27 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17892571
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
ID: 17892657
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
ID: 17892772
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:dbaSQL
ID: 17892807
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 143

Expert Comment

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

Author Comment

by:dbaSQL
ID: 17892865
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
ID: 17892892
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
ID: 17892906
maybe it only needs to be in the driver?  both @spcount and @output?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17892923
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
ID: 17897659
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 17897711
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
ID: 17897786
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17897838
well, do the procedures PROCEDURE2 ... do have that additional output parameter?
this looks like they don't
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17897858
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
ID: 17898890
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
ID: 17898953
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
ID: 17899808
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
ID: 17903110
can anybody possibly suggest the right way for me to add this parameter to procedure2 ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17904302
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
ID: 17905708
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 143

Expert Comment

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

Author Comment

by:dbaSQL
ID: 17905870
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
ID: 17906032
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
ID: 17907522
i am at a loss, any guidance truly is appreciated
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17916483
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
ID: 17939420
is anyone able to advise?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17998057
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

604 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