?
Solved

Wanting to use variables in query inside while loop

Posted on 2007-10-01
6
Medium Priority
?
177 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:donnatronious
  • 4
6 Comments
 
LVL 11

Expert Comment

by:Swindle
ID: 19992893
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?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 19992925
Hi,

I think that you are using the variables in a way that they are not meant to be used.

Your logic is something like this:
  select values into variables
  insert tablename
  select values
  from subselect where variable1 is not null and variable2 = variable3
  join subselect where variable1 is not null and variable2 = variable3
  join subselect where variable1 is not null and variable2 = variable3
  ...

Those variables wont change - you appear to be using them to short circuit the join. Another way to code what you have written is this:
  select values into variables
  if variable1 is not null and variable2 = variable3 begin
    insert
    select somevalues
    from sometables
    join someothertables
  end

HTH
  David
0
 

Author Comment

by:donnatronious
ID: 19993178
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:donnatronious
ID: 19993612
Swindle,

query runs the same with or without,

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

Author Comment

by:donnatronious
ID: 19993630
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!
0
 

Author Comment

by:donnatronious
ID: 19993714
Isn't there a way to just execute an sql statement with 'exec' or something?  Maybe I need to use that method?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question