从过程返回val

ALTER PROCEDURE testme @input  VARCHAR(10),
                       @output VARCHAR(20) output
AS
  BEGIN
      IF @input >= '1'
        BEGIN
            SET @output = 'i am back';

            RETURN;
        END
  END

DECLARE @get VARCHAR(20);

EXEC testme
  '1',
  @get output

SELECT @get 

下面是存储过程

create procedure sp1
(
@id as int,
@name as nvarchar(20) out
)
as
begin
select @name=name from employee where id=@id
end

这是执行这个过程的方法

 declare @name1 nvarchar(10)
    exec sp1 1,@name1 out
    print @name1

这个怎么样?它非常简单:

下面的SPROC有一个输出参数@ParentProductID 我们希望将@ParentProductID的输出值选择为下面声明的@MyParentProductID。 代码如下: 声明@MyParentProductID int exec p_CheckSplitProduct @ProductId = 4077， @ParentProductID = @MyParentProductID输出 选择@MyParentProductID

来自https://learn.microsoft.com/en-US/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql(原http://support.microsoft.com/kb/262499)

CREATE PROCEDURE Myproc @parm varchar(10), **@parm1OUT varchar(30) OUTPUT**, **@parm2OUT varchar(30) OUTPUT** AS SELECT @parm1OUT='parm 1' + @parm SELECT @parm2OUT='parm 2' + @parm GO DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @parmIN VARCHAR(10) DECLARE @parmRET1 VARCHAR(30) DECLARE @parmRET2 VARCHAR(30) SET @parmIN=' returned' SET @SQLString=N'EXEC Myproc @parm, @parm1OUT OUTPUT, @parm2OUT OUTPUT' SET @ParmDefinition=N'@parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition, @parm=@parmIN, @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2" GO DROP PROCEDURE Myproc

请查看下面的示例，通过执行存储过程获得输出变量值。

    DECLARE @return_value int,
    @Ouput1 int,
    @Ouput2 int,
    @Ouput3 int

EXEC    @return_value = 'Your Sp Name'
        @Param1 = value1,
        @Ouput1 = @Ouput1 OUTPUT,
        @Ouput2 = @Ouput2 OUTPUT,
        @Ouput3 = @Ouput3 OUTPUT

SELECT  @Ouput1 as N'@Ouput1',
        @Ouput2 as N'@Ouput2',
        @Ouput3 as N'@Ouput3'

操作步骤示例:

Create Procedure [dbo].[test]
@Name varchar(100),
@ID int Output   
As  
Begin   
SELECT @ID = UserID from tbl_UserMaster where  Name = @Name   
Return;
END     

如何调用这个过程

Declare @ID int    
EXECUTE [dbo].[test] 'Abhishek',@ID OUTPUT   
PRINT @ID