Output Parameter to Recordset from stored Proc 1

[gus121]

Hi I have a large T-SQL proc which normaly retrieves a recordset from the DB which i then display the results within my Application. I wish to output @TotalWeight (even if i get no results within the main recordset).
How do i do this?

CREATE    PROCEDURE [dbo].[usp_ShippingOptionsGet]
	@CustomerID int,
AS

DECLARE @ShippingZoneID int,
	@ShippingCode Char(2)
	@TotalWeight int 

SELECT @ShippingZoneID = Countries.ShippingZoneID 
FROM Customer JOIN Address ON Customer.BillingAddressID = Address.AddressID 
JOIN Countries ON Address.CountryCode = Countries.Country_Code WHERE Customer.CustomerID = @CustomerID

SELECT @TotalWeight = Sum([O].Weight*[B][I].Quantity) FROM [BasketItem] AS [B][I]
JOIN tblOptions AS [O] ON [B][I].OptionID = [O].Option_ID 
WHERE ([B][I].CustomerID = @CustomerID)

SELECT @ShippingCode = ShippingCode FROM Shipping 
WHERE ShippingZoneID = @ShippingZoneID 
AND WeightRangeLow <= @TotalWeight AND WeightRangeHigh > @TotalWeight

SELECT 
	* 
	
FROM 
	Shipping AS [ShipNormal]

JOIN 	
	ShippingZone AS [SZ]

ON 
	[ShipNormal].ShippingZoneID = [SZ].ShippingZoneID 

WHERE
	[SZ].ShippingZoneID = @ShippingZoneID 
AND
	[ShipNormal].WeightRangeLow <= @TotalWeight
AND
	[ShipNormal].WeightRangeHigh > @TotalWeight
UNION

SELECT 
	* 
	
FROM 
	Shipping AS [ShipUnusal]

JOIN 	
	ShippingZone AS [SZ]

ON 
	[ShipUnusal].ShippingZoneID = [SZ].ShippingZoneID 

WHERE
	[SZ].ShippingZoneID = @ShippingZoneID
AND
	[ShipUnusal].WeightRangeLow is Null
AND
	[ShipUnusal].WeightRangeHigh is Null
AND
	[ShipUnusal].ShippingCode > @ShippingCode

ORDER BY 
	ShippingCode

-Gus

 

[JamesLean]

I would use an OUTPUT parameter for this rather than another recordset.

CREATE PROC [dbo].[usp_ShippingOptionsGet]
    @CustomerID int,
    [red]@TotalWeight int OUTPUT[/red]
AS
...

--James

 

[Catadmin]

And then, if you want to add a value if the output parameter is Null, you can do one of two things. Either an IF @TotalWeight IS NULL
Begin
Set @TotalWeight = 0
End

or you can use the ISNULL() function, which is a little less typing.

ISNULL(@TotalWeight, 0)

At the end of your stored procedure, once you have your math done, you can use "Select @TotalWeight" and the SP will return that value.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[gus121]

Hi thanks alot for your time. Before Looking at handling Nulls i need to get the call from my ASP page to work and retrieve the output parameter as well as the Recordset info. here is the code i used to try and retrieve this info.

Here is the error

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Formal parameter '@CustomerID' was defined as OUTPUT but the actual parameter not declared OUTPUT.

Here is the ASP i used.

With cmd  
		'retrieves shipping options
																  
			.CommandText = "usp_ShippingOptionsGet"                                
			.CommandType = adCmdStoredProc                                  
			.ActiveConnection = gConn
			.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
			.Parameters.Append .CreateParameter("[COLOR=red]@TotalWeight[/color]", adInteger, adParamOutput, 0)
			.Parameters.Append .CreateParameter("@CustomerID", adInteger, adParamInput, 0)      
																	 
			.Parameters("@CustomerID")		= gCustomerID             
	
		End With                                                            
								   
		With rsShipOptions                                                          
			.CursorLocation = adUseClient                                   
			.Open cmd, ,adOpenStatic, adCmdStoredProc
			Dim TotalWeight
			 TotalWeight = .Parameters("@TotalWeight")                                  
			Set .ActiveConnection = Nothing                                 
		End With                                                            
		Set cmd = Nothing
		%>
		<input name="TotalWeight" type="hidden" value="<%=TotalWeight%>">
		<%
	If TotalWeight > 0 then
		If rsShipOptions.EOF Then 'if shipping weight is out of range

thanks

-Gus

 

[Catadmin]

Check your Stored Procedure. It sounds like you put Output after both CustomerID and TotalWeight or just after CustomerID and then tried to call TotalWeight.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

Also: what is .rsShipOptions? Recordset or Command? You are using nested With so...



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gus121]

.rsShipOptions is recordset
Here are the first few lines of my stored Proc..

ALTER     PROCEDURE [dbo].[usp_ShippingOptionsGet]
	@CustomerID int,
	@TotalWeight int OUTPUT
AS

DECLARE @ShippingZoneID int,
	@ShippingCode Char(2)
	--@TotalWeight int 

SELECT @ShippingZoneID = Countries.ShippingZoneID 
FROM Customer JOIN Address ON Customer.BillingAddressID = Address.AddressID 
JOIN Countries ON Address.CountryCode = Countries.Country_Code WHERE Customer.CustomerID = @CustomerID

SELECT @TotalWeight = Sum([O].Weight*[B][I].Quantity) FROM [BasketItem] AS [B][I]
JOIN tblOptions AS [O] ON [B][I].OptionID = [O].Option_ID 
WHERE ([B][I].CustomerID = @CustomerID)
....

-Gus

 

[vongrunt]

That's one of typical ADO bombs... unless you use cmd.NamedParameters=true, order of parameters is based on order of appending (.Parameters.Append). Either use named params or place declaration of @CustomerID before @TotalWeight in ASP code. IMO first method is waaay better.

After that you'll probably hit another problem I mentioned (nested With).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gus121]

Hi thanks for your help I think i hit your secound issue

Object doesn't support this property or method: 'Parameters'
/inc/i_cb_shipping.asp, line 25

on this line. TotalWeight = .Parameters("@TotalWeight")

I dont understand what you mean by "nested withs" as they are not nested.

Any suggestions how i should code this?

thanks

-Gus

 

[vongrunt]

This is kinda VB/ASP question but anyway...

With cmd
	'first With, here .blah means cmd.blah
	...
	With rsShipOptions
		' nested With, here .blah means rsShipOptions.blah

	End With
End With

This is natural scope thing... simply use TotalWeight = cmd.Parameters("@TotalWeight")... or move this line out of inner With if possible.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Oops sorry... you are right, withs are not nested (I probably got confused with first line indent).

cmd.Parameters hint is still valid though.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gus121]

Hi vongrunt thanks for your help you have fixed my problem and i have learnet a few things to!

-Gus