Lm th? no ? xc ?nh cc thng s? ?u ra khi b?n s? d?ng sp_executesql lu tr? th? t?c trong SQL Server

D?ch tiu ? D?ch tiu ?
ID c?a bi: 262499 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

TM T?T

Cc sp_executesql h? th?ng lu tr? th? t?c ?c s? d?ng ? th?c thi l?nh m?t T-SQL m c th? ?c ti s? d?ng nhi?u l?n, ho?c th?c hi?n m?t tuyn b? t? ?ng xy d?ng T-SQL. Ph?i m?t tham s? nh ?u vo ? x? l? cc T-SQL pht bi?u ho?c l. N c?ng cho php cc thng s? s?n l?ng ? ?c xc ?nh ? cho b?t k? ?u ra ?c t?o ra t? T-SQL pht bi?u c th? ?c lu tr? (m?c d i?u ny khng ph?i l ti li?u trong SQL Server sch tr?c tuy?n).

Hai k?ch b?n trong cc thng s? s?n l?ng s? h?u ch v?i sp_executesql l:
  • N?u sp_executesql t?o ra s?n l?ng s? c ch, lu tr? ny ?u ra cho m?t tham s? ?u ra cho php nh?ng ?t ku g?i ? s? d?ng cc tham s? cho cc truy v?n sau ny.
  • N?u sp_executesql th?c hi?n m?t th? t?c ?c lu tr? ?c xc ?nh b?ng cch s? d?ng tham s? ?u ra, cc tham s? ?u ra cho sp_executesql c th? ?c s? d?ng ? gi? cc k?t qu? ?u ra ?c t?o ra t? cc th? t?c ?c lu tr?.

THNG TIN THM

Hai v d? sau y ch?ng minh vi?c s? d?ng cc tham s? ?u ra v?i sp_executesql.

V d? 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
				
V d? 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
				
? c thm thng tin, xem "sp_executesql (T-SQL)" v "S? d?ng sp_executesql" trong SQL Server sch tr?c tuy?n. ? c thm thng tin, xem "S? d?ng sp_executesql" trong SQL Server sch tr?c tuy?n, v "sp_executesql (T-SQL) trong SQL Server 7.0 sch tr?c tuy?n" ho?c "sp_executesql" trong SQL Server 2000 cu?n sch tr?c tuy?n.

Thu?c tnh

ID c?a bi: 262499 - L?n xem xt sau cng: 21 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • 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
T? kha:
kbsqlsetup kbinfo kbmt KB262499 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:262499

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com