Avatar of Aleks
AleksFlag for United States of America asked on

Stored Procedure to select results IN ( )

I have a stored procedure, it will get one Integer parameter and the other will be a comma delimited parameter with numbers in them, such as :  1,34,35,3

I need it to return all results in which the id is any of those numbers. This is what I have:

USE [BlueDot];
GO

/****** Object:  StoredProcedure [dbo].[report_basic_employer_01_results]    Script Date: 12/29/2015 14:13:02 ******/
SET ANSI_NULLS OFF;
GO

SET QUOTED_IDENTIFIER OFF;
GO




ALTER PROCEDURE [dbo].[report_basic_employer_01_results] @Firmid INT,
    @Empids INT
AS
    SELECT  c.Id ,
            c.CaseId ,
            c.EmpId ,
            c.AlienId ,
            c.FirmAddressId ,
            c.ExpDate ,
            d.FirstNm ,
            d.MiddleNm ,
            d.LastNm ,
            d.Email ,
            d.NiStatus ,
            d.ExpiresOn ,
            c.FirmId ,
            c.MainCase ,
            d.NIVMaxStatus ,
            b.MaidenNm ,
            e.Processcatalog ,
            c.Archived ,
            f.Filedon ,
            f.Approvedon ,
            f.Validtodate
    FROM    Cases c
            LEFT JOIN Users AS d ON c.AlienId = d.UserId
            INNER JOIN Processcatalog AS e ON c.Process = e.ProcesscatalogID
            INNER JOIN Users AS b ON c.EmpId = b.UserId
            LEFT JOIN Activities_Misc AS f ON f.act_misc_id = c.Defaultrecpt
    WHERE   c.FirmId = @Firmid
            AND c.EmpId IN (@Empids)
            AND c.Archived = 0
    ORDER BY d.LastNm ,
            c.CaseId;

Open in new window


1. Question:  Is the query syntax correct for the :              AND c.EmpId IN (@Empid)
2. Is the variable @Empids  also an INT ? or should I have it a a varchar ?

Help is appreciated. I am using MS SQL 2008
Web DevelopmentMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Aleks

8/22/2022 - Mon
zephyr_hex (Megan)

1. I don't think this will work because you'll have a data type conversion error.  Instead, try this:

 WHERE c.EmpId IN (select * from dbo.split(@Empid,','))

Open in new window


2.  It should be varchar, and large enough to hold the largest possible string (perhaps varchar(max))
ASKER
Aleks

I'll give it a try
Brian Crowe

ALTER PROCEDURE [dbo].[report_basic_employer_01_results] @Firmid INT,
    @Empids INT
AS
    SELECT  c.Id ,
            c.CaseId ,
            c.EmpId ,
            c.AlienId ,
            c.FirmAddressId ,
            c.ExpDate ,
            d.FirstNm ,
            d.MiddleNm ,
            d.LastNm ,
            d.Email ,
            d.NiStatus ,
            d.ExpiresOn ,
            c.FirmId ,
            c.MainCase ,
            d.NIVMaxStatus ,
            b.MaidenNm ,
            e.Processcatalog ,
            c.Archived ,
            f.Filedon ,
            f.Approvedon ,
            f.Validtodate
    FROM    Cases c
			INNER JOIN dbo.SplitInts(@Empids, ',') AS S ON c.EmpId = S.Item
            LEFT JOIN Users AS d ON c.AlienId = d.UserId
            INNER JOIN Processcatalog AS e ON c.Process = e.ProcesscatalogID
            INNER JOIN Users AS b ON c.EmpId = b.UserId
            LEFT JOIN Activities_Misc AS f ON f.act_misc_id = c.Defaultrecpt
    WHERE   c.FirmId = @Firmid
            AND c.Archived = 0
    ORDER BY d.LastNm ,
            c.CaseId;

Open in new window


Here is a Split function if you don't have one already:

CREATE FUNCTION dbo.SplitInts
(
	@List       VARCHAR(MAX),
	@Delimiter  CHAR(1)
)
RETURNS @Items TABLE (Position INT IDENTITY(1,1), Item INT)
AS
BEGIN
   DECLARE @Len INT = LEN(@List) + 1;

   WITH cteInts AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @Len)-1)))
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@List, [end] + 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + 1), 0), @Len)-[end]-1)))
       FROM cteInts
       WHERE [end] < @len
   )
   INSERT @Items SELECT [value]
   FROM cteInts
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   RETURN;
END

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Aleks

That seems much more complicated.  About to test the first solution.
ASKER
Aleks

Zephir ... I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.split'.

/bluedot/Intranet/reports/basic_employers_01_results.asp, line 35
ASKER
Aleks

Brian:  I tried your function and approach and I get this error:

ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/bluedot/Intranet/reports/basic_employers_01_results.asp, line 31


This is line 31:

sp_reporting.Parameters.Append sp_reporting.CreateParameter("@Empids", 3, 1,4,sp_reporting__Empids)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Brian Crowe

You're going to need a split function of some type.  My solution looks more complicated because I actually included the function.
ASKER
Aleks

I ran the function. And got the error above when I altered the Stored Procedure to what you sent.
Brian Crowe

The problem may be with your ASP not the TSQL.  Try executing the stored procedure directly.

EXECUTE report_basic_employer_01_results @Firmid = <some value>, @Empids = '1,34,35,3'
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Aleks

When I execute the above I get this error:

Msg 8114, Level 16, State 5, Procedure report_basic_employer_01_results, Line 0
Error converting data type varchar to int.
Brian Crowe

Please post exactly what you executed.
ASKER
Aleks

EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids = '1,34,35,3'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
zephyr_hex (Megan)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Aleks

I get the same error, this is my code:

USE [BlueDot];
GO

/****** Object:  StoredProcedure [dbo].[report_basic_employer_01_results]    Script Date: 12/29/2015 14:13:02 ******/
SET ANSI_NULLS OFF;
GO

SET QUOTED_IDENTIFIER OFF;
GO




ALTER PROCEDURE [dbo].[report_basic_employer_01_results] @Firmid INT,
    @Empid INT
AS
    SELECT  c.Id ,
            c.CaseId ,
            c.EmpId ,
            c.AlienId ,
            c.FirmAddressId ,
            c.ExpDate ,
            d.FirstNm ,
            d.MiddleNm ,
            d.LastNm ,
            d.Email ,
            d.NiStatus ,
            d.ExpiresOn ,
            c.FirmId ,
            c.MainCase ,
            d.NIVMaxStatus ,
            b.MaidenNm ,
            e.Processcatalog ,
            c.Archived ,
            f.Filedon ,
            f.Approvedon ,
            f.Validtodate
    FROM    Cases c
            LEFT JOIN Users AS d ON c.AlienId = d.UserId
            INNER JOIN Processcatalog AS e ON c.Process = e.ProcesscatalogID
            INNER JOIN Users AS b ON c.EmpId = b.UserId
            LEFT JOIN Activities_Misc AS f ON f.act_misc_id = c.Defaultrecpt
    WHERE   c.FirmId = @Firmid
            AND c.EmpId IN (select * from dbo.split(@Empid,','))
            AND c.Archived = 0
    ORDER BY d.LastNm ,
            c.CaseId;

Open in new window


After I added the function I run the following:

EXECUTE report_basic_employer_01_results @Firmid = 2, @Empid = '1,34,35,3'

I get this error:

Msg 8114, Level 16, State 5, Procedure report_basic_employer_01_results, Line 0
Error converting data type varchar to int.
Brian Crowe

What is the datatype of your Cases.Empid column?  I am assuming it is a VARCHAR/NVARCHAR due to the error you are getting.  In that case you want a split function that returns that type and not INT's as the one I provided does.  Try zephyr's split function.
ASKER
Aleks

empid is an INT, its trying to compare that INT with each of the numbers that are comma delimited.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Brian Crowe

I see the problem.  Change the datatype of your @Empids parameter to VARCHAR(MAX) from INT.

