November 19, 2019 at 4:14 pm
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)
November 19, 2019 at 5:23 pm
The codes look ok. Something else must be causing your issue.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 19, 2019 at 5:59 pm
Thanks for the reply, bu this is all there is, and it is not working.
Thank you
November 19, 2019 at 6:02 pm
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))
November 19, 2019 at 6:10 pm
Okay, I got it to work.
Thanks
November 19, 2019 at 6:47 pm
This is a classic Catch-All query.
November 19, 2019 at 7:56 pm
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))
November 19, 2019 at 9:07 pm
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;
November 20, 2019 at 3:41 pm
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 + '%'))
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply