Help me with this Stored Procedure

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 2000 Forums

Transact-SQL (2000)

Help me with this Stored Procedure

Author

Topic

johnsmith180
Starting Member

1 Post

Posted - 2004-12-27 : 07:14:11

I have been working on this SP since morning but to no avail, lets hope u can help me.

SP is :

CREATE Procedure Auto_GetDeliveryAddress
(
@Username nvarchar(50) ,
@AddressID int OUTPUT,
@RecipientName nvarchar(50) OUTPUT,
@Address1 nvarchar(50) OUTPUT
)

AS
/* first get AddressID from another table */
DECLARE @deliveryAddressID int

Select @deliveryAddressID=AddressID
from Auto_CustomerAddress
where Username=@Username AND Prefered=1 AND AddressTypeID=1

SELECT
@AddressID=AddressID,
@RecipientName= RecipientName,
@Address1 = Address1

FROM
Auto_Address

WHERE
AddressID = @deliveryAddressID
GO

Basically, I want to get address details for a particular user, so I provide username and OUTPUT Address details.

First, I use @deliveryAddressID varibale to lookup AddressID from another table and then use this varibale in the following Select statement.

When I run this SP in Query Analyser, I get: this SP
"expects parameter '@AddressID', which was not supplied"

If I take out @AddressID from my SP, and then re-run my SP, I get following error:
this SP
"expects parameter '@RecipientName', which was not supplied"

So I am not really sure what exactly is the problem. Hope u can help.

dsdeming

479 Posts

Posted - 2004-12-27 : 08:16:23

When you call the sp, are you passing in the output parameters? That's what it's complaining about.

Dennis

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-27 : 08:34:03

When calling a stored proc with OUTPUT params, you must pass variables in to the stored proc to get them out in QA for it to work.


DECLARE @Username nvarchar(50)

'Output Param Vars
DECLARE @AddressID int 
DECLARE @RecipientName nvarchar(50)
DECLARE @Address1 nvarchar(50) 

SELECT @Username = 'TheUserName'

EXEC Auto_GetDeliveryAddress @Username, @AddressID, @RecipientName, @Address1 

'The results
SELECT @AddressID AS AddressID , @RecipientName AS RecipientName, @Address1 AS Address1

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources