Passing a string of IDs to a WHERE IN clause

Scenario: There is a stored procedure that accepts a comma separated string of Ids as ‘1589, 1586, 1587’.


Stored procedure:

CREATE PROCEDURE [dbo].[Employee_GetByIds]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (@IDs)
END


Issue: application threw an error since the Id field of the Employee table is in the type of int but the parameter (@IDs) is with the type of varchar. From our application where pass the string of ID list will be something like ‘1585,1586,1587’. So we have to hold this value in a string type variable and pass the value to the stored procedure. Stored procedure accepts the value as a varchar type parameter but it uses it in a int type field. That’s where the problem arises.

Solution: Use a table-valued function to insert the list of IDs to a table and use the function within the stored procedure to select the list of IDs from that table instead of comparing the raw ID list.


Splitter function that inserts the list of IDs to a table:

ALTER FUNCTION [dbo].[Splitter] (@IDs VARCHAR(100) )
RETURNS @Tbl_IDs TABLE(ID INT) AS

BEGIN
-- Append comma

SET @IDs = @IDs + ','
-- Indexes to keep the position of searching

DECLARE @Pos1 INT
DECLARE @pos2 INT

-- Start from first character

SET @Pos1=1
SET @Pos2=1

WHILE @Pos1 < LEN(@IDs)
BEGIN
SET @Pos1 = CHARINDEX(',',@IDs,@Pos1)
INSERT @Tbl_IDs SELECT CASE(SUBSTRING(@IDs,@Pos2,@Pos1-@Pos2) AS INT)
-- Go to next non comma character

SET @Pos2=@Pos1+1
-- Search from the next charcater

SET @Pos1 = @Pos1+1
END
RETURN
END


Stored procedure has been altered to use the list of IDs converted to a table value by the splitter function:

ALTER PROCEDURE [dbo].[SourceDocument_GetByIDs]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (SELECT ID FROM Splitter(@IDs))
END

Application Domain

One of the badly behaved DLLs/components of your application can bring your whole application or everything else down. One of the things we can do to resolve this issue is isolate the DLL/component from everything else. What does this isolation means? In pre-.Net days this only means isolating the code through processes.

If you don’t put an extra effort to isolate the components in your application, when you run the application, your whole application will run within a context of a single process. Since windows isolates process from each other through memory address, it’ll share the same memory space. All the components in the application have access to this common memory space shared though out the application. Because of this, a badly behaved piece of code can bring the whole application down.

How can you isolate processes? In pre.Net days COM is one of the technologies that enabled you to isolate processes by allowing a process to call a COM component which is an executable. But the main disadvantage of this method is since the processes can’t share memory or use the same address space, a complex marshalling process has to be used to copy data between the processes. Tough processes are great by considering security, the disadvantage is the performance. Because often number of processes will normally working together and you have to develop data marshalling processes to ensure the communication between those.

So the two main problems need to be addressed were the isolation of the processes and to ensure the marshalling process between processes to copy data between them.

In .Net, application domains are designed in a way that separating the processes without resulting performance problems with passing data between them. The whole idea behind applications domains are a process can be divided in to several application domains(containers). Most probably application domain corresponds to single application. Even though there are different executables, if they are running in the context of the same process, theoretically they can directly see each other’s data and it should share the same address space. But CLR makes sure that it does not happen. .Net Remoting is one of the areas application domains come in to action.

81st Battle of the maroons created the history in sri lankan sports

81st Battle of maroons made another memorable moment in sri lankan sports history by introducing a RF ID for the first time in a sporting event. Spectators were given a RF ID instead of conventional tear off tickets. The gates were equipped with the readers and the movements of the spectators will be monitored as the move in and out though the gates. This will streamline the ticketing process in future. Credit should go to the Battle of Maroons joint committee IT team for designing and implementing the system.