ALTER PROCEDURE dbo.report_basic_employer_01_results
(
    @Firmid INT,
    @Empids VARCHAR(MAX)
)
ASKER
Aleks

The SP works now ... I got my ASP code down. Only one thing remains.
If user selects at least one entry It filters out just fine. If user leaves it empty it should return ALL results, as if ALL where selected.

I tried this on my ASP

Dim sp_reporting__Empid
sp_reporting__Empid = "%"
if(request.form("employer")  <> "") then sp_reporting__Empid = request.form("employer")

Using the % sign as the default, but that doesn't work. what should I enter as default for all results to be returned if nothing is selected ?

I tried ""  but that returns no results.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Aleks

I changed it to:

WHERE   c.FirmId = @Firmid
        AND c.Archived = 0
            AND (@Empids IS NULL OR c.EmpId IN (SELECT c.EmpId FROM dbo.Split(@EmpIds, ','))
                  ORDER BY d.LastNm ,
        c.CaseId;


I get this error:

Msg 156, Level 15, State 1, Procedure report_basic_employer_01_results, Line 42
Incorrect syntax near the keyword 'ORDER'.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Brian Crowe

Count your parentheses
ASKER
Aleks

I added one, ran fine.
I didn't make any selection and I still got no results when making no selection

Running this:

EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids = ''

Gives no results
ASKER
Aleks

I changed it to:

        WHERE   c.FirmId = @Firmid
        AND c.Archived = 0
            AND (@Empids = '' --or compare with '%' depending on what you want to pass in
                  OR c.EmpId IN (SELECT c.EmpId FROM dbo.Split(@EmpIds, ',')))

and it returns all results. Thanks Ill split the points  :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Aleks

Great !  Thanks.
ASKER
Aleks

I talked too soon. It doesn't work If I do:

EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids = '15304'

I still get ALL results  :$
zephyr_hex (Megan)

Can you post the WHERE condition from your stored procedure again so we can verify it?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Aleks

It's right above
Brian Crowe

You can't pull EmpID from the split function because it doesn't have one.  You need to pull a column from the table returned by the split function.

SELECT c.EmpId FROM dbo.Split(@EmpIds, ',')
ASKER
Aleks

Well, that didn't work either.

I ran:

EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids = '2131231'

there is no such Empid in the table yet it returns ALL records.

This is what I currently have:

/****** Object:  StoredProcedure [dbo].[report_basic_cases_01_results]    Script Date: 12/30/2015 01:40:32 ******/
SET ANSI_NULLS OFF;
GO

SET QUOTED_IDENTIFIER OFF;
GO





ALTER PROCEDURE [dbo].[report_basic_cases_01_results]
    (
      @Firmid INT ,
      @Empids VARCHAR(MAX) = NULL
    )
AS
    SELECT  c.Id ,
            c.CaseId ,
            c.EmpId ,
            c.AlienId ,
            c.FirmAddressId ,
            c.ExpDate ,
            d.FirstNm ,
            d.MiddleNm ,
            d.LastNm ,
            d.Email ,
            d.NiStatus ,
            d.ExpiresOn ,
            c.FirmId ,
            c.MainCase ,
            d.NIVMaxStatus ,
            b.MaidenNm ,
            e.Processcatalog ,
            c.Archived ,
            f.Filedon ,
            f.Approvedon ,
            f.Validtodate
    FROM    Cases c
            LEFT JOIN Users AS d ON c.AlienId = d.UserId
            INNER JOIN Processcatalog AS e ON c.Process = e.ProcesscatalogID
            INNER JOIN Users AS b ON c.EmpId = b.UserId
            LEFT JOIN Activities_Misc AS f ON f.act_misc_id = c.Defaultrecpt
    WHERE   c.FirmId = @Firmid
            AND c.Archived = 0
            AND ( @Empids IS NULL
                 OR c.EmpId IN (SELECT c.EmpId FROM dbo.Split(@EmpIds, ',')))
    ORDER BY d.LastNm ,
            c.CaseId;



GO

Open in new window


Regardless of what value I pass all records are returned
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Brian Crowe

Your usage of the split function is wrong.  Change the column name to one that actually exists.

You can determine the schema of the dbo.Split by executing:

SELECT * FROM dbo.Split('1,2,3,4,5', ',')
ASKER
Aleks

Running that:  SELECT * FROM dbo.Split('1,2,3,4,5', ',')

gives me a column named 'value'

then I changed the code to:         OR c.EmpId IN (SELECT value FROM dbo.Split(@EmpIds, ',')))

