How to build a field name in a select statement

Question

Post reply

How to build a field name in a select statement

BRP

Default port

Points: 1433
More actions
May 4, 2017 at 6:45 pm

#321210

I'm about to build aselect statement as a string and execute it so I can create the column namesfrom text and a query. the field name will be val_alpha and I need to append a number dynamically to that for a complete column name.
Is there a way I cando this directly in a select statement? this is what I've started with as an example:
declare @a asvarchar(5)
declare @sel asvarchar(500)
declare @frm asvarchar(500)
set @a = '1'
set @sel = 'SELECTA.REG_NAME,val_alpha' + @a
set @frm = 'FROMdbo.F1ASR_REG_ASSET_ATTR AS A
wherea.attr_unique_id =''BE2BDE358FB348E79167972CE418A5935DB2F8F1'''
set @sel = @sel + '' + @frm
exec (@sel)
or can I somehow use
Select a.reg_name, 'val_alpha' + '1' from...
Bruce

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

To do this, you would need to use dynamic SQL, such as:
DECLARE @Field_suffix char(1); DECLARE @sql varchar(MAX);

SET @Field_suffix = '1'; SET @sql = 'SELECT A.REG_NAME,val_alpha' + @Field_suffix + ' FROM blah blah blah...';

--Printing as this isn't a full SQL statement in the example PRINT @sql; --EXEC(@SQL); GO

Note, however, that you need to heavily consider SQL injection when using methods like this. In my example I've used a char(1), which would make it difficult for someone to inject something, however, in your statement you have varchar(500); if this is a parametrised stored procedure you need to ensure that someone couldn't abuse it.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ant-Green SSC Guru Points: 113509 More actions · Answer 2

If your worried about SQL Injection, you could have a look at Gail's "catch all" query and how she gets around SQL injection.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

While I appreciate this isn't a catch all scenario, the concept will still be the same of executing a built up dynamic string using parameters negating any injection foot print.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

anthony.green - Friday, May 5, 2017 2:52 AM
If your worried about SQL Injection, you could have a look at Gail's "catch all" query and how she gets around SQL injection.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
While I appreciate this isn't a catch all scenario, the concept will still be the same of executing a built up dynamic string using parameters negating any injection foot print.

Except that column names can't be parameterised, and hence that method of preventing injection can't be used.

The only semi-secure way to do this is to check, before executing, that 'val_alpha' + @a matches a legit column name in sys.columns, and refuse to execute if that's not the case.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ant-Green SSC Guru Points: 113509 More actions · Answer 4

GilaMonster - Friday, May 5, 2017 3:26 AM
Except that column names can't be parameterised, and hence that method of preventing injection can't be used.
The only semi-secure way to do this is to check, before executing, that 'val_alpha' + @a matches a legit column name in sys.columns, and refuse to execute if that's not the case.

My bad, thanks for the correction.

BRP Default port Points: 1433 More actions · Answer 5

Thanks. I was hoping that by now they would have something and I would not have to setup a sp.
I've never heard of injection so thanks for that.
Bruce

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

You also want to use QUOTENAME() to handle complex names for your columns or objects.
DECLARE @Field_suffix char(1); DECLARE @sql varchar(MAX);

SET @Field_suffix = '1';

SELECT @sql = 'SELECT A.REG_NAME,' + QUOTENAME( 'val_alpha' + @Field_suffix) + ' FROM blah blah blah...' FROM sys.columns WHERE name = 'val_alpha' + @Field_suffix AND object_id = OBJECT_ID('YourTable');

--Printing as this isn't a full SQL statement in the example PRINT @sql; --EXEC(@SQL); GO

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

GilaMonster - Friday, May 5, 2017 3:26 AM
anthony.green - Friday, May 5, 2017 2:52 AM
If your worried about SQL Injection, you could have a look at Gail's "catch all" query and how she gets around SQL injection.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
While I appreciate this isn't a catch all scenario, the concept will still be the same of executing a built up dynamic string using parameters negating any injection foot print.
Except that column names can't be parameterised, and hence that method of preventing injection can't be used.
The only semi-secure way to do this is to check, before executing, that 'val_alpha' + @a matches a legit column name in sys.columns, and refuse to execute if that's not the case.

+1,000,000 to that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BRP Default port Points: 1433 More actions · Answer 8

BRP

Default port

Points: 1433

May 7, 2017 at 5:42 pm

#1941494

thanks, much appreciated.