CODEWITHSUNDEEP
sql-server-2008if-statement
JohnZ
JohnZ

Reputation: 482

Something better for this situation than using IF?

I am writing queries that are used on a website. When the query is needed it gets directly parsed to the server and the outcome is all controlled by the query. The website just returns the table. What I do have on the site is checkboxes that the client can select and those get parsed into the query as either @var = 1 or @var = 0. Because of that, right now I have this code to check and add or not depending if it is checked. My question is, is there a better way to go about this than using the IF statement like this as I have several sections of the code that include this:

SET @sql = 'select distinct '
If @mgchk = 1 
    SET @sql = @sql + 'p.MainGroup'
If @sgchk = 1 and @sql = 'select distinct '
    SET @sql = @sql + 'p.SubGroup'
If @sgchk = 1 and @sql not like '%SubGroup'
    SET @sql = @sql + ',p.SubGroup'
If @ssgchk = 1 and @sql = 'select distinct '
    SET @sql = @sql + 'p.SubSubGroup'
If @ssgchk = 1 and @sql not like '%SubSubGroup'
    SET @sql = @sql + ',p.SubSubGroup'
If @Seasonchk = 1 and @sql = 'select distinct '
    SET @sql = @sql + 'p.Season'
If @Seasonchk = 1 and @sql not like '%Season'
    SET @sql = @sql + ',p.Season'
If @vendorchk = 1 and @sql = 'select distinct '
    SET @sql = @sql + 'p.VendorID'
If @vendorchk = 1 and @sql not like '%VendorID'
    SET @sql = @sql + ',p.VendorID'

SET @sql = 
    @sql + 
    ' into 
        ##aa 
    from 
        RPProducts p, 
        RPIv i, 
        RPTrsd d, 
        RPTrs s 
    WHERE 
    s.StoreID = d.StoreID and 
        s.ReceiptNO = d.ReceiptNO and 
        i.UPC = d.UPC and 
        i.StoreID = d.StoreID and 
        i.IVProduct = p.Productid and 
        s.TRSdate >= '''+ convert(varchar(10), @trsfrom, 101) +''' and 
        s.TRSdate <= '''+ convert(varchar(10), @trsto, 101) +''''
execute sp_executesql @sql

@mgchk / @sgchk / @ssgchk / @seasonchk / @vendorchk are the checkboxes variables

To answer @Aaron,

Global temp because of the dynamic queries. The whole query gets processed and drop right away when the data is pulled. No clash will happen there.

My date variables are datetime and it gives me an error within dynamic SQL.

Yes, recalling the same thing over to check, which is the reason for this whole question, if there is something better to use than the IF checking.

And I find using alias joins easier...

Upvotes: 0

Views: 41

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

-- rather than convert to a dangerously formatted string,
-- here is a much better way to strip time from a datetime
-- (if you need to!)

SET @trsfrom = DATEADD(DAY, DATEDIFF(DAY, 0, @trsfrom), 0);
SET @trsto = DATEADD(DAY, DATEDIFF(DAY, 0, @trsto), 0);

DECLARE @sql NVARCHAR(MAX) = N'SELECT DISTINCT ';

-- here's an easier way to strip the first comma:

SET @sql += SUBSTRING(
    CASE WHEN @mgchk     = 1 THEN ',p.MainGroup'   ELSE '' END
  + CASE WHEN @sgchk     = 1 THEN ',p.SubGroup'    ELSE '' END
  + CASE WHEN @ssgchk    = 1 THEN ',p.SubSubGroup' ELSE '' END
  + CASE WHEN @Seasonchk = 1 THEN ',p.Season'      ELSE '' END
  + CASE WHEN @vendorchk = 1 THEN ',p.VendorID'    ELSE '' END, 2, 2000);

SET @sql += ' INTO ##aa 
    FROM
      dbo.RPProducts AS p -- use schema prefix!
    INNER JOIN dbo.RPIv AS i  -- use PROPER JOINS!
      ON i.IVProduct = p.Productid
    INNER JOIN dbo.RPTrsd AS d 
      ON i.UPC = d.UPC 
      AND i.StoreID = d.StoreID
    INNER JOIN dbo.RPTrs AS s 
      ON s.StoreID = d.StoreID 
      AND s.ReceiptNO = d.ReceiptNO
    WHERE s.TRSdate >= @trsfrom -- use strongly typed parameters!
    AND s.TRSdate <= @trsto;';

EXEC sp_executesql @sql, 
    N'@trsfrom DATETIME, @trsto DATETIME', 
    @trsfrom, @trsto;
----^^^^^^^^^^^^^^^^ here is how the query gets the @trsfrom & @trsto values

I still think your use of a ##global temp table is quite dangerous. If two people run this code at the same time, they are going to have serious problems.

Upvotes: 4

Related Questions