/*为动态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的输出变量.