重现两个以上值的存储过程 [英] stored procedure returing more than 2 values

问题描述

大家好，

我有一个查询:

Hi All,

I have a query as :

select @name=name,@address=address from Attendance_Master where OffEmail=@Email and LoginTime between @fromdate and @todate

我想在存储过程中使用此查询.

在过程中，我想传递三个值
1)@Email
2)@fromdate
3)@todate
并想返回两个值
1)@name
2)@address

请让我知道该怎么做以及在执行存储过程期间如何存储返回值.

谢谢你
莫哈末Wasif

and I want to use this query in stored procedure.

In Procedure I want to pass three values
1) @Email
2) @fromdate
3) @todate
and want to return two values
1) @name
2) @address

Please let me know how to do it and also how to store returning values during execution of stored procedure.

thanking You
Mohd. Wasif

推荐答案

您可以使用OUTPUT创建一个proc

You can create a proc using OUTPUT

CREATE PROCEDURE myProc
@Email    VARCHAR(100),
@fromdate DATETIME,
@todate   DATETIME,
@name     VARCHAR(100) OUTPUT,
@address  VARCHAR(100) OUTPUT
AS
select  TOP 1
        @name    = name,
        @address = address
from    Attendance_Master
where   OffEmail=@Email
        and
        LoginTime between @fromdate and @todate

这是使用proc
的方法

This is how you can use the proc

DECLARE @name     VARCHAR(100)
DECLARE @address  VARCHAR(100)

EXEC myProc @Email='[e-mail]',@fromdate='2011-01-01',@todate='2011-01-31', @name = @name OUTPUT, @address = @address OUTPUT

PRINT @name
PRINT @address

非常简单！

使用:
结束存储过程

Very simple!

End your stored procedure with :

SELECT @Name as name, @address as address

您可以使用输出变量

You could use output variables

CREATE PROCEDURE  GetDetails

	(
		@Input1			SMALLINT,
		@Input2			SMALLINT,
		@Output1		INT OUTPUT
		@Output2		VARCHAR(50) OUTPUT
	)

AS

SET NOCOUNT ON

SELECT
	@Output1 = Field1, 
	@Output2 = Field2
FROM
	MyTable
WHERE
	SomeField = @Input1
AND
	SomeOtherField = @Input2 

然后只要从使用该过程的任何代码中获取值即可

http://dotnetgems.blogspot.com/2007/10/using-output- parameters-in-stored.html [ ^ ]

或者，您可以只返回一个ResultSet并读取值，这两个值都是有效的.

Then just grab the values from whatever code is consuming the procedure

http://dotnetgems.blogspot.com/2007/10/using-output-parameters-in-stored.html[^]

Or you could just return a ResultSet and read the values, both are valid.

这篇关于重现两个以上值的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！