问当选项被注释掉时返回更多结果的SQL
EN

Stack Overflow用户

提问于 2018-03-23 03:22:56

回答 1查看 44关注 0票数 0

这里有一点关于sql的情况，我正在为存储过程更改一段sql，我已经编写了下面的内容，现在我碰到了一堵墙，

当我按原样运行查询时，它将返回19个结果，注释掉所有显示为可选模块的内容，并返回31个结果，其基本思想是从xml文件中读取数据，确定文件中的值，然后匹配我们在这个sql中的值，所以它会说如果a是那么1，如果b是0，那么它就是它的许可或非许可场景，

然而，我认为它的作用并不像它应该做的那样。

   -- Grab our license
 DECLARE @xml XML
 SELECT TOP 1 @xml = CAST(LicenceKey AS xml) FROM Organisation

 -- Calculate our licensed modules
 DECLARE @LicensedModules TABLE (moduleid INT)

 Declare @optionalmodules table (moduleid INT,description varchar (100))

 INSERT INTO @optionalmodules (moduleid, description) values (1,'R9')
 INSERT INTO @optionalmodules (moduleid, description) values (2,'S8')
 INSERT INTO @optionalmodules (moduleid, description) values (6,'S7')
 INSERT INTO @optionalmodules (moduleid, description) values (8,'A6')
 INSERT INTO @optionalmodules (moduleid, description) values (9,'C5')
 INSERT INTO @optionalmodules (moduleid, description) values (10,'S4')
 INSERT INTO @optionalmodules (moduleid, description) values (11,'A2')
 INSERT INTO @optionalmodules (moduleid, description) values (12,'P4')
 INSERT INTO @optionalmodules (moduleid, description) values (13,'PSL')
 INSERT INTO @optionalmodules (moduleid, description) values (14,'H4')
 INSERT INTO @optionalmodules (moduleid, description) values (15,'F2')
 INSERT INTO @optionalmodules (moduleid, description) values (16,'C1')
 INSERT INTO @optionalmodules (moduleid, description) values (17,'H2')
 INSERT INTO @optionalmodules (moduleid, description) values (18,'P3')
 INSERT INTO @optionalmodules (moduleid, description) values (20,'G1')
 INSERT INTO @optionalmodules (moduleid, description) values (21,'E2')
 INSERT INTO @optionalmodules (moduleid, description) values (22,'R1')
 INSERT INTO @optionalmodules (moduleid, description) values (23,'H1')
 INSERT INTO @optionalmodules (moduleid, description) values (24,'S2')
 INSERT INTO @optionalmodules (moduleid, description) values (25,'i1')
 INSERT INTO @optionalmodules (moduleid, description) values (26,'G1')
 INSERT INTO @optionalmodules (moduleid, description) values (27,'S1')
 INSERT INTO @optionalmodules (moduleid, description) values (28,'E1')
 INSERT INTO @optionalmodules (moduleid, description) values (29,'F1')
 INSERT INTO @optionalmodules (moduleid, description) values (31,'T2')
 INSERT INTO @optionalmodules (moduleid, description) values (32,'A1')
 INSERT INTO @optionalmodules (moduleid, description) values (33,'P2')
 INSERT INTO @optionalmodules (moduleid, description) values (16016,'P1')


INSERT INTO @LicensedModules

    SELECT CAST(sl.LookupReference AS INT)--,
        --sl.LookupReference, 
        --sl.Description, 
        --m.*,
        --cast(case license.value('.', 'varchar(3)') when 'Yes' then 1 else 0 end as bit) as optModLicenced,
        --case WHEN m.moduleid IS NULL THEN 1 ELSE cast(case license.value('.', 'varchar(3)') when 'Yes' then 1 else 0 end as bit) END AS isLicenced
    FROM SystemLookup sl
        LEFT JOIN @optionalmodules m ON sl.LookupReference = m.moduleid 
        Left Join @xml.nodes('//Modules/*') as organisation(license) on m.description = organisation.license.value('local-name(.)', 'varchar(50)')
    WHERE sl.LookupTypeId = 1 
        AND case WHEN m.moduleid IS NULL THEN 1 ELSE cast(case license.value('.', 'varchar(3)') when 'Yes' then 1 else 0 end as bit) END = 1

Select * FROM @LicensedModules