returning a value from stored procedure

[OldSlowDog]

Hi all,
I am trying to develop a SP for looking up the Quantity of a specific item at a specific warehouse. My idea is to pass the Item Name and Warehouse Location to the SP and have it return the Quantity.

CREATE GetQuantity_sp
-- =============================================
-- Create date: 5/11/2008
-- Description: Passing ItemName and WarehouseLoc to SP
-- returning the Quantity info.
-- Usage: QtyOnHand = EXEC GetQuantity_sp 'item' , 'Wh'
-- =============================================
@Item varchar(50)
@Warehouse varchar(30)
AS

SET NOCOUNT ON
SELECT I.Quantity
FROM Inventory I
WHERE I.ItemName = @item
AND I.WarehouseLoc = @Warehouse

Return(??)

My problem is I could not figure out how to return the quantity.
Thanks.

 

[bborissov]

No return.
Just use the returned recordset, or use OUTPUT vparameter:

CREATE GetQuantity_sp(
@Item varchar(50),
@Warehouse varchar(30),
@quantity numeric(14,3) OUTPUT --use proper type here
) 
AS
BEGIN
   SET NOCOUNT ON
   SELECT @quantity = I.Quantity
          FROM Inventory I
   WHERE I.ItemName = @item
     AND I.WarehouseLoc = @Warehouse
END

Then you could call it:

DECLARE @Test numeric(14,3)
EXEC GetQuantity_sp 'SomeName', 'Location', @Test OUTPUT
SELECT @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

You can use the return statement to return a single value from a stored procedure, but I wouldn't necessarily recommend it. Return is limited to returning an integer, but more importantly, it is usually reserved for returning an error number.

However... if you want to use it....

CREATE GetQuantity_sp
-- =============================================
-- Create date: 5/11/2008
-- Description: Passing ItemName and WarehouseLoc to SP
--               returning the Quantity info.
-- Usage: QtyOnHand = EXEC GetQuantity_sp 'item' , 'Wh'
-- =============================================
@Item varchar(50)
@Warehouse varchar(30)
AS

SET NOCOUNT ON
[!]Return ([/!]SELECT I.Quantity
FROM Inventory I
WHERE I.ItemName = @item
AND I.WarehouseLoc = @Warehouse[!])[/!]

Instead, I would suggest you use an output parameter, like this...

[COLOR=blue]CREATE[/color] [COLOR=blue]Procedure[/color] GetQuantity_sp
[COLOR=green]-- =============================================
[/color][COLOR=green]-- Create date: 5/11/2008
[/color][COLOR=green]-- Description: Passing ItemName and WarehouseLoc to SP
[/color][COLOR=green]--               returning the Quantity info.
[/color][COLOR=green]-- Usage: QtyOnHand = EXEC GetQuantity_sp 'item' , 'Wh'
[/color][COLOR=green]-- =============================================
[/color]@Item [COLOR=blue]varchar[/color](50),
@Warehouse [COLOR=blue]varchar[/color](30),
@Quantity [COLOR=blue]int[/color] [COLOR=blue]Output[/color]
[COLOR=blue]AS[/color]

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]
[COLOR=blue]SELECT[/color] @Quantity = I.Quantity
[COLOR=blue]FROM[/color]   Inventory I
[COLOR=blue]WHERE[/color]  I.ItemName = @item
       AND I.WarehouseLoc = @Warehouse

You would call it like this....

Declare @Quantity int
Exec GetQuantity_sp 'item','Wh',@Quantity Output
Select @Quantity

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

you can also create a UDF (User Defined Function)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Sorry Boris. You hadn't posted yet when I first started responding to this post.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OldSlowDog]

Borislav,
Thank you for your help. How do I capture the quantity in my Access ADP program. For example:

Dim....
Dim intQuantity as integer
...
...
DECLARE @Test numeric(14,0)
EXEC GetQuantity_sp 'SomeName', 'Location', @Test OUTPUT
intQuantity = SELECT @Test
msgbox "There are " & intQuantity & " pieces"

Can I do that?

Thanks.

 

[bborissov]

Why sorry George?
You are not a mind reader, aren't you?
Maybe I think less before I post
(Hmm, not maybe, that is for sure ))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

OldSlowDog,

Use an ADO Command Object. When using an output parameter, you need to indicate that (adParamOutput).

Try this google search. [google]access ADP command object output parameter[/google]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom