Setting variable SQL Procedure

Question

I am working on a database that contains customers, products, timesheets, etc for a store. The question I am working on involves creating a procedure that will change an "on/off" column to off (the product is available (1) by default, and this procedure turns it to 0) I have writen the procedure fine:

create proc p_fudgemart_deactivate_product 
(
    @product_id int
)
as
begin
update fudgemart_products
set product_is_active = 0
where product_id = @product_id
end

but the issue then comes when we are given a product NAME, and need to write a select statement to change that product to unavailable. I know that this requires the use of a variable, but I cannot figure out how to set the variable to the product id of that product. I was thinking something along the lines of:

Declare @prod_name_id int
    set @prod_name_id= (select product_id from fudgemart_products
    where product_name = 'Slot Screwdriver')
    execute p_fudgemart_deactivate_product product_id @prod_name_id

Am I able to use a select in my variable declaration like this?

Is product_name unique? As in, could there be more than one record with a name of "Slot Screwdriver"? — Andrew Morton
– Andrew Morton, Commented Oct 4, 2014 at 18:02

roman · Accepted Answer · 2014-10-04 17:50:36Z

1

actually you're on the right track. try something like this:

declare @prod_name_id int

select @prod_name_id = product_id
from fudgemart_products
where product_name = 'Slot Screwdriver'

exec p_fudgemart_deactivate_product
    @product_id = @prod_name_id

answered Oct 4, 2014 at 17:50

roman

118k30 gold badges205 silver badges209 bronze badges

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

2 Comments

wmiller11293 Over a year ago

Thank you very much!! Since product_name is providing the product_id to the procedure, would I need to repeat this in order to perform the procedure on multiple items? Rather then being able to add an OR? For example: 'where product_name = 'slot screwdriver' OR product_name = 'hammer''

Aaron Bertrand Over a year ago

@wmiller11293 do you really need to call the other procedure in that case? UPDATE dbo.fudgemart_products SET product_is_active = 0 WHERE product_name IN ('slot screwdriver','hammer');

Dan Guzman · Accepted Answer · 2014-10-04 19:01:03Z

0

If you are using SQL Server 2008 or later, you can declare and assign in one statement:

DECLARE @prod_name_id int = ( SELECT    product_id
                              FROM      fudgemart_products
                              WHERE     product_name = 'Slot Screwdriver'
                            );
EXECUTE p_fudgemart_deactivate_product @product_id = @prod_name_id;

answered Oct 4, 2014 at 19:01

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Collectives™ on Stack Overflow

Setting variable SQL Procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related