We help IT Professionals succeed at work.

Must declare scaler variable?

3,585 Views
Last Modified: 2008-01-09
Whats the problem here?

declare @seq as int
set @seq = 1
declare @sql varchar(4000)

set @sql = 'insert into CMvCM2.dbo.DEMO_Analysis_Results
      select a.attr, a.cnt, b.NullCM
      from      (select @seq  Attr, count(*) CNT
                  from CMvCM2_CompareDEMO
                  where score1 & cast(2 as bigint) = 2) a
      join      (select @seq  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'

exec (@sql)
Comment
Watch Question

declare @seq as int
set @seq = 1
declare @sql AS varchar(4000)

set @sql = 'insert into CMvCM2.dbo.DEMO_Analysis_Results
      select a.attr, a.cnt, b.NullCM
      from      (select '+ cast(@seq AS varchar(5)) +' Attr, count(*) CNT
                  from CMvCM2_CompareDEMO
                  where score1 & cast(2 as bigint) = 2) a
      join      (select '+ cast(@seq AS varchar(5)) +'  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'

exec (@sql)

Author

Commented:
Ok so why does that work?  Must everything be cast as some type of character before executing SQL with exec?
your @seq statement is only available outside the dynamic insert statement.
you can use the @seq statment inside like u had previously done. hence u need to concat the string with +

Author

Commented:
So why cast as varchar(5) then?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok, that makes sense...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.