set select variable in a 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

General SQL Server Forums

New to SQL Server Programming

set select variable in a stored procedure

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2012-01-13 : 11:19:50

hi!

i need to create a delete procedure that will delete a record from multiple tables.

my main table has shipment ID, which i need to use to delete from the shipment table.

please see my code below:

ALTER PROCEDURE[dbo].[sp_DeleteSource]
			@id int
AS
BEGIN
	SET NOCOUNT ON;

somevariable = select shipmentID from tblMain where myID = @id

delete tblMain where myID = @id

delete tblShipment where shipmentID = somevariable

how do i properly assign "somevariable" the value from my first select statement ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-13 : 11:27:25

your database design shouldn't let you do what you are proposing.

Why isn't there a foreign key constraint between tblShipment and tblMain on ShipmentID to ShipmentID

What you'd do (set based) is something like this


ALTER PROCEDURE [dbo].[sp_DeleteSource]
	@ID INT
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRANSACTION

	DELETE ts
	FROM
		dbo.tblShipment AS ts
		JOIN dbo.tblMain AS tm ON tm.[ShipmentID] = ts.[ShipmentID]
	WHERE
		tm.[myID] = @ID;
		
	DELETE tm
	FROM dbo.tblMain AS tm 
	WHERE tm.[myID] = @ID

	COMMIT TRANSACTION
END

OR set up a cascade delete that would do it automatically.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

xrum
Yak Posting Veteran

87 Posts

Posted - 2012-01-13 : 11:28:05

i got it: select @shipmentID = shipmentID from tblSource where Sourceid = @sourceID

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-13 : 11:30:41

quote:

i got it: select @shipmentID = shipmentID from tblSource where Sourceid = @sourceID

NOT THE BEST WAY. see my post

If there is more than one value return then you will end up only deleting one of them. You won't get an error message and you'll probably spend forever trying to find out what's happened.

Also -- there should be a constraint so you *cant* delete from tblMain without removing child entries first.

If there isn't a constraint then you should probably make one.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources