2 SQL queries in one stored procedure

Question

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" ?

Joel Coehoorn · Accepted Answer · 2015-01-04 23:50:51Z

1

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.

edited Jan 4, 2015 at 23:50

answered Jan 4, 2015 at 23:36

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cereal Over a year ago

I don't understand your code. What do you mean by having the Query run inside a specific value ? Also, where is "values" ?

Joel Coehoorn Over a year ago

You don't use the values clause if you provide a select query to an insert statement directly. I didn't say anything about "having the Query run inside a specific value", so I'm not sure what you're asking there.

G B · Accepted Answer · 2015-01-04 23:27:36Z

0

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)

answered Jan 4, 2015 at 23:27

G B

1,47210 silver badges13 bronze badges

Comments

mindbdev · Accepted Answer · 2015-01-05 09:26:01Z

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

Collectives™ on Stack Overflow

2 SQL queries in one stored procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related