Variable for Column Alias

03Explorer · May 21, 2009

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 · May 21, 2009

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 · May 21, 2009

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 · May 21, 2009

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)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Variable for Column Alias

03Explorer

Technical User

markros

Programmer

03Explorer

Technical User

markros

Programmer

Similar threads

Part and Inventory Search

Sponsor