create function  [dbo].[fun_splitin](@c varchar(),@split varchar())

 returns @t  table(col varchar())

 as

 begin

  while(charindex(@split,@c)<>)

    begin

      insert  @t(col)values (substring(@c,,charindex(@split,@c)-))

      set @c=stuff(@c,,charindex(@split,@c),'')

     end

  insert @t(col)values(@c)

 return

end

create procedure [dbo].[sp_getallstaticgoodlocationdata]

     @platoon varchar()

as

begin

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_getstaticgoodlocationdata_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = )

             drop table [dbo].[t_getstaticgoodlocationdata_temp]

             create   table   [t_getstaticgoodlocationdata_temp](id numeric()  identity(,) primary key , the_floor int,the_column1 int,the_column2 int,the_column3 int,the_column4 int,the_column5 int,the_column6 int,the_column7 int,the_column8 int,the_column9 int,the_column10 int,the_column11 int,the_column12 int

             ,the_column13 int,the_column14 int,the_column15 int,the_column16 int,the_column17 int,the_column18 int,the_column19 int,the_column20 int,the_column21 int,the_column22 int,the_column23 int,the_column24 int,the_column25 int,the_column26 int,the_column27 int,the_column28 int,the_column29 int,the_column30 int,

             the_column31 int,the_column32 int,the_column33 int,the_column34 int,the_column35 int,the_column36 int,the_column37 int,the_column38 int

             ,the_column39 int,the_column40 int)    

    DECLARE @the_platoon int

    DECLARE cursor_name CURSOR FOR

        select distinct F_Platoon from Sys_GoodLocation where F_Status= and F_Locked='N' and F_Platoon in ((select col from dbo.fun_splitin(@platoon,',')) )

    OPEN cursor_name

    FETCH NEXT FROM cursor_name INTO @the_platoon

    WHILE @@FETCH_STATUS =

        BEGIN

            if @the_platoon<>

                begin

                    insert into [dbo].[t_getstaticgoodlocationdata_temp](the_floor,the_column1,the_column2,the_column3,the_column4,the_column5,the_column6,the_column7,the_column8,the_column9,the_column10,the_column11,the_column12

                    ,the_column13,the_column14,the_column15,the_column16,the_column17,the_column18,the_column19,the_column20,the_column21,the_column22,the_column23,the_column24,the_column25,the_column26,the_column27,the_column28,the_column29,the_column30,

                    the_column31,the_column32,the_column33,the_column34,the_column35,the_column36,the_column37) exec sp_getgoodlocationviaplatoon @the_platoon

                end

            FETCH NEXT FROM cursor_name INTO @the_platoon

        END

    CLOSE cursor_name

    DEALLOCATE cursor_name 

    select the_floor,convert(int,isnull(SUBSTRING(convert(varchar(),the_column1),,),)) as the_platoon,the_column1,the_column2,the_column3,the_column4,the_column5,the_column6,the_column7,the_column8,the_column9,the_column10,the_column11,the_column12

    ,the_column13,the_column14,the_column15,the_column16,the_column17,the_column18,the_column19,the_column20,the_column21,the_column22,the_column23,the_column24,the_column25,the_column26,the_column27,the_column28,the_column29,the_column30,

    the_column31,the_column32,the_column33,the_column34,the_column35,the_column36,the_column37 from [dbo].[t_getstaticgoodlocationdata_temp]

end

exec [dbo].[sp_getallstaticgoodlocationdata] '1,2,3,6'