if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [sp_GenDeptTable]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [sp_GenDeptTable] GO
Set quoted_identifier off goset ANSI_NULLS ON GO
Create Procedure SP_gendEPTTTable (@cols int, @tds varchar (50), @TABLES VARCHAR (50)) ASDECLARE @R Table (v varchar (8000)) Declare @did bigint, @PID bigint, @ DNAME VARCHAR (1000), @pname varchar (1000), @i int, @n varchar (1000), @v varchar (8000) Declare C Cursor for select d0.deptid, d0.name, d1.deptid, ' ' D1.NAME ' 'from Department D0 Inner Join Department D1 On D0. deptid = d1.parentdeptid and d0.parentdeptid is null order by d0.deptid, d1.deptidopen cfetch from c into @pid, @pname, @did, @dnameselect @v = @tables @trsselect @n = '' while @ @FETCH_STATUS = 0BEGIN if @n <> @pname begin select @n = @pname insert @R value (' ' @pname ' ') end select @v = @tables @TRS @TDS @dname ' "select @i = 0
Fetch next from c into @pid, @pname, @did, @dname while @@ fetch_status = 0 and @i <@ cols-1 and @pname = @n begin select @i = @i 1 select @v = @ V @TDS @dname ' Fetch next from c @pid, @pname, @did, @dname endwhile @i <@cols - 1 begin select @v = @v @TDS ' 'SELECT @i = @i 1 End select @v = @v ' < 'INSERT INTO @R Values (@v) endclose cdeallocate cselect v as' Select a message range From @RGOSet quoted_identifier off goset ansi_nulls on Go
