|
|
You're in the
sql server programming
group:Calling a SP from an SP
sql server programming:
I have two stored procedures in SQL 2000. Procedure 1 (procMfrGageLookup) - looks up an item by part number, @MfrGageLookup, and returns the description, vendor, and price from the tblMfrGages. Procedure 2 (procMfrReplacedGageLookup) - looks up the above part number to see if it has been replaced with a new part from the tblMfrReplacedGages. After the user enters the part number (@MfrGageLookup), I have the ReplacedGageLookup procedure check to see if it has been replaced with a new part number, if so, replace @MfrGageLookup with the replacement part number and lookup the new part number to see if it has been replaced, etc - looping until it doesn't find a match. At that time I want the MfrGageLookup procedure to lookup the info for the latest part number. Everytime I try to run the procedure, I get the following error: Server: Msg 201, Level 16, State 4, Procedure procMfrGageLookup, Line 0 Procedure 'procMfrGageLookup' expects parameter '@GageDesc', which was not supplied. If run from query analyzer on it's own, procMfrGageLookup works great without any errors. Can someone tell me what I'm doing wrong? Below are the procedures: ----- procMfrGageLookup DECLARE @MfrGageLookup nvarchar(32), @GageDesc nvarchar(64) OUTPUT, @VendName nvarchar(30)OUTPUT, @GagePrice money OUTPUT AS (SELECT tblMfrGages.MfrGageModel, tblVendorHeader.VendName, tblMfrGages.MfrGageDesc, tblMfrGages.MfrGagePrice FROM tblMfrGages INNER JOIN tblVenderHeader ON tblMFrGages.MfrName = tblVendorHeader.VendID WHERE (tblMfrGages.MfrGageModel = @MfrGageLookup)) ----- procMfrReplacedGageLookup @MfrGageLookup nvarchar(32) @NewModel nvarchar(32) OUTPUT AS (SELECT tblMfrReplacedGages.MfrOldGageModel, tblMfrReplacedGages.MfrNewGageModel FROM tblMfrReplacedGages WHERE (tblMfrReplacedGages.MfrOldGageModel = @MfrGageLookup)) --A replacement WAS found IF @NewModel IS NOT NULL BEGIN SET @MfrGageLookup = @NewModel SET @NewModel = NULL EXEC procMfrReplacedGageLookup END --A replacement is NOT found ELSE EXEC procMfrReplacedGageLookup @MfrGageLookup Thank you in advance for your assistance. Cevin
I've taken this example from the BOL
See, how you should call a stored procedure which has an output parameter CREATE PROCEDURE get_sales_for_title @title varchar(80), -- This is the input parameter. @ytd_sales int OUTPUT -- This is the output parameter. AS -- Get the sales for the specified title and -- assign it to the output parameter. SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title RETURN --- The following program executes the stored procedure with a value for the input parameter and saves the output value of the stored procedure in the @ytd_sales_for_title variable local to the calling program. -- Declare the variable to receive the output value of the procedure. DECLARE @ytd_sales_for_title int -- Execute the procedure with a title_id value -- and save the output value in a variable. EXECUTE get_sales_for_title "Sushi, Anyone?", @ytd_sales = @ytd_sales_for_title OUTPUT -- Display the value returned by the procedure. PRINT 'Sales for "Sushi, Anyone?": ' + convert(varchar(6),@ytd_sales_for_title) GO Sales for "Sushi, Anyone?": 4095 [quoted text, click to view] "Cevin" <Cevin@No_Spam_CharterMI.net> wrote in message news:1kPah.328$807.323@newsfe06.lga... > Greetings, > > I have two stored procedures in SQL 2000. > > Procedure 1 (procMfrGageLookup) - looks up an item by part number, > @MfrGageLookup, and returns the description, vendor, and price from the > tblMfrGages. > Procedure 2 (procMfrReplacedGageLookup) - looks up the above part number > to see if it has been replaced with a new part from the > tblMfrReplacedGages. > > After the user enters the part number (@MfrGageLookup), I have the > ReplacedGageLookup procedure check to see if it has been replaced with a > new part number, if so, replace @MfrGageLookup with the replacement part > number and lookup the new part number to see if it has been replaced, > etc - looping until it doesn't find a match. At that time I want the > MfrGageLookup procedure to lookup the info for the latest part number. > > Everytime I try to run the procedure, I get the following error: > Server: Msg 201, Level 16, State 4, Procedure procMfrGageLookup, Line 0 > Procedure 'procMfrGageLookup' expects parameter '@GageDesc', which was not > supplied. > > If run from query analyzer on it's own, procMfrGageLookup works great > without any errors. > > Can someone tell me what I'm doing wrong? > > Below are the procedures: > ----- > procMfrGageLookup > DECLARE > @MfrGageLookup nvarchar(32), > @GageDesc nvarchar(64) OUTPUT, > @VendName nvarchar(30)OUTPUT, > @GagePrice money OUTPUT > AS > (SELECT tblMfrGages.MfrGageModel, tblVendorHeader.VendName, > tblMfrGages.MfrGageDesc, tblMfrGages.MfrGagePrice > FROM tblMfrGages > INNER JOIN tblVenderHeader ON tblMFrGages.MfrName = tblVendorHeader.VendID > WHERE (tblMfrGages.MfrGageModel = @MfrGageLookup)) > ----- > procMfrReplacedGageLookup > @MfrGageLookup nvarchar(32) > @NewModel nvarchar(32) OUTPUT > AS > (SELECT tblMfrReplacedGages.MfrOldGageModel, > tblMfrReplacedGages.MfrNewGageModel > FROM tblMfrReplacedGages > WHERE (tblMfrReplacedGages.MfrOldGageModel = @MfrGageLookup)) > > --A replacement WAS found > IF @NewModel IS NOT NULL > BEGIN > SET @MfrGageLookup = @NewModel > SET @NewModel = NULL > EXEC procMfrReplacedGageLookup > END > > --A replacement is NOT found > ELSE > EXEC procMfrReplacedGageLookup @MfrGageLookup > > Thank you in advance for your assistance. > Cevin >
Hugo,
Thank you for the information. Perhaps a couple clarifications may help... --The only purpose of procMfrReplacedGageLookup is to replace the user input (@MfrGageLookup) with the latest replacement gage number, if there is one. This would be transparent to the user. The calling application (access or a web page) would store the original user input for user notification that it has been replaced with a new gage number. --procMfrReplacedGageLookup will loop as long as it finds a replacement gage number (replacing @MfrGageLookup with new gage number each loop). This is because some gage numbers have been changed several times over the years and some original gage numbers are still in use. For instance, you buy gage number #1 ten years ago, it has been replaced with gage number #2 five years ago, and gage number #3 two years ago. If the user inputs #1 procMfrReplacedGageLookup would iterate 3 times (looking for #1, then #2, then #3) before finding that #3 has not been replaced and is actually the latest gage number. --procMfrGageLookup should only ever be run AFTER procMfrReplacedGageLookup. --procMfrReplacedGageLookup would supply the latest gage number to procMfrGageLookup which, I'm hoping will return all the OUTPUT info directly to the user. I assumed that the procMfrGageLookup OUTPUT would go to the user. But now, am I correct in thinking that it will return the OUTPUT back to procMfrReplacedGageLookup? Which would mean that I'd have to declare the three OUTPUT's from procMfrGageLookup as INPUT (initially set to NULL) and as OUTPUT in procMfrReplacedGageLookup who then passes it back to the user? Thanks again for your help. I hope the clarifications help to understand my final intentions. I intend to use this situation for other uses, if I can get it right. Cevin [quoted text, click to view] "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message news:upepm2hq9nnant6pk40emdtsolpdeio8tj@4ax.com... > On Mon, 27 Nov 2006 23:56:33 -0500, Cevin wrote: > > (snip) >>Everytime I try to run the procedure, I get the following error: >>Server: Msg 201, Level 16, State 4, Procedure procMfrGageLookup, Line 0 >>Procedure 'procMfrGageLookup' expects parameter '@GageDesc', which was not >>supplied. > (snip) > > Hi Cevin, > > You need to supply all parameters to procMfrReplacedGageLookup here. > Probably (but double-check, since I'm guessing at your intentions) > something like this: > > EXEC procMfrReplacedGageLookup > @MfrGageLookup = @MfrGageLookup, > @NewModel = @NewModel OUTPUT > >>END >> >>--A replacement is NOT found >>ELSE >> EXEC procMfrReplacedGageLookup @MfrGageLookup > > And here, you have to add ALL parameters instead of just one: > > EXEC procMfrReplacedGageLookup > @MfrGageLookup = @MfrGageLookup, > @GageDesc = @GageDesc OUTPUT, > @VendName = @VendName OUTPUT, > @GagePrice = @GagePrice OUTPUT > > And you should declare the targets of the output parameters as well. > > On the other hand, I see that you never actually put anything in the > OUTPUT parameters. So instead of the modification above, another way to > fix this is to simply remove the three OUTPUT parameters from the > signature of procedure procMfrReplacedGageLookup. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Mon, 27 Nov 2006 23:56:33 -0500, Cevin wrote: >Greetings, > >I have two stored procedures in SQL 2000. (snip) >Everytime I try to run the procedure, I get the following error: >Server: Msg 201, Level 16, State 4, Procedure procMfrGageLookup, Line 0 >Procedure 'procMfrGageLookup' expects parameter '@GageDesc', which was not >supplied. (snip) >procMfrReplacedGageLookup >@MfrGageLookup nvarchar(32) >@NewModel nvarchar(32) OUTPUT >AS >(SELECT tblMfrReplacedGages.MfrOldGageModel, >tblMfrReplacedGages.MfrNewGageModel >FROM tblMfrReplacedGages >WHERE (tblMfrReplacedGages.MfrOldGageModel = @MfrGageLookup)) > >--A replacement WAS found >IF @NewModel IS NOT NULL >BEGIN > SET @MfrGageLookup = @NewModel > SET @NewModel = NULL > EXEC procMfrReplacedGageLookup Hi Cevin, You need to supply all parameters to procMfrReplacedGageLookup here. Probably (but double-check, since I'm guessing at your intentions) something like this: EXEC procMfrReplacedGageLookup @MfrGageLookup = @MfrGageLookup, @NewModel = @NewModel OUTPUT [quoted text, click to view] >END > >--A replacement is NOT found >ELSE > EXEC procMfrReplacedGageLookup @MfrGageLookup And here, you have to add ALL parameters instead of just one: EXEC procMfrReplacedGageLookup @MfrGageLookup = @MfrGageLookup, @GageDesc = @GageDesc OUTPUT, @VendName = @VendName OUTPUT, @GagePrice = @GagePrice OUTPUT And you should declare the targets of the output parameters as well. On the other hand, I see that you never actually put anything in the OUTPUT parameters. So instead of the modification above, another way to fix this is to simply remove the three OUTPUT parameters from the signature of procedure procMfrReplacedGageLookup. --
[quoted text, click to view]
On Tue, 28 Nov 2006 20:56:55 -0500, Cevin wrote: (snip) [quoted text, click to view] >I assumed that the procMfrGageLookup OUTPUT would go to the user. But now, >am I correct in thinking that it will return the OUTPUT back to >procMfrReplacedGageLookup? Which would mean that I'd have to declare the >three OUTPUT's from procMfrGageLookup as INPUT (initially set to NULL) and >as OUTPUT in procMfrReplacedGageLookup who then passes it back to the user? Hi Cevin, There are basically three ways to return results from a stored procedure: 1. In an OUTPUT parameter. This requires that the OUTPUT keyword be present in both the signature of the stored proc and in the EXEC statement that calls the proc. The stored proc can set the value of the variable, and the new value will be available in the variable that the caller substituted for the parameter. 2. As a result set. Any SELECT statement will be executed and any results from such SELECT statements will be sent to the client. This output can be intercept by the caller using the INSERT .. EXEC syntax. 3. As a return value. This can only be an integer value and is designed to be used to return success or failure information. The value is returned by using RETURN (...), and the caller can catch the value by using the EXEC @variable = ProcName (...) syntax. --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |