Tuesday, April 24, 2012

Equivalent Split function in SqlServer

Several times we have a collection of unique ids and we have to do some operation like update or delete based upon those ids.Now Given below function works as split and send all  values to the table.
This function basically needs three parametes-:
1) a string containg values seperated by some delimeter
2) delimiter
3)TrimSpace option( bit type to kill whitespaces)

Create FUNCTION [dbo].[fn_String_To_Table] (
            @String VARCHAR(max), /* input string */
   @Delimeter char(1),   /* delimiter */
   @TrimSpace bit )      /* kill whitespace? */
RETURNS @Table TABLE ( [Val] VARCHAR(4000) )
AS
BEGIN
    DECLARE @Val    VARCHAR(4000)
    WHILE LEN(@String) > 0
    BEGIN
        SET @Val    = LEFT(@String,
             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),
             LEN(@String)))
        SET @String = SUBSTRING(@String,
             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),
             LEN(@String)) + 1, LEN(@String))
  IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))
    INSERT INTO @Table ( [Val] )
        VALUES ( @Val )
    END
    RETURN
END

Now here is a Stored Procedure using above function to update table.

create PROCEDURE [dbo].[sp_AdminDelStudEntry]
@RegNoString varchar(max)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[Admission] SET [Isdelete] = 'Y'
    WHERE [Reg_Noid] in (SELECT Val FROM [dbo].[fn_String_To_Table](@RegNoString,',',1))
 
UPDATE [dbo].[Academic] SET [Isdeleted] = 'Y'
    WHERE [regno] in (SELECT Val FROM [dbo].[fn_String_To_Table](@RegNoString,',',1))
END

No comments:

Post a Comment