Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Valerii79 Member Откуда: Кишинев, Молдавия Сообщений: 395 |
C XML никогда не работал. Можно ли переписать этот код без использования курсора?DECLARE @attrs TABLE( dbtesthis bigint , device bigint , twsid int , dt datetime , testhis int , property int , constants xml (Hard.TIS2ConstantsSchemaCollection) , datai bigint , dataf float , datas nvarchar(256) -- , val sql_variant -- , attribute int , constid bigint ) INSERT @attrs (dbtesthis, device, twsid, dt, testhis , property, constants, datai, dataf, datas, val, attribute, constid) SELECT ref.dbid, t.device, a.twsid, a.dt, a.testhis, a.property, CASE LEN (RTRIM (LTRIM (CAST (a.consts as nvarchar(max))))) WHEN 0 THEN NULL ELSE a.consts END as constants , a.datai, a.dataf, a.datas, -- CASE WHEN a.property BETWEEN 268435456/*0x10000000*/ AND 536870911/*0x1FFFFFFF*/ THEN a.datai WHEN a.property BETWEEN 536870912/*0x20000000*/ AND 805306367/*0x2FFFFFFF*/ THEN a.dataf WHEN a.property BETWEEN 805306368/*0x30000000*/ AND 1073741823/*0x3FFFFFFF*/ THEN a.datas ELSE NULL END as val -- , (SELECT attribute FROM Hard.tAttributeProperties WHERE code = a.property) as attribute , CASE WHEN LEN (RTRIM (LTRIM (CAST (a.consts as nvarchar(max))))) = 0 THEN (SELECT id FROM Hard.tAttributeConstants as ac JOIN Hard.tAttributeProperties as ap ON ap.attribute = ac.attribute WHERE ap.code = a.property AND ac.consts IS NULL) ELSE (SELECT id FROM @attribute_constants as ac JOIN Hard.tAttributeProperties as ap ON ap.attribute = ac.attribute WHERE ap.code = a.property AND ac.consts = CAST (a.consts as varchar(896))) END as constid FROM Data.tCacheAttributes a INNER JOIN Data.tCacheTests t ON t.twsid = a.testhis AND t.spid = a.spid INNER JOIN @ref ref ON t.twsid = ref.twsid WHERE a.spid = @spid ORDER BY a.twsid DECLARE @constants xml (Hard.TIS2ConstantsSchemaCollection) , @constid bigint , @attribute int DECLARE attrs_curs CURSOR LOCAL FAST_FORWARD FOR SELECT constants, attribute, constid FROM @attrs OPEN attrs_curs WHILE 1 = 1 BEGIN FETCH NEXT FROM attrs_curs INTO @constants, @attribute, @constid IF @@FETCH_STATUS <> 0 BREAK IF @constid IS NULL BEGIN -- check XML attributes IF @constants IS NOT NULL BEGIN DECLARE @err int SET @err = ( SELECT COUNT (*) FROM @constants.nodes ('declare namespace ns = "urn:TIS2:Constants"; /ns:constant') T(c) WHERE (CAST (T.c.value ('@code', 'int') as int) NOT BETWEEN 0 AND 268435455/*0x0FFFFFFF*/) OR (CAST (T.c.value ('@code', 'int') as int) NOT IN (SELECT code FROM Hard.tAttributeProperties)) ) IF @err > 0 RAISERROR ('Invalid constants in XML configuration!', 15, 2) END DECLARE @tId TABLE (id bigint) DELETE FROM @tId INSERT INTO Hard.tAttributeConstants (attribute, consts) OUTPUT inserted.id INTO @tId VALUES (@attribute, @constants) SET @constid = (SELECT id FROM @tId) INSERT INTO @attribute_constants(id, attribute, consts) SELECT @constid, @attribute, cast(@constants as varchar(896)) WHERE NOT EXISTS (SELECT * FROM @attribute_constants WHERE attribute = @attribute AND consts = cast(@constants as varchar(896)) ) END END CLOSE attrs_curs DEALLOCATE attrs_curs |
15 фев 13, 13:13 [13931403] Ответить | Цитировать Сообщить модератору |
LexusR Member Откуда: Novosibirsk Сообщений: 1887 |
в первом приближении всё можно заменить наINSERT INTO Hard.tAttributeConstants (attribute, consts) SELECT ap.attribute, a.consts from Data.tCacheAttributes a INNER JOIN Data.tCacheTests t ON t.twsid = a.testhis AND t.spid = a.spid -- убрать если это не фильтрация INNER JOIN @ref ref ON t.twsid = ref.twsid -- убрать если это не фильтрация CROSS APPLY a.consts.nodes('declare namespace ns = "urn:TIS2:Constants"; //ns:constant') T(c) INNER JOIN Hard.tAttributeProperties ap on ap.code = a.property OR ap.code = CAST (T.c.value ('@code', 'int') as int) WHERE a.spid = @spid and LEN (RTRIM (LTRIM (CAST (a.consts as nvarchar(max)))))>0 and CAST (T.c.value ('@code', 'int') as int) NOT BETWEEN 0 AND 268435455/*0x0FFFFFFF*/ and not exists(select * from Hard.tAttributeConstants hac where hac.attribute = ap.attribute and hac.consts = a.consts) |
15 фев 13, 14:22 [13931951] Ответить | Цитировать Сообщить модератору |
Valerii79 Member Откуда: Кишинев, Молдавия Сообщений: 395 |
LexusR, Благодарю! |
18 фев 13, 10:55 [13941268] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |