Replacing a parameter - 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

General SQL Server Forums

New to SQL Server Programming

Replacing a parameter

Author

Topic

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-28 : 07:10:25

What I'm trying to do within this storeproc is the following:
Check the Job Number and Pallet ID (both varchars) in the Rackinglocs table
If these both appear in the table then replace the Pallet ID with the actual location of the pallet from the Loc_ID field (varchar) from the Rackinglocs table
If they dont appear in the above table then leave the Pallet ID alone

I then want to fill in a field on the stock table with either the actual location or failing that, the Pallet_ID


-- Pull the stock location from the RackingLocs_tbl
declare @Loc_ID varchar(15)

Select Job_No, Pallet_ID, Loc_ID from RackingLocs_tbl -- EK0739, PLT01, E30
where Job_No = @Job_No 
-- If Stock location exists in RackingLocs_tbl then use it
IF @Loc_ID = @Stock_Loc -- ie if both equal PLT01 then 
-- ****************The procedure works up till this point*********************
BEGIN
	select @Loc_ID = Loc_ID from RackingLocs_tbl --replace contents of parameter with physical location E30
END
ELSE -- Otherwise use Pallet_ID as Stock_tbl.Stock_Loc
	select @Loc_ID = @Stock_Loc

Currently it's just storing the original pallet ID (e.g. PLT01) into the stock table even though I know that the rackinglocs table has the physical location present (E30)

Thanks

--
Paul

Complete SQL newbie!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-28 : 07:32:48

Where are you setting @Loc_ID?

Did you mean to set it in the SELECT?


Select Job_No, Pallet_ID, @Loc_ID = Loc_ID from RackingLocs_tbl -- EK0739, PLT01, E30
where Job_No = @Job_No

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-28 : 08:03:31

No, it already exists in the RackingLocs_tbl as Job_No:EK0739, Pallet_ID:PLT01, Loc_ID:E30

I should clarify that the RackingLocs_tbl is only temporary as a leftover from the previous db and will be removed after each line in the stock table has it's location updated

Oh sorry, I see what you mean but it should only be set the same as Loc_ID if the job_no and Pallet ID exist in that table
--
Paul

Complete SQL newbie!

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-28 : 08:37:23

Ah! I got it! Your hint helped :)


-- Pull the stock location from the RackingLocs_tbl
declare @Loc_ID varchar(15)
declare @Pallet_ID varchar(15)

Select @Loc_ID = Loc_ID ,@Pallet_ID = Pallet_ID from RackingLocs_tbl
where Job_No = @Job_No 

IF @Stock_Loc = @Pallet_ID

BEGIN
	select @Loc_ID = Loc_ID from RackingLocs_tbl
END

ELSE
	select @Loc_ID = @Stock_Loc

--
Paul

Complete SQL newbie!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-28 : 09:05:03

Glad you got it sorted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources