Running a stored procedure using Output result.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running a stored procedure using Output result.

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-12 : 01:49:49

Hey guys!

I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.

I've created a hierarchal listbox form that drills down From

Product - Colour - Year.

based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.

So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.

I want that product number to be used to populate the grid view. Is there away for me to do this?

Thanks in advanced everybody!

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-12 : 02:35:55

do you need help coding the nested dropdowns or the stored procedure specifically?

Perhaps youre looking for a way to limit the resultset with optional parameters?


declare @Product table (ProductID int identity(1,1), ProductName varchar(10), ProductColour varchar(10), ProductYear int)

insert into @Product (ProductName, ProductColour, ProductYear)
	select 'hat', 'red', 1993 union
	select 'hat', 'blue', 1993 union
	select 'hat', 'black', 1994
	
declare @ProductName	varchar(10),
	@ProductColour	varchar(10),
	@ProductYear	int
		
-- your inputs
set @ProductName = 'hat'
set @ProductColour = null
set @ProductYear = 1993	
	
select	ProductID
from	@Product
where	ProductName = isnull(@ProductName, ProductName) and
	ProductColour = isnull(@ProductColour, ProductColour) and
	ProductYear = isnull(@ProductYear, ProductYear)

Nathan Skerl

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-12 : 02:51:53

No i've got the listboxes done just fine, after they've selected the last part I;m trying to figure out a way for the Grid to grab the selected values and plug them into the

declare @ProductName varchar(10),
@ProductColour varchar(10),
@ProductYear int

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-12 : 11:21:05

here's what i've done

CREATE PROCEDURE GW_ProductID
(
 @modemID int,
 @colourID int,
 @SpeedID int,
 @ProductID  INT  OUTPUT
 )
 AS
SELECT @ProductID=PROD_ID FROM Products WHERE modemID=@ModemID AND colourID=@colourID
AND SpeedID=@SpeedID


GO

Then I get a product ID out of that.

CREATE PROCEDURE dbo.getInventory

@PRODUCTID VARCHAR(7)

AS
BEGIN

SELECT [Part#] AS [Part #], [Colour], [Details], [Availability]
FROM [Inventory]
WHERE ([Part#] LIKE '%' + @PRODUCTID + '%')
ORDER BY [Availibility] DESC

END
GO

i guess i need to know how to tell that table to grab the PRODUCTID from the output of the first stored proc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 11:29:11

You can try like this:-

CREATE PROCEDURE dbo.getInventory
AS
BEGIN
DECLARE @PRODUCTID int

EXEC GW_ProductID modemidvalue,colouridvalue,speedidvalue,@PRODUCTID OUTPUT

SELECT [Part#] AS [Part #], [Colour], [Details], [Availability] 
FROM [Inventory] 
WHERE ([Part#] LIKE '%' + CAST(@PRODUCTID S varchar(7)) + '%')
ORDER BY [Availibility] DESC

END
GO

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-12 : 12:43:20

Do you need it in two procedures? Why not pass in your inputs and get the desired resultset in one step:


create procedure dbo.getInventory
	(       @modemID	int,
	        @colourID	int,
		@SpeedID	int
	)
		
as
set nocount on

declare @ProductID int

-- resolve ProductID from inputs
select	@ProductID = ProductID
from	Products 
WHERE	modemID=@ModemID AND 
	colourID=@colourID AND 
	SpeedID=@SpeedID

-- return inventory resultset
select	[Part#] AS [Part #], 
	[Colour], 
	[Details], 
	[Availability] 
from	[Inventory]
where	([Part#] LIKE '%' + @PRODUCTID + '%')
order 
by	[Availibility] desc

Nathan Skerl

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-12 : 13:01:12

you geniuses never cease to amaze me!! both worked, will be using Nathans as it does seem to save a step. Thank you thank you thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources