Need help with a Query

Question

Post reply

Need help with a Query

itmasterw 60042

SSCrazy

Points: 2858
More actions
November 19, 2019 at 4:14 pm

#3698882
Hi,
Below I have code that takes in 3 parameters.
Now, sometimes, as you can imagine one of these can have a value, or only two, or all three may be populated. I am trying to find a way to write the code below so that this will work, but so far it has not.
Either I get nothing when I should get something or too much.
I can fix this with if statements to cover the different possibilities, but I would have to write 24 SQL statements.
Is there a way to handle this in one?
Thank you
```
DECLARE @Eid varchar(30) = 'MISC',
@portfolio_id varchar(50) = 'CHECKMATE',
@purchased_from  varchar(50) =NULL

INSERT [Purchases_Worksheet]
Select * 
FROM Purchases
Where (([purchased_from] LIKE @purchased_from +'%') OR [purchased_from] IS NULL)
AND(( [portfolio_id] LIKE @portfolio_id +'%') OR [portfolio_id] IS NULL)
AND(([entity_id] LIKE @Eid + '%') OR [entity_id] IS NULL)
```

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

The codes look ok. Something else must be causing your issue.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Thanks for the reply, bu this is all there is, and it is not working.

Thank you

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

I'm not sure what the parameter values mean, but I think you might have meant to check if the parameter is null not the column?

SELECT *
  FROM Purchases
 WHERE ([purchased_from] LIKE @purchased_from + '%' 
            OR @purchased_from IS NULL)
   AND ([portfolio_id] LIKE @portfolio_id + '%' 
            OR @portfolio_id IS NULL)
   AND ([entity_id] LIKE @Eid + '%' 
            OR @Eid IS NULL)

or maybe this:

SELECT *
  FROM Purchases
 WHERE ([purchased_from] LIKE @purchased_from + '%' 
             OR (@purchased_from IS NULL AND [purchased_from] IS NULL))
   AND ([portfolio_id] LIKE @portfolio_id + '%' 
             OR (@portfolio_id IS NULL AND [portfolio_id] IS NULL))
   AND ([entity_id] LIKE @Eid + '%' 
             OR (@Eid IS NULL AND [entity_id] IS NULL))

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

itmasterw 60042

SSCrazy

Points: 2858

November 19, 2019 at 6:10 pm

#3698929

Okay, I got it to work.

Thanks

DesNorton SSC-Insane Points: 24278 More actions · Answer 5

This is a classic Catch-All query.

If you check your execution plan, you will note that you have all table/index scans and no seeks. That is due to the fact that for this pattern, SQL needs to compare EVERY row in the table/index to the "@purchased_from IS NULL" to see if it meets the criteria for the filter. You would most likely get better performance by using dynamic SQL to build up your WHERE clause.

Because SQL creates and re-uses a plan based on the 1st set of parameters that it receives, later calls with different parameters may end up with an inappropriate plan for the parameters. This can be overcome with OPTION RECOMPILE.

How to post data/code on a forum to get the best help.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 6

DesNorton wrote:

This is a classic Catch-All query.
<li style="list-style-type: none;">
If you check your execution plan, you will note that you have all table/index scans and no seeks. That is due to the fact that for this pattern, SQL needs to compare EVERY row in the table/index to the "@purchased_from IS NULL" to see if it meets the criteria for the filter. You would most likely get better performance by using dynamic SQL to build up your WHERE clause.
<li style="list-style-type: none;">
Because SQL creates and re-uses a plan based on the 1st set of parameters that it receives, later calls with different parameters may end up with an inappropriate plan for the parameters. This can be overcome with OPTION RECOMPILE.

Another hint to try is adding at the end of the SQL that might help with performance is:

OPTION (OPTIMIZE FOR (@Eid UNKNOWN, @portfolio_id UNKNOWN,  @purchased_from UNKNOWN))

DesNorton SSC-Insane Points: 24278 More actions · Answer 7

I suppose a code sample for the Dynamic SQL might help

DECLARE
    @Eid             varchar(30) = 'MISC'
  , @portfolio_id    varchar(50) = 'CHECKMATE'
  , @purchased_from  varchar(50) = NULL;

DECLARE
    @SqlWhere  nvarchar(1000)
  , @SqlCmd    nvarchar(4000);


IF ( @Eid IS NOT NULL )
BEGIN
  SET @SqlWhere = ISNULL(@SqlWhere + N' AND ', ' WHERE ') + N'[entity_id] LIKE @Eid + ''%'' '
END;

IF ( @portfolio_id IS NOT NULL )
BEGIN
  SET @SqlWhere = ISNULL(@SqlWhere + N' AND ', ' WHERE ') + N'[portfolio_id] LIKE @portfolio_id + ''%'' '
END;

IF ( @purchased_from IS NOT NULL )
BEGIN
  SET @SqlWhere = ISNULL(@SqlWhere + N' AND ', ' WHERE ') + N'[purchased_from] LIKE @purchased_from + ''%'' '
END;

SET @SqlCmd    = N'INSERT INTO [Purchases_Worksheet] ( [purchased_from], [portfolio_id], [entity_id] ) '
               + N'SELECT [purchased_from], [portfolio_id], [entity_id] FROM Purchases'
               + ISNULL(@SqlWhere + N';', N';');

EXEC sys.sp_executesql @stmt           = @SqlCmd
                     , @params         = N'@Eid varchar(30), @portfolio_id varchar(50), @purchased_from varchar(50)'
                     , @Eid            = @Eid
                     , @portfolio_id   = @portfolio_id
                     , @purchased_from = @purchased_from;

How to post data/code on a forum to get the best help.

paulo.margarido Ten Centuries Points: 1245 More actions · Answer 8

does this work for you?

DECLARE @Eidvarchar(30) = 'MISC'
, @portfolio_idvarchar(50) = 'CHECKMATE'
, @purchased_fromvarchar(50) = NULL

INSERT[Purchases_Worksheet]
Select* 
FROMPurchases
Where(@purchased_from IS NULL OR ([purchased_from] LIKE @purchased_from + '%'))
AND(@portfolio_id   IS NULL OR ([portfolio_id]   LIKE @portfolio_id   + '%'))
AND(@Eid         IS NULL OR ([entity_id]      LIKE @Eid            + '%'))