SQL Server vs Oracle – Conditionally OR vs CASE vs IF

oraclesql server

Lets say, I have a query,

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam

Here MyParam is parameter and optional. So, it only check MyColumn = MyParam if MyParam is not 0. But our DBA is saying OR will makes it slow and db will suffers. Another option is,

IF MyParam = 0 
SELECT * FROM MyTable WHERE MyColumn = MyParam

The problem with this approach is that we have lot of optional parameters. So, our query become very very big. Another option is CASE.

So what you guys suggest. I am talking about in general whether Oracle or SQL Server.

Best Answer

In sql-server:

One option is dynamic sql, another is option (recompile).

using option (recompile):

select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile);

dynamic sql example:

declare @sql nvarchar(max);

if nullif(@MyParam,0) is not null
  begin;
  set @sql = 'select * from MyTable where MyColumn=@MyParam;'
  exec sp_exeuctesql @sql, '@MyParam int', @MyParam;
  end;
else 
  begin;
  set @sql = 'select * from MyTable;'
  exec sp_exeuctesql @sql;
  end;

Reference:

Related Solutions

Sql-server – Updating data from a report: any potential pitfalls

Honestly, don't do it. Yes it is possible to do it by declaring a global variable to your report and then passing that to the statement that generates the result set to do some processing before returning data but it is messy and ill-advised at best. What is to say that in the future you won't want to add more variables to the report? At this point it technically becomes a form - which is what ASP.NET was designed to do - not what SSRS/ReportViewer was designed to do.

You say that you are using Report Viewer in a ASP.NET web application - so my recommendation would be to handle the print logic within the application itself if you can and leave the report to do what it does best. We have an application at our company that produces a report in a similar fashion - and we needed to track who was printing the report and when - we did this by disabling the print functionality within the report and then supplied functionality within the application to render the report to a PDF and then print it. This allowed us to be very flexible in what we track/audit and how the report was generated and where it was printed.

Also, keeping the two pieces of logic separate will be better in the long run - for instance if you need to change the report this could be done external to the application and then you just provide the rdl file and replace the old one. Putting code within the report to change data means extra testing and blurs the boundaries of responsibilities.

Additionally, as you mention above - there is the possibility that data could be compromised one way or another (SQL Injection etc..) and having the application validate the input is by far the correct thing to do - not to mention adding all the extra SQL needed to validate, update and then return data would slow your report down. This may not seem like much if you only have a few users but if your user base is large it could cause issues.

It may seem like the quickest solution right now but that is not necessarily the right solution. Keep it simple and separated and you will find you will have a lot less problems in the long run.

I hope this helps.

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10 
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name 
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;

Best Answer

Related Solutions

Sql-server – Updating data from a report: any potential pitfalls

SQL Server – IF NOT NULL Then UPDATE Else Keep Value

Related Question