If .. Else.. problems in SP - 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

SQL Server Development (2000)

If .. Else.. problems in SP

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-09-15 : 10:12:48

I have this SP, I have trimmed the SP for bravity:

Basically, when I say: @TotalWeight = @tempActualWeight I am getting error message: "Incorrect syntax near '@TotalWeight'"
Any idea what I am missing??

the SP:

CREATE Procedure spv_Products_GetBasketWeightTest
(
@ActualWeight bit,
@TotalWeight int OUTPUT
)
AS

declare @tempActualWeight int
declare @tempVolumetricWeight int

if @ActualWeight = 1
begin

SELECT
@TotalWeight =ActualWeight FROM Basket_ShoppingCart

end

else
begin

-- first get actual weight

SELECT
@tempActualWeight = ActualWeight FROM Basket_ShoppingCart

-- first get volumetic weight --

SELECT
@tempVolumetricWeight = VolumetricWeight FROM Basket_ShoppingCart

-- Now determine which is highest --

if (@tempActualWeight > @tempVolumetricWeight)
begin
@TotalWeight = @tempActualWeight -- this does not work
-- print ' actual is more than volu...' -- BUT this works
end

-- else volumetric ---
else
begin
-- use actual rate --
@TotalWeight = @tempVolumetricWeight -- this does not work
-- print ' vol is more than actual...' -- BUT this works

end
end
GO

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 10:14:14

Change

@TotalWeight = @tempActualWeight -- this does not work

to

SELECT @TotalWeight = @tempActualWeight -- this should

work

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 10:17:09

or

SET @TotalWeight = @tempActualWeight

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-09-15 : 10:25:18

excellent.
Its difficult to switch my mind between web programming and sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 10:26:17

>>Its difficult to switch my mind between web programming and sql

anyway you should

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 10:44:36

Actually looking at this a bit more:

SELECT @tempVolumetricWeight = VolumetricWeight FROM Basket_ShoppingCart

will select a random value from your Basket_ShoppingCart table, which may not be what you intended

You can also combine the two selects:

SELECT
@tempActualWeight = ActualWeight FROM Basket_ShoppingCart
SELECT
@tempVolumetricWeight = VolumetricWeight FROM Basket_ShoppingCart

as


SELECT 
    @tempActualWeight = ActualWeight,
    @tempVolumetricWeight = VolumetricWeight FROM Basket_ShoppingCart

although my previous point about this selecting a value at random still applies (actually its worse than that because the WHOLE of the column from the Basket_ShoppingCart will be read into that variable, one row after another, and the last one read in will be the one retained). So as that table grows the process will slow down!

You need to stick a WHERE clause on it so that a specific record is choosen I expect - e.g. the Basket and Item Numbers

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources