Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3554
  • Last Modified:

Must declare scaler variable?

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)
0
donnatronious
Asked:
donnatronious
  • 3
  • 3
1 Solution
 
WizillingCommented:
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)
0
 
donnatroniousAuthor Commented:
Ok so why does that work?  Must everything be cast as some type of character before executing SQL with exec?
0
 
WizillingCommented:
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 +

0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
donnatroniousAuthor Commented:
So why cast as varchar(5) then?
0
 
WizillingCommented:
oh, yea.. the @sql is of type varchar... and concat a int (@seq) would then create errors.
ie u can only concat data type of the same type.. hence convert the int value of @sql to varchar so that it can be concatanated to @sql...
0
 
donnatroniousAuthor Commented:
Ok, that makes sense...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now