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

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

你可以这样做:

declare @rowCount int
exec yourStoredProcedureName @outputparameterspOf = @rowCount output

首先，声明输出变量:

DECLARE @MyOutputParameter INT;

然后，执行存储过程，你可以不带参数名，就像这样:

EXEC my_stored_procedure 'param1Value', @MyOutputParameter OUTPUT

或者使用参数名:

EXEC my_stored_procedure @param1 = 'param1Value', @myoutput = @MyOutputParameter OUTPUT

最后，你可以通过执行SELECT命令查看输出结果:

SELECT @MyOutputParameter 

下面是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 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