Dynamic SQL is bad?

Question

Dynamic SQL is bad?

Brenna Duncan-211418

Right there with Babe

Points: 777
More actions
October 4, 2005 at 7:52 pm

#90199

I keep hearing that it is bad to use dynamic sql in a stored procedure, like this...
CREATE sp_StoredProcedure
@join AS char(1)
AS
DECLARE @strsql AS varchar(200)
     SET @strsql = ''
     SET @strsql = @strsql + 'SELECT * FROM Customer C'

     IF @join = 'I'
           SET @strsql = @strsql + ' INNER'
     ELSE
          SET @strsql = @strsql + ' LEFT'
     SET @strsql = @strsql + ' JOIN Order O ON C.CustomerID=O.CustomerID'
     EXEC(@strsql)
But what if I need to dynamically join to a different table or perform an inner join instead of an outer join based on a parameter fed to a stored procedure? Is there a better way to do this? I like to do this so I can use a single Crystal report for a wide variety of data types.

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

The two main points against dynamic SQL are,

1. Performance - this procedure would be going through constant recompiles while the Query Optimizer works out which tables it's dealing with and which indexes it should be using.

2. Security - once you start referencing tables like this you'll need to specifically grant access to the tables themselves instead of just granting execute permission to a stored procedure.

Also, as a side note, I do hope you're not planning to have SELECT * in your procedure. This is also bad practice, some would even say it's a tool of the lazy and shortsighted,

- it can lead to problems in the client app if it's not designed to handle new columns gracefully.

- network guys will hate it because you're most likely dragging more information than you need across the network.

- it can lead to blocking issues if a user is running an UPDATE against the same table to update and unindexed field.

--------------------
Colt 45 - the original point and click interface

TimS Ten Centuries Points: 1165 More actions · Answer 2

It is bad because of SQL Injection and because of the permissions issues.

When using "dynamic sql" the user must have rights to both the sproc and the tables invovled.

The above can be re-written as LEFT JOIN like below

SELECT *

FROM Customer C

LEFT JOIN Order O ON C.CustomerID=O.CustomerID

WHERE @join 'I' OR O.CustomerID IS NOT NULL

Note: You should not use SELECT * like this in production code.

Tim S

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

<href="Curses'>http://www.sommarskog.se/dynamic_sql.html">Curses & Blessings of Dynamic SQL

man - I can never get this 'simple' html to work....

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

farrell - use it like so...& "view page source" for use of the anchor tags...

Curses & Blessings of Dynamic SQL

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Thanks.

(you got a good moniker too...)

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

I noticed the sucess with links in the monty python thread...congratulations!

as for the moniker...it's almost like christening yourself...

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Now there's a picture. A "committed" person "christening" themselves... Are you able to pop the champagne bottle with the nice tight white jacket wrapped around you?

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

I think you must have me confused with remi...he should be the one in the jacket...

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 9

I figured Remi's was just simply looser as he can type faster...

I wasn't born stupid - I had to study.