Two Select and two Insert in One SQL Server Stored Procedure

I have a procedure which I will write below. Now I want to add two selects.

First, SELECT ID FROM Category Where CategoryName = '' save it in variable @CategoryID.

And second select statement: SELECT ID FROM Type WHERE TypeName = '' and save this in @TypeID.

Now I want to add a second insert:

INSERT INTO Inventory(ProductID, InputQuantity, Margin, InputDateTime, ExpDate) 
VALUES(@ProductID, @Quantity,@开发者_JS百科Margin, @InputDateTime, @ExpDate) 
where ProductID = Scope_Identity()

Thanks :)

This is my procedure :

USE [AcidDB]
GO
/****** Object:  StoredProcedure [dbo].[InsertProducts]    Script Date: 03/24/2011 15:29:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[InsertProducts]

@CategoryID int,
@TypeID int,
@BarCode NvarChar(MAX),
@ArtNumber NvarChar(MAX),
@ProductName NvarChar(MAX),
@Price Decimal(18, 2),
@SelfPrice Decimal(18, 2),
@PriceWithOutAWD Decimal(18, 2),
@UnitsInStock int,
@Comment NvarChar(MAX)

AS 

INSERT INTO Products(CategoryID, TypeID, BarCode, ArtNumber, ProductName, Price, SelfPrice, PriceWithOutAWD, UnitsInStock, Comment) 
VALUES(@CategoryID, @TypeID, @BarCode, @ArtNumber, @ProductName, @Price, @SelfPrice, @PriceWithOutAWD, @UnitsInStock, @Comment) 

Exec InsertProducts '','','','','','','','','',''
Select SCOPE_IDENTITY()

---

You can't have a where on an insert:

INSERT INTO Inventory(ProductID, InputQuantity, Margin, InputDateTime, ExpDate) 
VALUES(@ProductID, @Quantity,@Margin, @InputDateTime, @ExpDate) 
where ProductID = Scope_Identity()

After you insert the data into the Product table, put the new ProductId into a variable:

@ProductId = Scope_Identity()

Then, use this variable in the insert:

INSERT INTO Inventory(ProductID, InputQuantity, Margin, InputDateTime, ExpDate) 
VALUES(@ProductID, @Quantity,@Margin, @InputDateTime, @ExpDate) 

---

Assuming that your revised procedure will be passed a categoryname and typename, then I think the following is what you want. There's no need to do separate selects from the Category and Type tables - just make it part of the first insert query.

ALTER PROC [dbo].[InsertProducts]

@CategoryName nvarchar(max),
@TypeName nvarchar(max),
@BarCode NvarChar(MAX),
@ArtNumber NvarChar(MAX),
@ProductName NvarChar(MAX),
@Price Decimal(18, 2),
@SelfPrice Decimal(18, 2),
@PriceWithOutAWD Decimal(18, 2),
@UnitsInStock int,
@Comment NvarChar(MAX),
@Quantity int,
@Margin decimal(14,2),
@InputDateTime datetime,
@ExpDate datetime
AS 
declare @ProductID int

INSERT INTO Products(CategoryID, TypeID, BarCode, ArtNumber, ProductName, Price, SelfPrice, PriceWithOutAWD, UnitsInStock, Comment) 
select c.CategoryID, t.TypeID, @BarCode, @ArtNumber, @ProductName, @Price, @SelfPrice, @PriceWithOutAWD, @UnitsInStock, @Comment
from Category c cross join Type t
where c.CategoryName = @CategoryName and
t.TypeName = @TypeName

set @ProductID = SCOPE_IDENTITY()

INSERT INTO Inventory(ProductID, InputQuantity, Margin, InputDateTime, ExpDate) 
VALUES(@ProductID, @Quantity,@Margin, @InputDateTime, @ExpDate)

By the way - do you really want someone putting the entire script for Romeo and Juliet in as a product name? nvarchar(max) has its place, but it shouldn't just be blindly used to avoid thinking about what you sensibly want to allow in your database.

---

@Damien_The_Unbeliever has the right idea, but I suggest to you that you use try catch blocks and transactions as well. When you have mulitple transactions that need to work together as a group they should be included in one explicit transaction and rolled back if either fails. Otherwise you will have data integrity problems. You would not want this to go into products and then fail in inventory. See Books Online for how to do this.