Query Data without Dynamic SQL

Question

Query Data without Dynamic SQL

Goldie Lesser

SSCertifiable

Points: 6155
More actions
June 26, 2008 at 11:00 am

#381378

Hi.
If I have a table which stores column descriptions. I need to query other tables based on the information in this table.
The defined as follows:
DECLARE @Sample TABLE
(
ID INT IDENTITY(1,1)
,TableName VARCHAR(50)
,ColumnName VARCHAR(50)
,DisplayName VARCHAR(50)
)
INSERT INTO @Sample
SELECT 'User', 'Field1', 'Green' UNION ALL
SELECT 'User', 'Field2', 'Purple' UNION ALL
SELECT 'User', 'Field3', 'Yellow' UNION ALL
SELECT 'User', 'Field4', 'Orange'
Now I need to write a query like 'SELECT Field1 AS 'Green',Field2 AS 'Purple',Field3 AS 'Yellow',Field4 AS 'Orange' FROM dbo.User'.
I know how to do this with dynamic SQL, but I was wondering if there would be a way to do it without using dynamic SQL?
Or is dynamic SQL the way to go?
Here's what I have in dynamic SQL in case it's any help.
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT '
SELECT @sql = @sql + (ColumnName + ' AS ''' + DisplayName + ''',')
FROM @Sample
WHERE TableName = 'User'
SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) + ' FROM dbo.User'
exec sp_executesql @sql

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

It sounds like someone came up with the brillant* idea of storing meta-data for other tables in a "master table".

If you're stuck with that database, you're probably stuck with dynamic SQL. You're probably also stuck with a lot of other problems.

The real solution is most likely to rebuild the database completely.

*: regular readers of TheDailyWTF.com will realize that this wasn't a typo on my part. Everyone else can just take it on faith that it's an outside joke: You had to not be there in order to get it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 2

The example I gave is a modified example of what we are doing.

I thought the actual example would take too long to explain.

Does anyone have any suggestions of how to do this without dynamic SQL?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

ggraber (6/26/2008)
The example I gave is a modified example of what we are doing.
I thought the actual example would take too long to explain.
Does anyone have any suggestions of how to do this without dynamic SQL?

You have to use Dynamic SQL to do this (or some 'trick' that turns out to be dynamic SQL anyway).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 4

This kind of "trick" is to make forming GUI code (inherently dynamic) easier. It's a trick to try to make the GUI table driven so someone doesn't have to recompile GUI code when someone wants to make a slight DB change. Also makes all the GUI code universal in it's calls.

I haven't had to work with it so haven't been able to see what it actually does to a database and, as a result, can't say whether it's good or bad for the GUI... but it's a stinker to write stored procs against... as you're finding out, everything is going to need to be dynamic SQL. Of course, with VARCHAR(MAX) that's no sweat... except maybe for the recompiles.

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

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

Jeff Moden (6/26/2008)
I haven't had to work with it so haven't been able to see what it actually does to a database and, as a result, can't say whether it's good or bad for the GUI...

I have. 🙁 My current monster system does this.

It's not that bad an idea, but it does have a number of implications for SQL. Most or all of your procs will need some dynamic SQL, making them harder to write and requiring more permissions on the base tables than is usually recommended.

There will be overhead in every call to the DB caused by the need to retrieve the meta data from the tables and assemble select statements. If you're doing permissions checks based on meta data, it gets even worse.

You will have lots of unparameterised ad-hoc calls to the DB (dynamic SQL). As such, if you have a large 64-bit box, you are at risk of procedure cache bloat and token cache bloat[/url]. Neither is fun.

You will have to put strong controls around the meta data tables, because one bad update statement can bring your entire system down.

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

GilaMonster (6/27/2008)
Jeff Moden (6/26/2008)
I haven't had to work with it so haven't been able to see what it actually does to a database and, as a result, can't say whether it's good or bad for the GUI...
I have. 🙁 My current monster system does this.
It's not that bad an idea, but it does have a number of implications for SQL. Most or all of your procs will need some dynamic SQL, making them harder to write and requiring more permissions on the base tables than is usually recommended.
There will be overhead in every call to the DB caused by the need to retrieve the meta data from the tables and assemble select statements. If you're doing permissions checks based on meta data, it gets even worse.
You will have lots of unparameterised ad-hoc calls to the DB (dynamic SQL). As such, if you have a large 64-bit box, you are at risk of procedure cache bloat and token cache bloat[/url]. Neither is fun.
You will have to put strong controls around the meta data tables, because one bad update statement can bring your entire system down.

All ya gotta do now is store all of the data in a single monster EAV and you'd have it made. 😛

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

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

Jeff Moden (6/27/2008)
All ya gotta do now is store all of the data in a single monster EAV and you'd have it made. 😛

Fortunatly I don't think anyone here's quite that stupid. I hope.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

GilaMonster (6/27/2008)
Jeff Moden (6/27/2008)
All ya gotta do now is store all of the data in a single monster EAV and you'd have it made. 😛
<Shudder>
Fortunatly I don't think anyone here's quite that stupid. I hope.

Or they just haven't thought of it yet... :w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

rbarryyoung (6/27/2008)
GilaMonster (6/27/2008)
Jeff Moden (6/27/2008)
All ya gotta do now is store all of the data in a single monster EAV and you'd have it made. 😛
<Shudder>
Fortunatly I don't think anyone here's quite that stupid. I hope.
Or they jsut haven't thought of it yet... :w00t:

Or...they spent three years disentangling one such disaster.... I still get a blinding flash of pain when I see that acronym.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?