CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Cereal
Cereal

Reputation: 39

2 SQL queries in one stored procedure

Can I run 2 queries in one stored procedure ?

CREATE PROCEDURE AddProd
   @Store_Name varchar(50),
   @Price int,
   @Prod_Name varchar(50),
   @Qty int,
   @ProductDescription varchar(50),
   @RatingSum int,
   @RatingCount int,
   @ProductImage varchar(50),
   @Prod_Date date,
AS
BEGIN
    SELECT S.Store_ID
    FROM Store S
    WHERE StoreName=@StoreName

    INSERT INTO Product (Store_ID, Price, Prod_Name, Qty, ProductDescription, RatingSum, RatingCount, ProductImage, Prod_Date)
    VALUES (S.Store_ID, @Price, @Prod_Name, @Qty, @ProductDescrpition, @RatingSum, @RatingCount, @ProductImage, @Prod_Date)
END
GO

For this code above, I want retrieve a STORE_ID by giving the STORE_NAME the user give as a parameter.

I want to use this STORE_ID in the INSERT statement.

Can I do this ?!

AKA, is the S.store_ID returned from the first query, the same as that one I used in "Values" ?

Upvotes: 0

Views: 1086

Answers (3)

mindbdev
mindbdev

Reputation: 444

If StoreID is unique for each store name you can store it in a variable and use it in your insertion 

CREATE PROCEDURE AddProd

    @Store_Name varchar(50),
    @Price int,
    @Prod_Name varchar(50),
    @Qty int,
    @ProductDescription varchar(50),
    @RatingSum int,
    @RatingCount int,
    @ProductImage varchar(50),
    @Prod_Date date,

    AS
    BEGIN
        DECLARE @StoreID [DataType]
        SELECT @StoreID = S.Store_ID
        FROM Store S
        WHERE StoreName=@StoreName

        INSERT INTO Product (Store_ID,Price,Prod_Name,Qty,ProductDescription,RatingSum,RatingCount,ProductImage,Prod_Date)
        values (@StoreID,@Price,@Prod_Name,@Qty,@ProductDescrpition,@RatingSum,@RatingCount,@ProductImage,@Prod_Date)
    END
    GO

In any scenario you can use the following 

 CREATE PROCEDURE AddProd

   @Store_Name varchar(50),
   @Price int,
   @Prod_Name varchar(50),
   @Qty int,
   @ProductDescription varchar(50),
   @RatingSum int,
   @RatingCount int,
   @ProductImage varchar(50),
   @Prod_Date date,

   AS
   BEGIN



    INSERT INTO Product (Store_ID,
    Price,
    Prod_Name,
    Qty,
    ProductDescription,
    RatingSum,
    RatingCount,
    ProductImage,
    Prod_Date
    )
    SELECT       
    S.Store_ID    
    @StoreID,
    @Price,
    @Prod_Name,
    @Qty,
    @ProductDescrpition,
    @RatingSum,
    @RatingCount,
    @ProductImage,
    @Prod_Date
     FROM Store S
                WHERE StoreName=@StoreName
            END
            GO

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Technically, you can do this in a single query:

INSERT INTO Product
        (Store_ID,  Price, Prod_Name, Qty, ProductDescription, RatingSum, RatingCount, ProductImage, Prod_Date)
SELECT S.Store_ID, @Price,@Prod_Name,@Qty,@ProductDescription,@RatingSum,@RatingCount,@ProductImage,@Prod_Date 
FROM Store S 
WHERE StoreName=@StoreName

I don't have test data handy to check, but you may have to give the appropriate names to each of the columns in the select clause from that query, instead of just the variable names. The only other reason this might not work is if you also wanted to return the selected storeID from the stored procedure, but even in that case you can just add an OUTPUT clause:

INSERT INTO Product 
          (Store_ID,  Price, Prod_Name, Qty, ProductDescription, RatingSum, RatingCount, ProductImage, Prod_Date)
OUTPUT S.Store_ID
  SELECT S.Store_ID, @Price,@Prod_Name,@Qty,@ProductDescription,@RatingSum,@RatingCount,@ProductImage,@Prod_Date
  FROM Store S 
  WHERE StoreName=@StoreName

But for the title question, the answer is affirmative; you can execute multiple statements inside a single stored procedure.

Upvotes: 1

G B
G B

Reputation: 1462

unless you want to return the storeID from the sp remove that query and put it into the insert

INSERT INTO Product (Store_ID,Price,Prod_Name,Qty,ProductDescription,RatingSum,RatingCount,ProductImage,Prod_Date)
values (
    (SELECT S.Store_ID FROM Store S WHERE StoreName=@StoreName),
    @Price,@Prod_Name,@Qty,@ProductDescrpition,@RatingSum,@RatingCount,@ProductImage,@Prod_Date)

Upvotes: 0

Related Questions