Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
How can I write this stored procedure that would check stockqty and print message if it goes down to zero? Currently I am getting error. Thanks.
 
Error:
Msg 102, Level 15, State 1, Procedure ValidateQty, Line 8
Incorrect syntax near ';'.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@vStockqty".
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@Partid".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@vStockqty".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Partid".
--------------------------------------------------------------------------------
 
IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name = 'ValidateQty')
    BEGIN DROP PROCEDURE ValidateQty; END;
GO
 
CREATE PROCEDURE ValidateQty 
@vStockqty CHAR(10),
@Partid CHAR(10)
AS 
BEGIN 
-- No query required; test for positive value
END;
GO
 
-- testing block for ValidateQty  -- warn if the stockqty goes below 1
IF (@vStockqty < 1 (SELECT Stockqty
                  FROM Inventory
                  WHERE PartID = @Partid) )
BEGIN    
 
EXECUTE ValidateQty 0, @vStockqty;
 PRINT 'Stock quantity is below zero  for  ' + @Partid   ;
END;
GO
Posted 9-Mar-13 20:27pm
7prince409
Comments
Sandeep Mewara at 10-Mar-13 3:26am
   
CREATE PROCEDURE ValidateQty
@vStockqty CHAR(10),
@Partid CHAR(10)
AS
BEGIN
-- No query required; test for positive value
END;
 
You have given end above. Remove it and see.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Try some TSQL Basic Books for Creating Procedure....
 
I think, Sorry to say this, you are not having any basic T-SQL Programming Skills...
 
First Read about followings
 
Data Types (Quantity and all not a VARCHAR Data type, These are Numbers right).
Conditional Statements (IF, WHILE ...)
How and Where to use BEGIN...END , Go Statements...
Simple Stored Procedures
 
Check the links..
 
SQL Tutorials[^]
 
 
IF OBJECT_ID('ValidateQty') IS NOT NULL DROP PROCEDURE dbo.ValidateQty
GO
CREATE PROCEDURE ValidateQty 
@vStockqty INT,
@Partid INT
AS 
BEGIN 
	-- No query required; test for positive value
	-- testing block for ValidateQty  -- warn if the stockqty goes below 1
	SELECT @vStockqty = Stockqty 
	FROM Inventory
	WHERE PartID = @Partid
	
	IF @vStockqty < 1
	BEGIN    
		PRINT 'Stock quantity is below zero  for  ' + @Partid   ;
	END;
END;
 
EXECUTE ValidateQty 10, 121;
 
Regars,
GVPrabu
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Check this:
CREATE PROCEDURE ValidateQty 
@vStockqty INT NOT NULL,
@Partid INT NOT NULL
AS 
BEGIN  
    IF (@vStockqty > 0)
    BEGIN
            SELECT Stockqty
            FROM Inventory
            WHERE PartID = @Partid)
        END
    ELSE
        BEGIN
            PRINT 'Stock quantity is below zero  for  ' + @Partid 
        END
END
 
Do you see the differences?
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

try this one:
IF EXISTS (
       SELECT NAME
       FROM   sys.procedures
       WHERE  NAME = 'ValidateQty'
   )
BEGIN
    DROP PROCEDURE ValidateQty;
END;
    GO
    
CREATE PROCEDURE ValidateQty
	@vStockqty CHAR(10),
	@Partid CHAR(10)
AS
	DECLARE @CurrentValue INT = (
	            SELECT Stockqty
	            FROM   Inventory
	            WHERE  PartID = @Partid
	        )
	
	-- testing block for ValidateQty  -- warn if the stockqty goes below 1
	IF (@CurrentValue -@vStockqty) < 1
	BEGIN
	    EXECUTE ValidateQty 0, @vStockqty;
	    PRINT 'Stock quantity is below zero  for  ' + @Partid ;
	END;
GO
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 520
1 Maciej Los 290
2 Richard MacCutchan 265
3 BillWoodruff 265
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,764
1 Sergey Alexandrovich Kryukov 7,437
2 DamithSL 5,639
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411028.1 | Last Updated 10 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100