sql server

• kuppurajm

  SSCommitted

  Points: 1593

  More actions

  November 18, 2010 at 10:16 pm

  #226786

  Hi,

  I want to Assign cte query(dynamic) resut to variable

  but I am getting error like (Must declare the scalar variable "@val").

  my query is:

  --------------

  declare @val int

  declare @tblname1 as varchar(50)

  declare @tblname2 as varchar(50)

  set @tblname1='tbl_00001'

  set @tblname2='tblSt_00001'

  exec('with cte1

  as

  (

  select line,Status_text,Zerox_ID from '+@tblname1+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  )

  select @val=(select count(*) from cte1)')

  thanks in advance

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  November 18, 2010 at 10:36 pm

  #1252859

  Try this

  select @val = count(*) from cte1

• kuppurajm

  SSCommitted

  Points: 1593

  More actions

  November 18, 2010 at 10:38 pm

  #1252860

  I have try already this

  but its not working

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  November 18, 2010 at 10:57 pm

  #1252862

  declare @val int

  declare @tblname1 as varchar(50)

  declare @tblname2 as varchar(50)

  set @tblname1='tbl_00001'

  set @tblname2='tblSt_00001'

  exec(';with cte1

  as

  (

  select line,Status_text,Zerox_ID from '+@tblname1+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  )

  select ' + @val + 'count(*) from cte1'

  )

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• Vishal.Gajjar

  SSCrazy

  Points: 2285

  More actions

  November 18, 2010 at 11:00 pm

  #1252863

  Use sp_executesql() instead of exec(),

  using sp_executesql(), you can specify @val as output parameter...

  Vishal Gajjar
  http://SqlAndMe.com

• kuppurajm

  SSCommitted

  Points: 1593

  More actions

  November 18, 2010 at 11:04 pm

  #1252865

  Hi

  thank you.Its working fine.but I am trying to print that variable nothing

  will be displayed

  code

  -----

  declare @val int

  declare @tblname1 as varchar(50)

  declare @tblname2 as varchar(50)

  set @tblname1='tbl_00001'

  set @tblname2='tblSt_00001'

  exec(';with cte1

  as

  (

  select line,Status_text,Zerox_ID from '+@tblname1+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'

  )

  select ' + @val + 'count(*) from cte1'

  )

  print @val

• kuppurajm

  SSCommitted

  Points: 1593

  More actions

  November 18, 2010 at 11:11 pm

  #1252868

  I am alredy try that method also

  declare @val int

  declare @sql varchar(max)

  declare @tblname1 as varchar(50)

  declare @tblname2 as varchar(50)

  set @tblname1='tbl_00001'

  set @tblname2='tblSt_00001'

  select @sql=N'with cte1

  as

  (

  select line,Status_text,Zerox_ID from '+@tblname1+' where allocateto is not null

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'close

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'hold

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'otherstatus

  )

  select @val=count(*) from cte1'

  EXEC sp_executesql @sql, N'@val int output', @val output

• Vishal.Gajjar

  SSCrazy

  Points: 2285

  More actions

  November 18, 2010 at 11:14 pm

  #1252869

  remove "@val=" from your query in sp_executesql()

  Vishal Gajjar
  http://SqlAndMe.com

• kuppurajm

  SSCommitted

  Points: 1593

  More actions

  November 18, 2010 at 11:22 pm

  #1252871

  Thank you.

  I got correct query.

  -------------------------

  declare @vcTemp int

  declare @sql nvarchar(max)

  declare @tblname1 as varchar(50)

  declare @tblname2 as varchar(50)

  set @tblname1='tbl_00001'

  set @tblname2='tblSt_00001'

  SELECT @sql='select @vcTemp=count(*) from(

  select line,Status_text,Zerox_ID from '+@tblname1+' where allocateto is not null

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'close

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'hold

  except

  select line,Status_txt,Zerox_file_ID from '+@tblname2+'otherstatus

  )as a'

  EXEC sp_executesql @sql, N'@vcTemp int output', @vcTemp output

  print @vcTemp

  This will work fine