Sql procedure with two input parameters

Question

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 ?

Take out the "NOT NULL" part of the parameters declaration.

Tab Alleman
– Tab Alleman

2015-07-06 14:10:22 +00:00
Commented Jul 6, 2015 at 14:10 — Tab Alleman
– Tab Alleman, Commented Jul 6, 2015 at 14:10
Did the answer help?

Wibbler
– Wibbler

2015-07-09 08:13:58 +00:00
Commented Jul 9, 2015 at 8:13 — Wibbler
– Wibbler, Commented Jul 9, 2015 at 8:13

Wibbler · Accepted Answer · 2015-07-06 18:14:49Z

2

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

edited Jul 6, 2015 at 18:14

answered Jul 6, 2015 at 14:14

Wibbler

1,0439 silver badges17 bronze badges

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

2 Comments

Arsen Milosev Over a year ago

When i try to parse it gives error 'Incorrect syntax near the keyword 'SELECT'.' The select being after "=" .

Wibbler Over a year ago

I've removed the incorrect syntax from the setting of the variable, please see my edited answer. To set a variable from a table the correct syntax is SELECT @[variablename] = [field] FROM [table]

Collectives™ on Stack Overflow

Sql procedure with two input parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related