T-SQL - 存储过程 [英] T-SQL - stored procedure

问题描述

如何编写这个存储过程来检查stockqty并打印消息，如果它降到零？目前我收到错误。谢谢。



错误：

消息102，等级15，状态1，程序ValidateQty，第8行

'';''附近的语法不正确。

消息137，级别15，状态2，行3

必须声明标量变量@vStockqty。

消息137，等级15，状态2，行5

必须声明标量变量@Partid。

消息137，等级15，状态2，第8行

必须声明标量变量@vStockqty。

消息137，级别15，状态2，行9

必须声明标量变量@Partid。

----------------------------------- ---------------------------------------------

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

推荐答案

尝试一些TSQL Basic Books来创建程序....



我想，很抱歉这样说，你不是有任何基本的T-SQL编程技巧...



首先阅读以下内容



数据类型（数量和所有不是VARCHAR数据类型，这些是数字权利。

条件语句（IF，WHILE ...）

如何以及在何处使用BEGIN ...结束，发表声明......

简单的存储程序



查看链接..



SQL教程 [ ^ ]

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

Regars,
GVPrabu

检查一下：

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?

试试这个：

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

这篇关于T-SQL - 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！