sql server - Setting variable SQL Procedure -

sql server - Setting variable SQL Procedure -

i working on database contains customers, products, timesheets, etc store. question working on involves creating procedure alter "on/off" column off (the product available (1) default, , procedure turns 0) have writen procedure fine:

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

but issue comes when given product name, , need write select statement alter product unavailable. know requires utilize of variable, cannot figure out how set variable product id of product. thinking along lines of:

declare @prod_name_id int set @prod_name_id= (select product_id fudgemart_products product_name = 'slot screwdriver') execute p_fudgemart_deactivate_product product_id @prod_name_id

am able utilize select in variable declaration this?

actually you're on right track. seek this:

declare @prod_name_id int select @prod_name_id = product_id fudgemart_products product_name = 'slot screwdriver' exec p_fudgemart_deactivate_product @product_id = @prod_name_id

sql-server tsql variables stored-procedures