Variable for Column Alias

Question

Variable for Column Alias

03Explorer (TechnicalUser)

(OP)

21 May 09 14:48

I am trying to make a variable driven code that the user enters criteria at the top of the SQL code and the output is custom driven.

Example:

CODE

DECLARE @StartDate DATETIME         SET @StartDate = '2009/01/01 00:00:00'
DECLARE @EndDate DATETIME           SET @EndDate   = '2009/05/20 23:59:59'
DECLARE @APPLICATION VARCHAR(3)        SET @APPLICATION = 'PRV'
DECLARE @ELEMENT1name VARCHAR(100)     SET @ELEMENT1name = 'MemberID'
DECLARE @ELEMENT1 VARCHAR(100)         SET @ELEMENT1 = 'PatID_ValidIDPrompt'
DECLARE @ELEMENT2name VARCHAR(100)     SET @ELEMENT2name = 'Total_Calls'
DECLARE @ELEMENT2 VARCHAR(100)         SET @ELEMENT2 = 'PatID_Silencer'

SELECT
  apname as @application,
  table2.elemname as @ELEMENT1name,
  table1.elemname as @ELEMENT2name,
  @ELEMENT1 / @ELEMENT2 as 'percentage'
FROM
...

This is a simplified snipit of the code I am trying to make more dynamic.

markros (Programmer) · Answer 1 · 2009-05-21 15:37:31

If you want to format your columns certain way (make the names of the fields to match your variables), then you should construct the whole query as one string and execute it using execute statement.

03Explorer (TechnicalUser) · Answer 2 · 2009-05-21 15:50:59

I am not certain on what you are saying... I want the column names to be resembling the field variables, but not as technical.

Can you share a simple example of what you are describing?

markros (Programmer) · Answer 3 · 2009-05-21 17:32:06

DECLARE @StartDate DATETIME         SET @StartDate = '2009/01/01 00:00:00'
DECLARE @EndDate DATETIME           SET @EndDate   = '2009/05/20 23:59:59'
DECLARE @APPLICATION VARCHAR(3)        SET @APPLICATION = 'PRV'
DECLARE @ELEMENT1name VARCHAR(100)     SET @ELEMENT1name = 'MemberID'
DECLARE @ELEMENT1 VARCHAR(100)         SET @ELEMENT1 = 'PatID_ValidIDPrompt'
DECLARE @ELEMENT2name VARCHAR(100)     SET @ELEMENT2name = 'Total_Calls'
DECLARE @ELEMENT2 VARCHAR(100)         SET @ELEMENT2 = 'PatID_Silencer'

declate @SqlStr varchar(4000)

SET @SqlStr = 'SELECT
  apname as ' + @application + ', table2.elemname as ' + @ELEMENT1name + ', table1.elemname as ' + @ELEMENT2name +
',' + @ELEMENT1 +'/'+ @ELEMENT2 + 'as '''percentage''' FROM '...
print @SqlStr -- to test
execute(@SqlStr)

Come Join Us!

Posting Guidelines

Jobs

Variable for Column Alias