Re: Which is best performace wise in a stored proc - dynamic sql or big case statement ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Uri Dimant (urid_at_iscar.co.il)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 14:52:32 +0200

Steve
What is about to use IF ...ELSE ...
IF @var= 1
SELECT * FROM Table1
ELSE
SELECT * FROM Table2

PS.
How many tables do you have to check out? Do you have some WHERE conditions
for these SELECTS,don't you?

"Steve W" <lsl@btconnect.com> wrote in message
news:%23gVE2chTEHA.544@TK2MSFTNGP11.phx.gbl...
> I want to be able to pass a variable to my stored proc and run the same
sql
> select statement against a different table based on the variable.
>
> It seems to me I could do this via (a) a case statment or (b) dynamic sql.
>
> So for (a) I would have something like :
>
> CREATE MyProc @iVar INT AS
> CASE
> WHEN @iVar = 1 THEN SELECT aCol FROM Table1
> WHEN @iVar = 2 THEN SELECT aCol FROM Table2
> : :
> : :
> WHEN @iVar = N THEN SELECT aCol FROM TableN
> END
>
>
> for (b) I would have something like :
>
> CREATE MyProc @iVar INT AS
> DECLARE @sSQL NVARCHAR(100)
> SET @sSQL = 'SELECT aCol FROM Table' + CAST(@iVar AS NVARCHAR(10))
> sp_executesql @sSQL
>
>
> Which would be best from (purely) a performance point of view ? Would
there
> be a major difference between them ? Would the gap bewteen them narrow
or
> widen as the number of possible variables increased ? (I'm expecting
around
> 100 possible values).
>
> I appreciate that there is a maintenace issue with (a) and not with (b),
> and also that @iVar would be more sensibly a NVARCHAR for (b). So I will
go
> for (b) unless (as I expect there might be) there will be a big
performance
> hit. I expect there might be because I can't see how SQL Server can do
> anything other than re-compile @sSQL everytime it runs. If that is the
case,
> would it be sensible not to use a stored proc and just fire the select
> statement at the database from my app ?
>
> Thanks,
>
> Steve
>
>

Relevant Pages