document.write('
Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1. sp_executesql
  2.  
  3. Example 1
  4.  
  5. DECLARE @SQLString NVARCHAR(500)
  6. DECLARE @ParmDefinition NVARCHAR(500)
  7. DECLARE @IntVariable INT
  8. DECLARE @Lastlname VARCHAR(30)
  9. SET @SQLString = N\'SELECT @LastlnameOUT = max(lname)
  10.                   FROM pubs.dbo.employee WHERE job_lvl = @level\'
  11. SET @ParmDefinition = N\'@level tinyint,
  12.                        @LastlnameOUT varchar(30) OUTPUT\'
  13. SET @IntVariable = 35
  14. EXECUTE sp_executesql
  15. @SQLString,
  16. @ParmDefinition,
  17. @level = @IntVariable,
  18. @LastlnameOUT=@Lastlname OUTPUT
  19. SELECT @Lastlname
  20.                
  21.  
  22. Example 2
  23.  
  24. CREATE PROCEDURE Myproc
  25.     @parm VARCHAR(10),
  26.     @parm1OUT VARCHAR(30) OUTPUT,
  27.     @parm2OUT VARCHAR(30) OUTPUT
  28.     AS
  29.       SELECT @parm1OUT=\'parm 1\' + @parm
  30.      SELECT @parm2OUT=\'parm 2\' + @parm
  31. GO
  32. DECLARE @SQLString NVARCHAR(500)
  33. DECLARE @ParmDefinition NVARCHAR(500)
  34. DECLARE @parmIN VARCHAR(10)
  35. DECLARE @parmRET1 VARCHAR(30)
  36. DECLARE @parmRET2 VARCHAR(30)
  37. SET @parmIN=\' returned\'
  38. SET @SQLString=N\'EXEC Myproc @parm,
  39.                             @parm1OUT OUTPUT, @parm2OUT OUTPUT\'
  40. SET @ParmDefinition=N\'@parm varchar(10),
  41.                      @parm1OUT varchar(30) OUTPUT,
  42.                      @parm2OUT varchar(30) OUTPUT\'
  43.  
  44. EXECUTE sp_executesql
  45.     @SQLString,
  46.     @ParmDefinition,
  47.     @parm=@parmIN,
  48.     @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT
  49.  
  50. SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
  51. GO
  52. DROP PROCEDURE Myproc
  53. http://support.microsoft.com/kb/262499
');