/*为动态SQL赋值*/
DECLARE @ZIPCODE NVARCHAR(50);
DECLARE @SQL NVARCHAR(200);
DECLARE @PARAM_DEF NVARCHAR(200);
SET @SQL =N'SELECT * from Company where zipcode=@VAR_ZIPCODE';
SET @PARAM_DEF=N'@VAR_ZIPCODE NVARCHAR(20)';
SET @ZIPCODE='10016';
EXECUTE sp_executesql @SQL, @PARAM_DEF,
@VAR_ZIPCODE = @ZIPCODE;
/*从动态SQL 中获取数值*/
DECLARE @ZIPCODE NVARCHAR(50);
DECLARE @SQL NVARCHAR(200);
DECLARE @PARAM_DEF NVARCHAR(200);
DECLARE @P_OUT NVARCHAR(20);
SET @SQL =N'SELECT @V_OUT=companyName from Company where zipcode=@VAR_ZIPCODE';
SET @PARAM_DEF=N'@VAR_ZIPCODE NVARCHAR(20),@V_OUT VARCHAR(20) OUTPUT';
SET @ZIPCODE='10016';
EXECUTE sp_executesql @SQL, @PARAM_DEF,
@VAR_ZIPCODE = @ZIPCODE,
@V_OUT =@P_OUT OUTPUT;
SELECT @P_OUT
下面介绍一下SQL SERVER中,如何为动态SQL设置变量以及获取动态SQL的输出变量.