Stored Procedure to select results IN ( )

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

Select allOpen in new window

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

I'll give it a try

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;

Select allOpen 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

Select allOpen in new window

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

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

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)

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

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

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'

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.

Please post exactly what you executed.

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

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;

Select allOpen 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.

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.

empid is an INT, its trying to compare that INT with each of the numbers that are comma delimited.

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

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.

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

Count your parentheses

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

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

Great !  Thanks.

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

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

It's right above

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

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

Select allOpen in new window

Regardless of what value I pass all records are returned

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

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

Select allOpen in new window

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

So what should I do then ?

If you're using

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

Select allOpen 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.

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

Select allOpen in new window

Then try executing it with some valid values in the @empids variable

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;

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

Select allOpen in new window

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

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