关于执行一段带有变量的sql语句有关问题
关于执行一段带有变量的sql语句问题
请教一下,以下代码
现在我想执行一下这个@sql,肿么办?
------解决方案--------------------
exec(@sql)
------解决方案--------------------
请教一下,以下代码
- SQL code
declare @pTFNUpdateXml xml declare @tfnCriteriaElements xml declare @TollFreeNumberRegionID int, @TollFreeNumberCountryID int set @pTFNUpdateXml = ' <BulkEditQuery> <Criteria Field="TFNRegionID" Value="1" IsNull="False" /> <Criteria Field="TFNCountryID" Value="4" IsNull="False" /> <Criteria Field="TFNCarrierID" Value="1" IsNull="False" /> <Criteria Field="TFNCarrierAccount" Value="92888359" IsNull="False" /> <Criteria Field="TFNCarrierStartDate" Value="1/1/2011" IsNull="False" /> <Criteria Field="TFNCarrierEndDate" Value="1/1/2012" IsNull="False" /> <Criteria Field="TFNTypeID" Value="3" IsNull="False" /> <Criteria Field="TFNActiveBool" Value="1" IsNull="False" /> </BulkEditQuery>' set @tfnCriteriaElements = @pTFNUpdateXml.query('/BulkEditQuery/Criteria') select @pTFNUpdateXml select @tfnCriteriaElements declare @UpdateColumns table (ColID int not null identity(1,1) primary key, ColName nvarchar(128), ColNameAlias nvarchar(128)) insert @UpdateColumns (ColName,ColNameAlias) select 'TollFreeNumberRegionID','TFNRegionID' union all select 'TollFreeNumberCountryID','TFNCountryID' union all select 'TollFreeNumberCarrierID','TFNCarrierID' union all select 'CarrierAccountNumber','TFNCarrierAccount' union all select 'StartDate','TFNCarrierStartDate' union all select 'EndDate','TFNCarrierEndDate' union all select 'TollFreeNumberTypeID','TFNTypeID' union all select 'ActiveBool','TFNActiveBool' select * from @UpdateColumns declare @UpdateColumntable table (UpdateColID int not null identity(1,1) primary key, ColName nvarchar(128) not null, ColNameAlias nvarchar(100)not null, DataType nvarchar(32) not null, DataTypeDf nvarchar(64) not null, Nullable bit not null ) insert @UpdateColumntable(ColName,ColNameAlias,DataType,DataTypeDf,Nullable) select ColName,ColNameAlias,DATA_TYPE, case when DATA_TYPE in ('varchar','nvarchar') then DATA_TYPE + '('+ cast(character_maximum_length as nvarchar(8)) + ')' when DATA_TYPE in ('bit','int','datetime','smalldatetime') then DATA_TYPE end as DataTypeDf, case when IS_NULLABLE = 'NO' then 0 when IS_NULLABLE = 'YES' then 1 end as Nullable from INFORMATION_SCHEMA.COLUMNS ISC join @UpdateColumns UC on ISC.Column_Name = UC.ColName where table_name = 'TollFreeNumber' select * from @UpdateColumntable declare @sql nvarchar(max) set @sql = '' select top 1 @sql = 'set ' + '@' + ColName + ' = case when ' + '@tfnCriteriaElements.value(''' + '((/Criteria[@Field="' + ColNameAlias + '"])[1]/@Value)' + ''',''' + DataTypeDf + ''') = 0 then NULL else @tfnCriteriaElements.value(''' + '((/Criteria[@Field="' + ColNameAlias + '"])[1]/@Value)' + ''',''' + DataTypeDf + ''') end' from @UpdateColumntable print @sql
现在我想执行一下这个@sql,肿么办?
------解决方案--------------------
exec(@sql)
------解决方案--------------------
- SQL code
select top 1 @sql = 'select ' + '@' + ColName + ' = case when ' + '@tfnCriteriaElements.value(''' + '((/Criteria[@Field="' + ColNameAlias + '"])[1]/@Value)' + ''',''' + DataTypeDf + ''') = 0 then NULL else @tfnCriteriaElements.value(''' + '((/Criteria[@Field="' + ColNameAlias + '"])[1]/@Value)' + ''',''' + DataTypeDf + ''') end' from @UpdateColumntable print @sql exec(@sql)
------解决方案--------------------