Remove one column from SQL select statement

Question

Remove one column from SQL select statement

adonetok

SSCrazy Eights

Points: 8395
More actions
July 24, 2012 at 1:57 pm

#264677

In my application, user will select different columns to create a select statement.
For example, user A will create a statement like below:
Select orderID, orderDate, Amount, City from tableOrder
user B will create a statement like below:
Select orderID, orderDate from tableOrder
My app need to remove [City] no matter user how to select another columns.(about 20 columns in list)
How to check original user's SQL statement and make new SQL statement without [City] column?

Viewing 5 posts - 1 through 4 (of 4 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

it depends on how the user selects the columns they want. i would do any actual dynamic query creation with a stored procedure API level and validate any input given to the stored procedure.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

adonetok (7/24/2012)
In my application, user will select different columns to create a select statement.
For example, user A will create a statement like below:
Select orderID, orderDate, Amount, City from tableOrder
user B will create a statement like below:
Select orderID, orderDate from tableOrder
My app need to remove [City] no matter user how to select another columns.(about 20 columns in list)
How to check original user's SQL statement and make new SQL statement without [City] column?

The easy way to do this is to not allow the users to query the table directly. Build a that doesn't include the city column and give them SELECT access to that instead.

--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)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 3

capn.hector (7/24/2012)
it depends on how the user selects the columns they want. i would do any actual dynamic query creation with a stored procedure API level and validate any input given to the stored procedure.

If you create a view and remove city from it and give them the select access to the view. Wouldn't that work?

Regards,

TA

Regards,
SQLisAwe5oMe.

CapnHector SSCoach Points: 16143 More actions · Answer 4

SQLCrazyCertified (7/24/2012)
capn.hector (7/24/2012)
it depends on how the user selects the columns they want. i would do any actual dynamic query creation with a stored procedure API level and validate any input given to the stored procedure.
If you create a view and remove city from it and give them the select access to the view. Wouldn't that work?
Regards,
TA

That would be the way depending on what the owants to accomplish.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]