Select from table where ids are not in list of ids

Question

I'm trying to select from a table where the primary_no (VARCHAR(20)) does not include any of the strings in the @IDS variable, but it's not working and I have tried using a CTE, not exists and not in. Neither of them work. The query still selects all of the data even those with the primary_no that are in the @IDS variable.

ALTER PROCEDURE [dbo].[LinkProc] @IDS VARCHAR(MAX) /* DECLARE @IDS VARCHAR(MAX) SET @IDS = ' ''00000447'', ''0000300'', ''2900071'', ''2900192'' ' EXEC LinkProc @IDS = @IDS */ AS WITH cte (id) AS ( SELECT * FROM dbo.splitstring(@IDS) ) SELECT * FROM link_tb WHERE grp_id = 4 AND status_cd = 'A' AND primary_no NOT IN (SELECT id FROM cte)

I have also tried this with no luck:

SELECT * FROM link_tb lt WHERE grp_id = 4 AND status_cd = 'A' AND NOT EXISTS (SELECT id FROM cte c WHERE lt.primary_no = c.id)

The result set from calling (SELECT id FROM cte):

'00000447' '0000300' '2900071' '2900192'

I found a solution to this problem, I answered below.

We have created this splitstring function manually. So when I call this (SELECT * FROM dbo.splitstring(@IDS) it does give me a list of the split strings. — hypnagogia
– hypnagogia, Commented Oct 14, 2019 at 17:26

Thom A · Accepted Answer · 2019-10-15 11:22:18Z

Disclaimer: Turns out, from the chat, that the OP is not using 2014, but 2005 (which is completely unsupported and has been for years). As a result the answer using a table type parameter will not work because the functionality does not exist.

I have left the answer there, however, for future users who have a similar question.

Instead of using a delimited list, use a table-type parameter

CREATE TYPE dbo.PrimaryList AS TABLE (primary_no varchar(20) NOT NULL); GO ALTER PROC dbo.LinkProc @IDs dbo.PrimaryList READONLY AS BEGIN SELECT * FROM dbo.link_tb l LEFT JOIN @IDs I ON l.primary_no = I.primary_no WHERE grp_id = 4 AND status_cd = 'A' AND I.primary_no IS NULL; END; GO

You can then call the SP as so:

DECLARE @IDs dbo.PrimaryList; INSERT INTO @IDs VALUES('00000447'), ('0000300'), ('2900071'), ('2900192'); EXEC dbo.LinkProc @IDs;

Edit: As for why what you have isn't working, it's because you're quote wrapping your values. What you're doing is the equivlent of:

EXEC dbo.LinkProc @IDs = '''00000447''';

The value of primary_no isn't going to be '00000447' it's just going to be 00000447. If you have to pass a delimited list (which I suggest against, and I wouldn't be surprised if your function using a WHILE, and if it does you need to remove that), then don't quote the values:

EXEC dbo.LinkProc @IDs = '00000447,0000300,2900071,2900192';

The @IDS I'm passing in are coming from another table and the list of the IDS can be very long. Is there a way to do this without hard-coding the values?
Yes, use an INSERT INTO...SELECT...FROM statement @hypnagogia , instead of an INSERT INTO...VALUES... statement
If you can have a very long list, then using an table-value parameter is an even better idea, @hypnagogia . The performance of a statement using IN can degrade with a (very) long list.
I'm getting errors trying to implement your solution. I may be writing it incorrectly. But for the INSERT INTO...SELECT...FROM you mentioned, I won't be selecting from a table within the query, I will be sending in the list of @IDS as a parameter.
That's what i'm doing above, @hypnagogia : EXEC dbo.LinkProc @IDs;

hypnagogia · Accepted Answer · 2019-10-18 23:35:06Z

This was the solution to my problem. The IDS were parsed into a table and I selected from there. My main issue was I had set the @IDS with incorrect formatting, there were line breaks in the string. Upon putting them together with no spaces it worked.

ALTER PROCEDURE [dbo].[LinkProc] @IDS VARCHAR(MAX) /* DECLARE @IDS VARCHAR(MAX) SET @IDS = '00000447,0000300,2900071,2900192' EXEC LinkProc @IDS = @IDS */ AS SELECT * FROM link_tb WHERE grp_id = 4 AND status_cd = 'A' AND primary_no NOT IN (SELECT param_value FROM dbo.PARSE_PARAM_LIST(@IDS, ','))

Collectives™ on Stack Overflow

Select from table where ids are not in list of ids

2 Answers 2

12 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Related