Stored Procedure

[HRAHIMI]

I am trying to create a stored procedure but when I execute it, it looks like it's not even passing thru the "If @qunit = 'LBS'" line. Reason I know that it is b/c the final result is not valid. It is not converting which is what I am trying to do. Please help!!!!

IF EXISTS (SELECT l.expquant, l.qunit, l.minweight, l.weightunit, m.purgradename, m.salgradename, m.matchno
FROM loads as l inner join matchlines as m
ON l.matchno = m.matchno)
DROP PROCEDURE dbo.spRpt_GradesShippedincWeight
GO
CREATE PROCEDURE dbo.spRpt_GradesShippedincWeight
@expquant as varchar(10)=null, @qunit as varchar(5)=null,
@minweight as varchar(10)=null, @weightunit as varchar(5)=null,
@purgradename as varchar(40)=null, @salegradename as varchar(40)=null,
@matchno as varchar(15)=null, @addWhere varchar(4000)=null

AS

If @qunit = 'LBS'
BEGIN
SELECT QT=l.expquant/2000, l.qunit, l.minweight, l.weightunit, m.purgradename, m.salgradename, m.matchno
FROM loads as l
Inner join matchlines as m
ON l.matchno = m.matchno
END
else
If @qunit = 'MT'
BEGIN
SELECT QT=l.expquant/.0011023, l.qunit, l.minweight, l.weightunit, m.purgradename, m.salgradename, m.matchno
FROM loads as l
Inner join matchlines as m
ON l.matchno = m.matchno
END
else
BEGIN
SELECT QT=l.expquant, l.qunit, l.minweight, l.weightunit, m.purgradename, m.salgradename, m.matchno
FROM loads as l
Inner join matchlines as m
ON l.matchno = m.matchno
END


GO
Grant all on dbo.spRpt_GradesShippedincWeight to Harwinuser
GO

Exec spRpt_GradesShippedincWeight

 

[SQLBill]

What are you trying to do with this code? You create variables but you never use them! You have all these variables:

@expquant as varchar(10)=null, @qunit as varchar(5)=null,
@minweight as varchar(10)=null, @weightunit as varchar(5)=null,
@purgradename as varchar(40)=null, @salegradename as varchar(40)=null,
@matchno as varchar(15)=null, @addWhere varchar(4000)=null

but they are never used anywhere in the procedure code. Why not?

-SQLBill

 

[HRAHIMI]

Well I used a template of another stored procedure that's why. I can get rid of all the excess variables. But my main problem is that I have a set of records with different units of measure (MT, LBS, and ST) so I am trying to convert them within the stored procedure. With the procedure I've already created it doesn't convert. It just leaves them as what the original value is.

 

[SQLSister]

What value are you passing in for @qunit? What result did you intend? What result are you getting?

Questions about posting. See faq183-874

 

[HRAHIMI]

I'm trying to pass qunit and based on that it will execute different select statements to get different values for the field "expquant". So for instance: qunit = lbs and expquant = 500. It should see that it is in lbs and convert it 500/2000. And then give me the new value.
Forgive me b/c I am veryyyy new to this w/o any type of training.

Thanks for your help!!!

 

[SQLSister]

What statement are you using to execute the stored procedure?

What result did you get?
What result did you want?



Questions about posting. See faq183-874