Re: is this bad?

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

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 15:12:01 +0100

Ops, my apologies. I didn't see that you already looked at that article. The thing about the CASE
solution is that it might not produce the best execution plan for a given query. But with dynamic
SQL, you risk SQL injection (etc). I would go for the CASE solution if you find the performance is
satisfactory. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@nowhere.com> wrote in message news:ePVvvUOwEHA.3880@TK2MSFTNGP10.phx.gbl...
> understood. Note that the link you provided is the same link I put in my
> orginal post. Thats where I got the solution I'm using now. But after
> someone told me that any DBA would not be very happy with it I decided to
> post here to see if that was true.. and if so, why?
>
> thanks for the reply though. I do appreciate it.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
> message news:uPr0tLOwEHA.1300@TK2MSFTNGP14.phx.gbl...
> > There's seldom a "right" or "wrong" way of doing something. With enough
> information, you will be
> > able to make an informed decision. This might be a good place to start:
> > http://www.aspfaq.com/2501
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "djc" <noone@nowhere.com> wrote in message
> news:OhTZy6NwEHA.1512@TK2MSFTNGP12.phx.gbl...
> > > I got this solution from here: http://www.aspfaq.com/show.asp?id=2501
> > > I was told the "Gods" of SQL are found here (not the link above, but
> here
> > > here) and could help. Anothers input in a dotnet forum said something to
> the
> > > effect the any DBA would be very unhappy with this.
> > >
> > > I am using a stored procedure to retrieve data for an asp.net datagrid
> that
> > > needs to be sortable and pageable. This means I need to pass in 2
> variables,
> > > what column to sort by and whether its ASC or DESC. Here is what I have
> now
> > > and it seems to work fine. Note I have JUST started to try to transition
> > > from putting SQL directly in my code to using stored procedures so I
> REALLY
> > > don't know what I'm doing yet!
> > >
> > > CREATE PROCEDURE dbo.GetAllIssues
> > > (
> > >     @SortBy VarChar(50), @Asc_Desc VarChar(8)
> > > )
> > > AS
> > >
> > >     SELECT MainID, Title, ShortDesc, EstimatedDeliveryDate, Status
> > >     FROM tblMain
> > >     ORDER BY
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'desc' THEN
> > >             CASE @SortBy
> > >                 WHEN 'MainID' THEN MainID
> > >             END
> > >         END
> > >         Desc,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'desc' THEN
> > >         CASE @SortBy
> > >             WHEN 'Title' THEN Title
> > >             END
> > >         END
> > >         DESC,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'desc' THEN
> > >         CASE @SortBy
> > >             WHEN 'EstimatedDeliveryDate' THEN EstimatedDeliveryDate
> > >             END
> > >         END
> > >         DESC,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'desc' THEN
> > >         CASE @SortBy
> > >             WHEN 'Status' THEN Status
> > >             END
> > >         END
> > >         DESC,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'asc' THEN
> > >             CASE @SortBy
> > >                 WHEN 'MainID' THEN MainID
> > >             END
> > >         END,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'asc' THEN
> > >         CASE @SortBy
> > >             WHEN 'Title' THEN Title
> > >             END
> > >         END,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'asc' THEN
> > >         CASE @SortBy
> > >             WHEN 'EstimatedDeliveryDate' THEN EstimatedDeliveryDate
> > >             END
> > >         END,
> > >
> > >     CASE @Asc_Desc
> > >         WHEN 'asc' THEN
> > >         CASE @SortBy
> > >             WHEN 'Status' THEN Status
> > >             END
> > >         END
> > > GO
> > >
> > > what do you think?
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: is this bad?
    ... orginal post. ... > able to make an informed decision. ... > Tibor Karaszi, SQL Server MVP ... >> I am using a stored procedure to retrieve data for an asp.net datagrid ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure to drop and create table for end user
    ... >>> such) so the only thing the end user has access to is execute permission ... >>> back with select permission not granted on a table the stored procedure ... >> Probably because you are using dynamic SQL. ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Assertion: SQL Server 2000 cant issue a SOAP call
    ... Calling it directly might not be supported, but there's no reason a remoting ... > It might be worth mentioning that calling CLR code from SQL Server is not ... > Tibor Karaszi, SQL Server MVP ... >> from a stored procedure, or from an extended stored procedure, but I ...
    (microsoft.public.sqlserver.programming)
  • Re: Createobject of (null) caused exception C0000005
    ... Aaron [SQL Server MVP] wrote: ... >> coming from my stored procedure. ... with more or less effort or performance impact in the database tier. ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
  • Re: 900 byte limit on stored procedure parameter?
    ... Please post the stored procedure and the full CREATE TABLE definition - ... SQL Server MVP ... http://sqlserverfaq.com - free video tutorials ... If I pass 901 characters I ...
    (comp.databases.ms-sqlserver)