Best Practice

Question

Best Practice

Chaz-265757

Old Hand

Points: 333
More actions
July 7, 2006 at 3:09 pm

#153079

Hi to all

Best practice, which of the following would you say is best to use.

This is inside of a store procedure

Wrapping a SQL statement inside of an exec or just writing the statement as is.

exec('
          SELECT     *
             FROM tableName
          WHERE FieldName = ''' + @FieldValue + '''
        ')

or

SELECT *
   FROM tableName
WHERE FieldName = @FieldValue

Is their any performance issue with one over the other?

I will use the exec only when my table is dynamic.

We are trying to put some standards together and this is one of our biggest debates.

Thanks
Kindest Regards,
Chaz

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

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

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Certainly the exec option should only be used if necessary. Having the SQL "in-line" in the stored procedure enables it to be checked and precompiled and ready to run. Dynamic SQL cannot be prepared and optimized by the query engine until it is run, and then it must be done every time it is run; there is no save prepared query for subsequent executions.

This site has a long list of articles on the problems of Dynamic SQL. A search will give you plenty of ammunition.

Hope this helps

Mark

David.Poole SSC Guru Points: 75992 More actions · Answer 2

Best practice would be to

Be explicit in your field names
qualify your object with its owner

If you truly need dynamic SQL look at sp_executesql rather than exec. sp_executesql will cache the execution plan and if the query is called again with the same arguments the plan will be reused.

In the example you give I would have each SELECT carried out by child procedures with one master procedure to decide which one to call.

CREATE PROC dbo.MasterRetrieval
@FieldValue VARCHAR(50) ,
@EnumValue INT=0
AS
SET NOCOUNT ON

DECLARE @PROCNAME SYSNAME
DECLARE @SQL VARCHAR(4000)

SELECT @PROCNAME=CASE @EnumValue 
WHEN 0 THEN 'exec dbo.ChildProc1'
WHEN 1 THEN 'exec dbo.ChildProc2'
...etc
ELSE 'exec dbo.ChildProc99' END

SET @SQL = @PROCNAME + '''' + @FieldValue  + ''''
EXEC (@SQL )

What this achieves is that your child procs are simple compiled and cached procedures (at least after they are run for the first time after a reboot).

Your master procedure is dynamic and will recompile every time, however as its sole purpose is to trigger a child proc that actually does the data retrieval this is still efficient.

Of course you need some code to make sure that the @FieldValue argument cannot be used for injection attacks.

LinkedIn Profile

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Agreed. Curses & Blessings of Dynamic SQL

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

Chaz-265757 Old Hand Points: 333 More actions · Answer 4

Thanks to all for your replies…

Kindest Regards,

Chaz