select 和exec的问题,急
有个存储过程 返回值的
declare @Hours real
exec @Hours=aGetLeaveHours_return @begintime,@endtime
select @Hours
有个select语句
select @begintime,@endtime
需要结果是
select @begintime,@endtime,@hours
把select的两个参数带进存储过程,显示返回的值
有没有大神可以解惑的...
[解决办法]
例子:
USE AdventureWorks2012;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
@SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
@SQLString
,@ParmDefinition
,@CustomerID = @IntVariable
,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;
create function [dbo].[f_splitSTR]
(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --分隔字符
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
GO
select *
from dbo.f_splitSTR('a,b,c',',')
/col
a
b
c*
*/