CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
hypnagogia
hypnagogia

Reputation: 173

Select from table where ids are not in list of ids

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.

Upvotes: 0

Views: 2498

Answers (2)

hypnagogia
hypnagogia

Reputation: 173

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, ','))

Upvotes: 0

Thom A
Thom A

Reputation: 95658

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';

Upvotes: 2

Related Questions