SQL 执行时如何在存储过程变量中赋值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41225730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to assign value in stored procedure variable when executing it?
提问by Ein Hazard
This is my SQL code
这是我的 SQL 代码
CREATE PROC sp_procedure
(@Name VARCHAR(50),
@Stock numeric)
AS
DECLARE @Code CHAR(4)
BEGIN
UPDATE tbProcedure
SET Name = @Name, Stock = @Stock
WHERE Code = @Code
SELECT * FROM tbProcedure
END
Then I execute the code like this
然后我像这样执行代码
EXEC sp_procedure 'Name',15,2
Then I got error result saying too many argument specified.
然后我得到错误结果，说指定的参数太多。
I also tried this
我也试过这个
EXEC sp_procedure 'Name',15
It doesn't return an error, but 0 rows affected.
它不会返回错误，但会影响 0 行。
I want to assign @Codevalue when I execute the stored procedure, is it even possible to do that ?
我想@Code在执行存储过程时赋值，是否可以这样做？
EDIT:
编辑：
Sorry, it's actually a CHAR(4), not INT
对不起，它实际上是一个CHAR(4)，不是INT
Again sorry, I just copy paste all code without looking at it first, above is the actual code, I am very sorry for the confuse...
再次抱歉，我只是复制粘贴所有代码而没有先查看它，上面是实际代码，我很抱歉混淆......
回答by D. Barton
It looks like you are providing the parameters in a different order than you have declared them. Choice is declared before Name but you are providing Name first when executing the procedure. Also, don't you want your update statement to say "code = @choice"? The local variable is undefined and not needed.
看起来您提供的参数顺序与您声明的顺序不同。Choice 在 Name 之前声明，但在执行过程时首先提供 Name。另外，您不希望更新语句显示“code = @choice”吗？局部变量未定义且不需要。
回答by Abhishek J Patel
For Microsoft SQL Server
对于 Microsoft SQL Server
For your problem: (don't use sp prefix) and (@code is not a parameter so we cannot pass value, it is local variable) If you are having some problems with IF then, make @code as parameter but pass null value when needed and make that null check in procedure.
对于您的问题：（不要使用 sp 前缀）和（@code 不是参数，因此我们无法传递值，它是局部变量）如果您对 IF 有一些问题，请将 @code 作为参数但传递空值需要时并在程序中进行空检查。
See below to assign values:
请参阅下文以分配值：
USE [databasename]
GO
DECLARE @return_value int
EXEC @return_value = <procedurename>
@stringvariable = N'<stringvalue>',
@Flag = <integervalue>
SELECT 'Return Value' = @return_value (return value if any)
GO
回答by Ein Hazard
I have come to a conclusion that its impossible to combine branching stored procedure if one of the branch do not use all the parameters.
我得出的结论是，如果分支之一不使用所有参数，则不可能组合分支存储过程。
And because of that I have write 2 stored procedures, below is my full code :
因此，我编写了 2 个存储过程，以下是我的完整代码：
For insert procedure: [this is the procedure that using a variable]
对于插入过程：[这是使用变量的过程]
CREATE PROC InsProcedure
(@Name VARCHAR(50),
@Stock numeric)
AS
DECLARE @CODE INT
DECLARE @CODE2 CHAR(4)
BEGIN
// Creating custom auto number
SET @CODE = 0
SELECT @CODE = ISNULL(MAX(CAST(KodeBarang AS INT)), 0)
FROM tbProcedure
SET @CODE2 = @CODE + 1
SET @CODE2 = REPLICATE('0', 4 - LEN(@CODE2)) + @CODE2
// End Custom auto number
INSERT INTO tbProcedure VALUES(@CODE2, @Name, @Stock)
SELECT * FROM tbProcedure
END
For update, delete, and show procedure:
更新、删除和显示程序：
CREATE PROC OtherProcedure
(@choice int, // this is for branching option later
@Code CHAR(4),
@Name VARCHAR(50),
@Stock numeric)
AS
IF @choice = 1
BEGIN
UPDATE tbProcedure
SET Name = @Name, Stock = @Stock
WHERE Code = @Code
SELECT * FROM tbProcedure
END
ELSE IF @choice = 2
DELETE FROM tbProcedure
WHERE KodeBarang = @Code
ELSE
SELECT * FROM tbProcedure
and use the procedures like this :
并使用如下程序：
InsProcedure 'Laptop', 5
Result:
结果：
00001, Laptop, 5
Branch 1 [Update]
分支 1 [更新]
OtherProcedure 1, 'Laptops', 10, 00001
Result:
结果：
- Before : 00001, Laptop, 5
- After : 00001, Laptops, 10
- 之前 : 00001, 笔记本电脑, 5
- 之后：00001，笔记本电脑，10
Branch 2 [Delete]
分支2【删除】
OtherProcedure 2, '', 0, 00001 // Delete record where Code is '00001'
Branch 3 [Show]
分支3 [显示]
OtherProcedure 3, '', 0, 0 // You can also use any number besides 3
回答by Crayon
First of all, your variable @Stock doesn't return decimal because it's INT variable, you might try float or something else. It's the reason why it shows an error if you give it "15,2"
首先，您的变量 @Stock 不返回十进制，因为它是 INT 变量，您可以尝试 float 或其他东西。这就是为什么如果你给它“15,2”它会显示错误的原因
WHERE Code = @Code
And 0 rows affected was caused because you didn't set that variable properly. You have to assign a valid value to it.
0 行受影响是因为您没有正确设置该变量。您必须为其分配一个有效值。
