???? ID: 262499 - ????? ???????: 02 ?????? 2010 - ??????: 2.0

How to specify output parameters when you use the sp_executesql stored procedure in SQL Server

???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
??? ?? ??????? ???? | ??? ?? ??????? ????

??????

Thesp_executesqlsystem stored procedure is used to execute a T-SQL statement which can be reused multiple times, or to execute a dynamically built T-SQL statement. It takes parameters as inputs in order to process the T-SQL statements or batches. ?? ?? ?? ??? ?? ?????? T-SQL ??? ?? ??????? ???????? ???? ?? ???? (?????? ?? SQL Server ???????? ?????? ??? ????? ???? ??) ????????? ???? ?? ???? ?? ?????? ?????????? ?? ?????? ???

?? ???????? ??? ?? ?????? ???????? ?? ??? ?????? ????sp_executesql???:
  • ???sp_executesql??????, ?? ?????? ???????? ?? ??? ?? ?????? ?? ???????? ???? ?? ??? ?????? ???????? ?? ????? ???? ?? ??? ?????? ??? ?? ?????? ???? ?? ???? ?????? ????? ???
  • ???sp_executesql???????? ????????? ?????? ????????, ?? ??? ?????? ???????? ?? ????? ???? ??? ????????? ?? ?? ????????? ?? ??? ??sp_executesql???? ?????? ???????? ????????? ?? ????? ???? ?? ??? ????? ???? ?? ?????

???? ???????

????? ?? ?????? demonstrate ?? ??? ?????? ???????? ?? ?????sp_executesql.

?????? 1
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
                   FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                        @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname
?????? 2
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
???????? ??????? ?? ???, "sp_executesql (T-SQL)" ?? "using sp_executesql" ??? SQL Server ???????? ?????? ?????? ???????? ??????? ?? ??? SQL Server ???????? ?????? ??? "using sp_executesql" ?? "sp_executesql (T-SQL) ??? SQL Server 7.0 ???????? ??????" ?? SQL Server 2000 ???????? ?????? ??? "sp_executesql" ??????
???? ???? ???? ??:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
????? ?? ???? ????
??????: 
kbinfo kbmt KB262499 KbMthi
???? ?????? ???????????? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:262499  (http://support.microsoft.com/kb/262499/en-us/ )
????? ?? ???? ????