We help IT Professionals succeed at work.

Stored Procedure to select results IN ( )

118 Views
Last Modified: 2016-01-10
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
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))

Author

Commented:
I'll give it a try
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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

Author

Commented:
That seems much more complicated.  About to test the first solution.

Author

Commented:
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

Author

Commented:
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)
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

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

Author

Commented:
I ran the function. And got the error above when I altered the Stored Procedure to what you sent.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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'

Author

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
Please post exactly what you executed.

Author

Commented:
EXECUTE report_basic_employer_01_results @Firmid = 2, @Empids = '1,34,35,3'
CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.

Author

Commented:
empid is an INT, its trying to compare that INT with each of the numbers that are comma delimited.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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)
)

Author

Commented:
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.
Database Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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'.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
Count your parentheses

Author

Commented:
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

Author

Commented:
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  :)

Author

Commented:
Great !  Thanks.

Author

Commented:
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  :$
CERTIFIED EXPERT
Top Expert 2010

Commented:
Can you post the WHERE condition from your stored procedure again so we can verify it?

Author

Commented:
It's right above
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

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

Author

Commented:
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
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

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

Author

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

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

Author

Commented:
So what should I do then ?
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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

Author

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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

Author

Commented:
Seems to have worked  :) ... let me test further and get back to you in 10 min.

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.