Stored Procedure Question - SQL Server Forums

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)

Stored Procedure Question

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 10:43:30

Ive created a stored procedure with three outputs. Every time i try to execute it, i get an error telling me that an input @PUIC is required. Here is the stored procedure:

Create Procedure GetShippingDetailsfromUIC
{
@PUIC char(5) OUTPUT,
@SUIC char(5) OUTPUT,
@UIC char(5) OUTPUT
}
As
Select @uic = uic, @PUIC = (Select uic from uics where diary in(Select diary from uics where uic = @uic) and Parent = 'P'), @SUIC = Case(Select INAC from uics where uic = @UIC)
when 'E'
then
@uic
else
' '
end
from UICS
Go

any ideas on this error
thanks
Ben Switzer

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 10:56:45

How are you calling the sproc?

Brett

8-)

SELECT POST=NewId()

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 11:36:04

through sql squery analyzer

exec getshippingdetailsfromuic

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 11:45:41

How does this work?


Create Procedure GetShippingDetailsfromUIC
@PUIC char(5) OUTPUT,
@SUIC char(5) OUTPUT,
@UIC char(5) OUTPUT
AS

SELECT 
 @PUIC = '1'
,@SUIC = '2'
,@UIC = '3'


DECLARE @PUIC char(5), @SUIC char(5), @UIC char(5)

EXEC GetShippingDetailsfromUIC 
	  @PUIC OUTPUT
	, @SUIC OUTPUT
	, @UIC OUTPUT

SELECT
 @PUIC
,@SUIC
,@UIC

Brett

8-)

SELECT POST=NewId()

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 12:01:14

Well I applied this to mine, and i dont get the error, but I am only getting the last record of the result. Not sure why.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 12:24:59

quote:
Originally posted by BenSwitzer

Well I applied this to mine, and i dont get the error, but I am only getting the last record of the result. Not sure why.

Well, how can you fit 10 Vlaues in to a single local variable..

What are you trying to get?

If you have n rows, what would you do with them?

Can you explain in business terms what you're trying to do?

Brett

8-)

SELECT POST=NewId()

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 13:28:50

Well basically I tried to do what would take through a program in one query. The uics table uses a parent and inac column to designate shipping. If a uic has a diary code of "AB" and its not a parent and inac is not 'E' then I need to do a lookup of its parent for the same diary code. im not sure if that makes sense. Their are around 9000 uics and i wanted to do this lookup for all 9000 in one query.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 13:49:12

or at least one stored procedure

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 14:05:21

Well I probably should of thought of this before, but i think i will create a couple of UDF's that i can call to do this. Im pretty sure that will work.

thanks
Ben Switzer

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 14:43:42

quote:
Originally posted by BenSwitzer
are around 9000 uics and i wanted to do this lookup for all 9000 in one query.

Riddle me this batman...and do what with them?

Brett

8-)

SELECT POST=NewId()

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2003-08-04 : 15:13:58

Basically i wanted an output as follows

UIC PARENTUIC ShippingUIC
00000 99999
11111 11111
22222 33333 22222
etc.

In order to get this output i had to create two UDFs and call them from my query. Then once i have this information i will put it into an array and search through it since that is much faster than requering the database.

Im not sure if you are following me, but the two functions worked perfectly. Considering its my first attempt to do a UDF in SQL, Im happy. :+)

Thanks
Ben Switzer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources