返回存储过程中的选定值 [英] return selected value in stored procedure

问题描述

大家好



执行存储过程时遇到问题



当我执行以下代码时，我得到了正确的结果：

hi all

I Have Problem when execute Stored Procedure

when i execute the following code I get the right result :

ALTER PROCEDURE dbo.Selectplandate
	(
	@plandatefrom datetime,
	@plandateto datetime
	
	)
AS
SELECT Distinct CONVERT(datetime,plandate,103) as [Date]
FROM      plannedgangs
WHERE (plandate between @plandatefrom AND @plandateto )

但是当我使用这段代码时我得到了错误的结果：结果为空

but when I use this code i got wrong result : the result is null

ALTER PROCEDURE dbo.Selectplandate
	(
	@plandatefrom datetime,
	@plandateto datetime
	
	)
AS

DECLARE @plandate datetime

SET @plandate =
(
SELECT Distinct CONVERT(datetime,plandate,103) as [Date]
FROM      plannedgangs
WHERE (plandate between @plandatefrom AND @plandateto )
)
SELECT @plandate as [Date]

推荐答案

我的猜测是SQL语句返回的数量超过一排。标量变量只能包含一个值。请改用Table变量。

My guess is that the SQL statement returns more than one row. A scalar variable can contain only one value. Use a Table variable instead.

DECLARE @t1 TABLE([date] DATETIME);
INSERT INTO @t1 ([date]) SELECT DISTINCT plandate AS [Date]
  FROM plannedgangs
  WHERE (plandate BETWEEN @plandatefrom AND @plandateto);
SELECT [Date] FROM @t1;

假设 plandate plangangs 中的是 DateTime 数据类型，您不需要 CONVERT 功能。如果它不是 DateTime 数据类型，那么子句之间的将无法正常工作。

Assuming that plandate in plannedgangs is a DateTime Data Type, you do not need the CONVERT function. If it is not a DateTime Data Type, then the between clause will not work correctly.

在这个问题的解决方案中：标量变量和任何类型的变量总是存储时必须是单个值，当你使用存储过程select语句时，它返回多个值，因为你必须声明和使用表变量而不是标量变量。

In solution of this question: scalar variable and any type of variable always store must be a single value at time and when you working with store procedure select statement it's returns more then one value because of that you have to declare and use table variable instead of scalar variable.

SELECT Distinct CONVERT(datetime,plandate,103) as [Date]
into #tmp
FROM      plannedgangs
WHERE (plandate between @plandatefrom AND @plandateto )

select * from #tmp

drop table #tmp

谢谢......： - ）

Thank you......:-)

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