asked on

Wanting to use variables in query inside while loop

So, my problem is that my variables @CM, @CMD, @CM2 and @CM2D don't seem to be used properly in the insert statement. The query does run though. I have never tried to use variables in this manner, so I am sure to be mistaken somwhere. Please let me know if something doesn't make sense.

      --Create table to hold looping variables and populate table
      --Values should be modified when new columns are to be compared.

if objectproperty(object_id('CMvCM2.dbo.DEMO_Analysis_Var'), 'IsTable') = 1
drop table CMvCM2.dbo.DEMO_Analysis_Var
create table CMvCM2.dbo.DEMO_Analysis_Var (seq int, bitmask bigint, CM varchar(30), CMD varchar(30), CM2 varchar(30), CM2D varchar(30))

      insert into CMvCM2.dbo.DEMO_Analysis_Var values (1, 2, 'b.homephone', '', 'a.homephone', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (2, 4, 'b.homephone2', '', 'a.homephone2', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (3, 8, 'b.workphone', '', 'a.workphone', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (6, 64, 'b.Address1', '', 'a.Address1', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (8, 256, 'b.city', '', 'a.city', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (9, 512, 'b.state', '', 'a.state', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (10, 1024, 'b.zip5', '', 'a.zip5', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (11, 2048, 'b.zip4', '', 'a.zip4', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (12, 4096, 'b.foreignaddr', '', 'a.foreignaddr', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (13, 8192, 'b.firstname', '', 'a.FirstName', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (14, 16384, 'b.lastname', '', 'a.LastName', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (15, 32768, 'b.middlename', '', 'a.MiddleName', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (16, 65536, 'b.NameSuffix', '', 'a.NameSuffix', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (17, 131072, 'b.NamePrefix', '', 'a.NamePrefix', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (18, 262144, 'b.FirmName', '', 'a.firmname', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (19, 524288, 'b.birthdate', '1900/01/01', 'a.birthdate', '1/1/1900')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (20, 1048576, 'b.Gender', 0, 'a.gender', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (21, 2097152, 'b.race', 0, 'a.race', 0)
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (22, 4194304, 'b.ssn', '', 'a.ssn', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (23, 8388608, 'b.PRIMARYBORROWERFLAG', '', 'a.PRIMARYBORROWERFLAG', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (24, 16777216, 'b.emailprimary', '', 'a.emailprimary', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (25, 33554432, 'b.emailsecondary', '', 'a.emailsecondary', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (26, 67108864, 'b.emailother', '', 'a.emailother', '')
      insert into CMvCM2.dbo.DEMO_Analysis_Var values (27, 134217728, 'b.emailunknown', '', 'a.emailunknown', '')

-- Perform Analysis for All Loans CCN and ACCT
/**************************************************************************************************/

      --Create Results Table
if objectproperty(object_id('CMvCM2.dbo.DEMO_Analysis_Results'), 'IsTable') = 1
drop table CMvCM2.dbo.DEMO_Analysis_Results
create table CMvCM2.dbo.DEMO_Analysis_Results (attr varchar(3),cnt int, NullCM int, NullCM_CM2ZorE int, NullCM2 int, NullCM2_CMZorE int, BothNull int, BothNotNull int, BothNotNullCMZorE int, BothNotNullCM2ZorE int)

      --Variable Declaration
      declare @seq as int
      declare @bitmask bigint
      declare @CM varchar(30) -- CM column name
      declare @CMD varchar(30) -- CM Empty String or 0 value
      declare @CM2 varchar(30) -- CM2 column name
      declare @CM2D varchar(30) -- CM2 Empty String or 0 value

set @seq = 0

while @seq is not null begin
select @seq = min(seq)
      from CMvCM2.dbo.DEMO_Analysis_Var
      where seq > @seq

      --Populate Variables
if @seq is not null begin
            select @bitmask = bitmask,
                        @CM = CM,
                        @CMD = CMD,
                        @CM2 = CM2,
                        @CM2D = CM2D
            from CMvCM2.dbo.DEMO_Analysis_Var
            where seq = @seq
--
            insert into CMvCM2.dbo.DEMO_Analysis_Results
            select a.attr, a.cnt, b.NullCM, bb.NullCM_CM2ZorE, c.NullCM2, cc.NullCM2_CMZorE, d.BothNull, e.BothNotNull, ee.BothNotNullCMZorE, eee.BothNotNullCM2ZorE
            from      (select '-' + str(@seq) Attr, count(distinct z.ccn) CNT
                        from CMvCM2_CompareDEMO z
                        where score1 & cast(@bitmask as bigint) = @bitmask) a
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) NullCM
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is not null and @CM2 != @CM2D and @CM is null) b
            on a.attr = b.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) NullCM_CM2ZorE
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is not null and @CM2 = @CM2D and @CM is null) bb
            on a.attr = bb.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) NullCM2
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is null and @CM is not null and @CM != @CMD) c
            on a.attr = c.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) NullCM2_CMZorE
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is null and @CM is not null and @CM = @CMD) cc
            on a.attr = cc.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) BothNull
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is null and @CM is null) d
            on a.attr = d.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) BothNotNull
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is not null and @CM is not null and @CM != @CMD and @CM2 != @CM2D) e
            on a.attr = e.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) BothNotNullCMZorE
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is not null and @CM is not null and @CM = @CMD) ee
            on a.attr = ee.attr
            join      (select '-' + str(@seq) Attr, count(distinct z.ccn) BothNotNullCM2ZorE
                        from CMvCM2_CompareDEMO z
                              join CMvCM2.dbo.cm2_Demographics_20070825 a
                               on a.ccn = z.ccn and a.acct = z.acct
                              join CMvCM2.dbo.cm_Demographics_20070825 b
                               on b.ccn = z.ccn and b.acct = z.acct
                        where score1 & cast(@bitmask as bigint) = @bitmask
                        and @CM2 is not null and @CM is not null and @CM2 = @CM2D) eee
            on a.attr = eee.attr

      end
end

Swindle

If you encounter a time through your loop where one of the values that you set is null, it will leave the old value stored in your variable and last record's information into the current record. I would suggest clearing out your set variables as the last step in your loop.

SET @bitmask = null
SET @CM = null
SET @CMD = null
SET @CM2 = null
SET @CM2D = null

If that's not the problem you're having, is there an error message you are getting to make you think it's not working, or does the data just come out wrong?

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

donnatronious

ASKER

So here is the first set of variables,

insert into CMvCM2.dbo.DEMO_Analysis_Var values (1, 2, 'b.homephone', '', 'a.homephone', '')

Here is what the insert statement would look like for the first set of variables. Does this make sense? So I want the loop to use the above variables to insert the below.

      insert into CMvCM2.dbo.CMvCM2_DEMO_AnalysisCCNACCT
      select a.attr, a.cnt, b.NullCM, bb.NullCM_CM2ZorE, c.NullCM2, cc.NullCM2_CMZorE, d.BothNull, e.BothNotNull, ee.BothNotNullCMZorE, eee.BothNotNullCM2ZorE
      from      (select '-1' Attr, count(*) CNT
                  from CMvCM2_CompareDEMO
                  where score1 & cast(2 as bigint) = 2) a
      join      (select '-1' Attr, count(*) NullCM
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is not null and a.homephone != '' and b.homephone is null) b
      on a.attr = b.attr
      join      (select '-1' Attr, count(*) NullCM_CM2ZorE
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is not null and a.homephone = '' and b.homephone is null) bb
      on a.attr = bb.attr
      join      (select '-1' Attr, count(*) NullCM2
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is null and b.homephone is not null and b.homephone != '') c
      on a.attr = c.attr
      join      (select '-1' Attr, count(*) NullCM2_CMZorE
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is null and b.homephone is not null and b.homephone = '') cc
      on a.attr = cc.attr
      join      (select '-1' Attr, count(*) BothNull
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is null and b.homephone is null) d
      on a.attr = d.attr
      join      (select '-1' Attr, count(*) BothNotNull
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is not null and b.homephone is not null and b.homephone != '' and a.homephone != '') e
      on a.attr = e.attr
      join      (select '-1' Attr, count(*) BothNotNullCMZorE
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is not null and b.homephone is not null and b.homephone = '') ee
      on a.attr = ee.attr
      join      (select '-1' Attr, count(*) BothNotNullCM2ZorE
                  from CMvCM2_CompareDEMO z
                        join CMvCM2.dbo.cm2_Demographics_20070825 a
                         on a.ccn = z.ccn and a.acct = z.acct
                        join CMvCM2.dbo.cm_Demographics_20070825 b
                         on b.ccn = z.ccn and b.acct = z.acct
                  where score1 & cast(2 as bigint) = 2
                  and a.homephone is not null and b.homephone is not null and a.homephone = '') eee
      on a.attr = eee.attr

donnatronious

ASKER

Swindle,

query runs the same with or without,

SET @bitmask = null
SET @CM = null
SET @CMD = null
SET @CM2 = null
SET @CM2D = null

donnatronious

ASKER

dtodd,

I think your right, all I am trying to do is replace a part of the query and run it again. I don't actually want to test if the variable is null, I want to set the column name which is to be tested!

donnatronious

ASKER

Isn't there a way to just execute an sql statement with 'exec' or something? Maybe I need to use that method?