Stored Procedure - INSERT with multiple SELECTs

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

Stored Procedure - INSERT with multiple SELECTs

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-17 : 13:57:07

Here's my stored procedure:


ALTER PROCEDURE sproc_InsertOrder 
	
	(
		@CustID tinyint,
		@Size varchar(50),
		@Crust varchar(50),
		@total smallmoney 
	)

AS

	INSERT INTO tblOrders (CustID, SizeID, CrustID, Total)
	VALUES(@CustID,
		SELECT SizeID FROM tblSizes WHERE @Size = SizeName,
		SELECT CrustID FROM tblCrusts WHERE @Crust = Crust,
		@total)	
	SELECT SCOPE_IDENTITY()

RETURN

And my tables:

tblOrders
-------------------------------------------
OrderID | CustID | SizeID | CrustID | Total
-------------------------------------------

tblSizes
--------------------------
SizeID | SizeName | Price
--------------------------

tblCrusts
------------------------
CrustID | Crust | Price
------------------------

My stored procedure is giving me an error, it says "Unable to parse query text", and I'm certain the problem is with the SELECT statements. What am I doing wrong?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-17 : 14:00:15

You need to get the values of SizeID and CrustId separately into local variables and use them in your INSERT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-17 : 14:01:23

quote:
Originally posted by dinakar

You need to get the values of SizeID and CrustId separately into local variables and use them in your INSERT.

How do I do that? Can I do it inside the same stored procedure?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-17 : 14:23:14

Yes.


ALTER PROCEDURE sproc_InsertOrder 
	(
		@CustID tinyint,
		@Size varchar(50),
		@Crust varchar(50),
		@total smallmoney 
	)
AS

Declare @SizeId int, @CrustId Int

Select @SizeId = SizeID FROM tblSizes WHERE SizeName = @Size
Select @CrustId = CrustID FROM tblCrusts WHERE Crust = @Crust


	INSERT INTO tblOrders (CustID, SizeID, CrustID, Total)
	VALUES(@CustID, @SizeId, @CrustId, @total)	
	SELECT SCOPE_IDENTITY()

RETURN

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-17 : 14:40:34

Thank you dinakar, that worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources