I am new to SQL and trying to write a stored procedure. I am having some difficulties to get default values as an output result.
我是SQL新手,正在尝试编写一个存储过程。我很难得到默认值作为输出结果。
I have 2 table:
我有两个表:
Student_Input:
Student_Input:
InputID SectionID ParameterName Sequence
------------------------------------------------
1 100 FirstName 1
2 100 MiddleName 2
3 100 LastName 3
Student_Input_details:
Student_Input_details:
ParameterName ParameterValue DefaultValue
-----------------------------------------------------
FirstName John 1
FirstName Troy 0
FirstName Mark 0
I am trying to call ParameterName
from Student_Input
and Its default value from Student_Input_Details
as an output in one table. I am trying with following query but I am getting following error:
我尝试从Student_Input和Student_Input_Details中的默认值调用ParameterName作为一个表中的输出。我正在尝试以下查询,但我得到以下错误:
Msg 201, Level 16, State 4, Procedure Getparameterdefaultvalues, Line 0
Procedure or function 'Getparameterdefaultvalues' expects parameter '@ParameterValue', which was not supplied.msg201,级别16,状态4,过程Getparameterdefaultvalues,第0行过程或函数“Getparameterdefaultvalues”,它没有提供。
I am sure I am missing something important here.
我肯定我漏掉了一些重要的东西。
My query is below. I am learning , it may be an easy question. Thx........
我下面的查询。我正在学习,这可能是一个简单的问题。Thx ........
CREATE PROCEDURE Getparameterdefaultvalues
(
@ParameterName varchar(50) ,
@ParameterValue varchar(50) OUT
)
AS
BEGIN
SELECT @ParameterValue = DefaultValue FROM ParameterInput_Values
WHERE ParameterName=@ParameterName
END
DECLARE @ParameterValue varchar(50)
EXEC Getparameterdefaultvalues @ParameterName = 1, @ParameterValue OUTPUT
PRINT 'Result is: ' + @ParameterValue
I need result like (i.e. ParameterName
should only display its default value at runtime):
我需要这样的结果(例如,参数名应该在运行时显示它的默认值):
ParameterName ParameterValue
----------------------------------
FirstName John
I have tried on other blogs but couldn't resolve this. Apologies If my question is not so cleared. Any help would be great!! Thx
我曾在其他博客上尝试过,但没能解决这个问题。如果我的问题没有得到解决,我很抱歉。任何帮助都是伟大的!!谢谢
1 个解决方案
#1
7
Pretty simple and easy: your stored procedure expects two parameters - @ParameterName
and @ParameterValue
- but your call only supplies one...
非常简单和简单:您的存储过程需要两个参数—@ParameterName和@ParameterValue—但是您的调用只提供一个参数……
You should call your stored procedure like this:
您应该这样调用存储过程:
DECLARE @ParameterValue varchar(50)
EXEC Getparameterdefaultvalues @ParameterName = 1, @ParameterValue = @ParameterValue OUTPUT
PRINT 'Result is: ' + @ParameterValue
Also: you're returning the retrieved value as an output parameter - so you're getting back a single value into a variable - you're not getting back a result set (rows/columns).
另外,您将检索到的值作为输出参数返回——因此您将返回一个值到变量中——您不会返回一个结果集(行/列)。
#1
7
Pretty simple and easy: your stored procedure expects two parameters - @ParameterName
and @ParameterValue
- but your call only supplies one...
非常简单和简单:您的存储过程需要两个参数—@ParameterName和@ParameterValue—但是您的调用只提供一个参数……
You should call your stored procedure like this:
您应该这样调用存储过程:
DECLARE @ParameterValue varchar(50)
EXEC Getparameterdefaultvalues @ParameterName = 1, @ParameterValue = @ParameterValue OUTPUT
PRINT 'Result is: ' + @ParameterValue
Also: you're returning the retrieved value as an output parameter - so you're getting back a single value into a variable - you're not getting back a result set (rows/columns).
另外,您将检索到的值作为输出参数返回——因此您将返回一个值到变量中——您不会返回一个结果集(行/列)。