了解简单存储过程：将输出移动到变量

I'm hoping I can get some help with understanding a simple stored procedure. Basically, I want to store the output of a select statement in a variable and then print out the value of the variable.

我希望我能帮助理解一个简单的存储过程。基本上，我想将select语句的输出存储在变量中，然后打印出变量的值。

I found a very similar question here: Simple Stored Procedure Question

我在这里找到了一个非常相似的问题：简单的存储过程问题

And the code looks as follows:

代码如下：

CREATE PROCEDURE ReturnPrice 
   @carID int,
   @price decimal(18,2) output 
AS 
   SELECT 
      @price = Price 
   FROM 
      dbo.Cars 
   WHERE 
      CarID = @carID

My question is this: How do I get the value of @carID?

我的问题是：我如何获得@carID的价值？

If I try this:

如果我试试这个：

declare @carOutput varchar(50)
exec carInformation, '@carOutput varchar(50) output',  @carOutput output

I get an error of:

我得到一个错误：

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.

Thank you!

谢谢！

1 个解决方案

#1

exec carInformation, '@carOutput varchar(50) output',  @carOutput output
                   ^bad

exec carInformation  '@carOutput varchar(50) output',  @carOutput output
                   ^good

but also '@carOutput varchar(50) output' must be an int, but you are passing in a string

而且'@ carOutput varchar（50）output'必须是一个int，但是你传入一个字符串

you need to have the calling application already know the value for @carID, and the procedure uses that value to return the price.

您需要让调用应用程序已知道@carID的值，并且该过程使用该值来返回价格。

so try something like:

所以尝试类似的东西：

DECLARE @CarID_x int
       ,@Price_x decimal(18,2)

SET @CarID_x=123

EXEC ReturnPrice @CarID, @Price_x OUTPUT 
PRINT @Price_x

要么

DECLARE @Price_x decimal(18,2)

EXEC ReturnPrice 123, @Price_x OUTPUT 
PRINT @Price_x

both code examples above will return the Price column value for the dbo.Cars row that has the CarID value of 123.

上面的两个代码示例都将返回CarID值为123的dbo.Cars行的Price列值。

If you don't know the CarID then run:

如果您不知道CarID，请运行：

SELECT 
      CarID, Price 
   FROM dbo.Cars

and you will get a result set containing all the prices for all the cars.

并且您将获得包含所有汽车的所有价格的结果集。

#1

exec carInformation, '@carOutput varchar(50) output',  @carOutput output
                   ^bad

exec carInformation  '@carOutput varchar(50) output',  @carOutput output
                   ^good