Then I ran:  EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids = '2131231'

And I still get ALL results.

Is the function correct ?  

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 12/30/2015 13:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[Split] (@list nvarchar(max), @delim nchar(1) = ',')

returns table as

return
   with csvtbl(start, [stop]) as (
     select start = convert(bigint, 1), [stop] = charindex(@delim collate slovenian_bin2, @list + @delim)
     union all
     select start = [stop] + 1, [stop] = charindex(@delim collate slovenian_bin2, @list + @delim, [stop] + 1)
     from   csvtbl
     where  [stop] > 0
  )
  select ltrim(rtrim(substring(@list, start, case when [stop] > 0 then [stop] - start else 0 end))) as value
  from   csvtbl
  where  ([stop] > 0)
  and (ltrim(rtrim(substring(@list, start, case when [stop] > 0 then [stop] - start else 0 end))) <> '')

Open in new window

Brian Crowe

with

c.EmpId IN (SELECT c.EmpId FROM dbo.Split(@EmpIds, ','))

You are effectively doing

c.EmpId = c.EmpId

which will always evaluate to TRUE

You can see this if you execute

SELECT 999 FROM dbo.split('1,2,3,4,5', ',')
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Aleks

So what should I do then ?
zephyr_hex (Megan)

If you're using

 c.EmpId IN (SELECT value FROM dbo.Split(@EmpIds, ',')))

Open in new window


I don't think the problem is related to this condition.  That's why I asked you to re-post your WHERE condition ... because it's possible you're missing a parenthesis related to the OR which is causing all results to be returned.
Brian Crowe

There is nothing wrong with the split function.  I tested it.  Let's take it back a step or two and see if we can determine where it is going wrong.

Execute the query below.

DECLARE @FirmID	INT = 2,
	@Empids		VARCHAR(MAX) = '2131231'

SELECT  c.*
FROM    Cases c
        --LEFT JOIN Users AS d ON c.AlienId = d.UserId
        --INNER JOIN Processcatalog AS e ON c.Process = e.ProcesscatalogID
        --INNER JOIN Users AS b ON c.EmpId = b.UserId
        --LEFT JOIN Activities_Misc AS f ON f.act_misc_id = c.Defaultrecpt
WHERE   c.FirmId = @Firmid
        AND c.Archived = 0
        AND (@Empids IS NULL
                OR c.EmpId IN (SELECT value FROM dbo.Split(@EmpIds, ',')))

Open in new window

Then try executing it with some valid values in the @empids variable
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Aleks

The first one always gives me ALL results, the second one seems to give the right results. But the problem persists.

If I leave it blank and run:

EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids =  ''

I get no results, instead of ALL results.

I changed the where clause to:

    WHERE   c.FirmId = @Firmid
            AND c.Archived = 0
            AND ( @Empids IS NULL
                 OR c.EmpId IN (SELECT value FROM dbo.Split(@EmpIds, ',')))
    ORDER BY d.LastNm ,
            c.CaseId;
Brian Crowe

Blank is not the same as NULL so I would expect you to get no results when @Empids = '' with the WHERE as shown.

If you want an empty string to return all records then you need to change the WHERE clause to:

    WHERE   c.FirmId = @Firmid
            AND c.Archived = 0
            AND ( @Empids IS NULL OR @Empids = ''
                 OR c.EmpId IN (SELECT value FROM dbo.Split(@EmpIds, ',')))

Open in new window

ASKER
Aleks

Seems to have worked  :) ... let me test further and get back to you in 10 min.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Aleks

It works fine indeed. Thanks for the follow up  :)