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: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 08:23:44 -0400

I would use sp_executesql or exec as you have done, as long as there are no
where clause conditions 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"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

  • Re: Which is best performace wise in a stored proc - dynamic sql or big case statement ?
    ... > CREATE MyProc @iVar INT AS ... I expect there might be because I can't see how SQL Server can do ... > anything other than re-compile @sSQL everytime it runs. ...
    (microsoft.public.sqlserver.server)
  • Re: Reading Query Statements from SQL Server
    ... > it and then rewrite it back to the SQL Server under a different name? ... Dim oCon As ADODB.Connection ... Dim sSql As String ... Set oRS = New ADODB.Recordset ...
    (microsoft.public.vb.database)
  • Re: Current_user
    ... Dim sSQL As String ... Dim Usr As String ... >> in sql server, but I don't know how to link this table ...
    (microsoft.public.access.adp.sqlserver)
  • RE: call order
    ... CREATE PROCEDURE MyProc ... CREATE VIEW MyProc AS SELECT * FROM SYSOBJECTS ... > is there a particular call sequence that SQL SERVER uses - ...
    (microsoft.public.sqlserver.programming)
  • ADOCommand
    ... My-Server is a sql server 2000 server. ... MyDB is my active database. ... MyProc is a storedprocedure that have an output parameter. ...
    (borland.public.delphi.database.ado)