Variable Name used for Column Alias

• taffy

  Ten Centuries

  Points: 1067

  More actions

  November 14, 2003 at 4:24 am

  #81529

  Hi there

  I have written a weekly revenue report that when run, totals the amount of money we have taken so far on a certain code, and then for the last 6 weeks how many transactions there were per week. I will then email it to Marketing.

  I would like for the column titles for the past 6 weeks to be week45,week44 etc etc, so to automatically fill in the week number for the last 6 weeks.

  Have tried this

  DECLARE @CurrentWeek int

  SET @CurrentWeek=DatePart(week, GetDate())-1

  Is there any way I can set the column alias to be a variable???

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  November 14, 2003 at 5:20 am

  #482015

  You would need to use dynamic SQL, e.g.:

  
  
  EXEC('SELECT SUM(Revenue) AS Week' + RIGHT('0' + CAST(@CurrentWeek AS varchar(2)),2) + '
  
  FROM MyTable
  
  WHERE SaleDate BETWEEN...')

  --Jonathan

  
  
  --Jonathan

• sqlSushi

  Mr or Mrs. 500

  Points: 516

  More actions

  November 18, 2003 at 2:11 pm

  #482506

  You might also look into sp_executesql.

  Basically it allows you to build a dynamic sql query and run it in a Stored Proc.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 19, 2003 at 12:32 am

  #482550

  I think you can use EXEC and sp_executeSQL in a stored procedure.

  However, the advantage of sp_executeSQL is that this one is more likely to reuse execution plans.

  As for my standard link on dynamic SQL you should read this

  http://www.algonet.se/~sommar/dynamic_sql.html

  Frank

  http://www.insidesql.de

  http://www.familienzirkus.de

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]