操作步骤示例:

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

简单的方法是在Sql Server Management Studio (SSMS)中右键单击该过程，选择“Execute stored procedure…”，并根据提示为输入参数添加值。然后，SSMS将生成代码以在新的查询窗口中运行过程，并为您执行它。您可以研究生成的代码，了解它是如何完成的。

来自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

检查一下，其中前两个参数是输入参数，第三个是Procedure定义中的输出参数。

DECLARE @PK_Code INT;
EXEC USP_Validate_Login 'ID', 'PWD', @PK_Code OUTPUT
SELECT @PK_Code

下面是stored_proc的定义:

create proc product(@a int,@b int)
as
return @a * @b

这是从Python中执行的: Conn = pyodbc.connect('…') Cursor = conn.cursor()

sql = """
SET NOCOUNT ON
declare @r float
exec @r=dbo.product 5,4
select @r
"""
result = cursor.execute(sql)
print (result.fetchall())

CREATE PROCEDURE DBO.MY_STORED_PROCEDURE
(@PARAM1VALUE INT,
@PARAM2VALUE INT,
@OUTPARAM VARCHAR(20) OUT)
AS 
BEGIN
SELECT * FROM DBO.PARAMTABLENAME WHERE PARAM1VALUE=@PARAM1VALUE
END

DECLARE @OUTPARAM2 VARCHAR(20)
EXEC DBO.MY_STORED_PROCEDURE 1,@OUTPARAM2 OUT
PRINT @OUTPARAM2