Dynamic variable name for AS variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic variable name for AS variable

Author

Topic

pixelwiz
Starting Member

25 Posts

Posted - 2011-08-26 : 14:15:39

Hi, I have the following SQL query and would like to append an ID to the end of a variable name. Is that possible?


DECLARE @sideID1 int, @sideID2 int

SET @sideID1 = (SELECT id FROM event_sides WHERE event_id = 5013 AND priority = 1)
SET @sideID2 = (SELECT id FROM event_sides WHERE event_id = 5013 AND priority = 2)

SELECT 
	SUM (CASE WHEN hole_points > 0 THEN 1 ELSE 0 END) AS ('winningSide' + @sideID1),
	SUM (CASE WHEN hole_points < 0 THEN 1 ELSE 0 END) AS ('winningSide' + @sideID1)
FROM (

	SELECT m.id match_id, m.session, m.match_number, m.match_type_id,
		dbo.udf_mp_getMatchPoints(m.id,@sideID1,@sideID2) hole_points
	FROM matches m
	WHERE m.event_id = 5013
	AND NOT EXISTS (SELECT 1 FROM match_points WHERE match_id = m.id)
) sub1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-26 : 14:20:51

I don't understand your question. Could you point out exactly where you want to edit it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-08-26 : 16:23:13

Hi,

You would have use dynamic sql to do that I believe.
Store your built sql command in a varchar variable and then execute it.
Do searches for "dynamic sql" and the sp_executesql command or just execute if you don't need to pass in parameters etc.

Something like

declare @sql varchar(1000), @id int
set @id = 1
set @sql = 'select mycol' + cast(@id as varchar(10) + ' from mytable'
execute(@sql)

Will execute: select mycol1 from mytable

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-26 : 16:42:03

The question is about variable names, isn't it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 04:44:06

I think op was asking a way to make aliases dynamic. I didnt understand need for this though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 07:09:46

quote:
Originally posted by tkizer

The question is about variable names, isn't it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Yes but a dynamic variable name

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 07:12:15

quote:

Originally posted by pixelwiz

Hi, I have the following SQL query and would like to append an ID to the end of a variable name. Is that possible?


DECLARE @sideID1 int, @sideID2 int

SET @sideID1 = (SELECT id FROM event_sides WHERE event_id = 5013 AND priority = 1)
SET @sideID2 = (SELECT id FROM event_sides WHERE event_id = 5013 AND priority = 2)

SELECT 
	SUM (CASE WHEN hole_points > 0 THEN 1 ELSE 0 END) AS ('winningSide' + @sideID1),
	SUM (CASE WHEN hole_points < 0 THEN 1 ELSE 0 END) AS ('winningSide' + @sideID1)
FROM (

	SELECT m.id match_id, m.session, m.match_number, m.match_type_id,
		dbo.udf_mp_getMatchPoints(m.id,@sideID1,@sideID2) hole_points
	FROM matches m
	WHERE m.event_id = 5013
	AND NOT EXISTS (SELECT 1 FROM match_points WHERE match_id = m.id)
) sub1

Why do you want to do this? Have a look at Cross-tab reports in SQL Server help file.

Madhivanan

Failing to plan is Planning to fail

pixelwiz
Starting Member

25 Posts

Posted - 2011-08-29 : 08:15:24

I needed to have the ID added to the alias to make something else easier, but I found another way to go about it. It'd still be cool if there was a way to make a dynamic alias name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 08:27:40

id added to alias? whats the purpose of that? how do you handle this at front end if column name changes at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources