CODEWITHSUNDEEP
sqlsql-server
Arsen Milosev
Arsen Milosev

Reputation: 29

Sql procedure with two input parameters

I need to make a SQL stored procedure that will take two input parameters ( id from table ‘users’ and id from table ‘sales’ ) and then if value of column ‘coupons’ (table ‘users’) is greater then 0, it increases value for 1 in column ‘numOfSales’(table ‘sales’) and decreases value for 1 in column ‘coupons’. I tried this :

CREATE PROCEDURE usp_makesale
@id_sales int NOT NULL,
@id_users int NOT NULL
AS
 BEGIN
 SET NOCOUNT ON;
 SELECT users.coupons, sales.numOfSales
IF (coupons > 0)
  BEGIN
  SET coupons - 1;
  SET numOfSales + 1;
  END

How to declare those variables properly ?

Upvotes: 1

Views: 1383

Answers (1)

Wibbler
Wibbler

Reputation: 1045

You should declare the variables like so:

DECLARE @coupons AS INT
SELECT @coupons = coupons FROM users WHERE users.id = @id_users

DECLARE @numOfSales AS INT
SELECT @numOfSales = numOfSales FROM sales WHERE sales.id = @id_sales

However you also haven't correctly written an update statement to update the values in your columns. You require something like:

UPDATE users
SET coupons = coupons - 1
WHERE users.id = @id_users

UPDATE sales
SET numOfSales = numOfSales + 1
WHERE sales .id = @id_sales

Upvotes: 2

Related Questions