Which of these works better? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Which of these works better?

Author

Topic

MegaTrain
Starting Member

16 Posts

Posted - 2003-12-15 : 12:34:02

I know both of these work, but I'd like your opinion on which is a better idea to use:
I need to return data that matches one of several possible values.

Option 1:
CREATE proc Data_getPCAWhere
@where as nvarchar(1000)=null
as
EXEC ('SELECT * FROM tblPCAs ' + @where)
GO

Here I have to construct the entire Where statement in my VB code and pass it as the parameter. Certainly the most flexible option, but slower.

Option 2:
CREATE proc Data_GetPCAsByStatus
@s0 as int
,@s1 as int=null
,@s2 as int=null
,@s3 as int=null
,@s4 as int=null
,@s5 as int=null

as
SELECT * FROM tblPCAs WHERE tblPCAs.StatusID in (@s0, @s1, @s2, @s3, @s4, @s5)
GO

Here I can pass it as many or as few values as is necessary.

Will it make a difference if I put more parameters in?
Say @s0-@s5 and @L0-@L6 for another Where...In statement.

Your suggestions are appreciated.... My SQL guy is out this week :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-15 : 12:54:14

Your first option is using dynamic sql. This is definitely not recommended and will be slower.

Option 2 is much better even though it requires more code to write.

Both option have SELECT *. You need to use an explicit column list. SELECT * will take a performance hit.

Tara

MegaTrain
Starting Member

16 Posts

Posted - 2003-12-15 : 13:01:17

Thanks.
So making it a column list is better even if it IS every field?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-15 : 13:05:50

YES!!!

You take a performance hit by using SELECT * because SQL Server has to go retrieve the column list for you. You can easily generate the column list in Query Analyzer if you are using the SQL 2000 version. Just right click on your object in QA and script it as a SELECT.

Your SQL guy should have told you about SELECT * and dynamic sql. These are very bad and should be part of some standards document that he has. There are a bunch of other things that need to done besides these.

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-12-15 : 13:07:24

Also Check out

http://www.sqlteam.com/item.asp?ItemID=11499

use the following where for even greater flexibility

where
(@CSV is null or FieldID IN (SELECT ID FROM dbo.UDFFunc(@CSV))

MegaTrain
Starting Member

16 Posts

Posted - 2003-12-15 : 13:28:28

SWEET! I didn't know you could do that in the Query Analyzer.

And let me just say, for the record, that "My SQL guy" is really "The guy that teaches the MS SQL classes but doesn't work with it much in the real world".

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2003-12-15 : 13:44:48

Then he should get his MCDBA so he knows better; or he should get a real job for awhile, so he'll have to support what he's teaching now (this option would be the funnest). :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources