The Weird and The Wonderful

CREATE PROCEDURE [dbo].[WorkOrder_Search](@Description varchar(250)) 
AS
Begin
	SELECT Order_id,Dept_Code,Graphic_Number,[Description],Quantity,Date_Received as [Recv. Date],Date_Due as [Due Date],
	Date_Delivered as [Delivered Date],proof_sent_date as [Proof Date],print_date as [To Print],
	dbo.func_grOrder_GetAssigned(Order_id) as [Assign To],Status,Note, Master_Cal_ID 
	From grGraphic_Order  
	where [Description] like '%' + @Description + '%'
end

CREATE PROCEDURE [dbo].[WorkOrder_search](@Description varchar(250))
AS
begin
	SET QUOTED_IDENTIFIER  OFF    
	SET ANSI_NULLS  OFF 
	SET NoCount ON

	Declare @sql varchar(8000)
	Declare @Orig_1 varchar(1000)
	Declare @Declare varchar(200),
	@From varchar(100),@Into varchar(50),@Groupby varchar(300),
	@Orderby varchar(400),@Select varchar(400),@Select1 varchar(400),@Select2 varchar(300),
	@cursor1 varchar (500),@cursor2 varchar(4000),@create varchar(4000)

	set @Orig_1 = ''

	Create table #Assignto_temp(Order_id int, Assignto_names varchar(500))

	Insert into #Assignto_temp(Order_id )

	Select gro.order_id
	From grassignto gro
	Inner join wfpmauser wf
	On gro.user_id = wf.user_id
	group by gro.order_id

	declare @order_number int, @Assignto varchar(255), @List_names varchar(255),@order_number_prev int
	declare assignto Cursor
	Local
	Forward_only
	for

	Select gro.order_id,Wf.First_name+' '+Wf.Last_name as Assignto
	From grassignto gro
	Inner join wfpmauser wf
	On gro.user_id = wf.user_id
	order by order_id 

	open assignto
	Fetch Next from assignto into @order_number, @Assignto
	While @@Fetch_Status=0
	begin
			select @List_names=Assignto_names from #Assignto_temp where order_id=@order_number
	 	
		if @List_names is null 
		begin
			update #Assignto_temp
			set Assignto_names=@Assignto
			where order_id=@order_number
		end
		else
		begin
			update #Assignto_temp
			set Assignto_names=@list_names + ',' + @Assignto
			where order_id=@order_number
		end
		  Fetch Next from assignto into @order_number, @Assignto
	end
	close assignto
	deallocate assignto

	Set @Declare ="Declare @Orig_1 varchar(1000),@Originator varchar(100),@order_id int,@Orig varchar(300) set @Orig_1 = '' " 
	Set @Select =  " SELECT distinct gro.* "
	Set @Into = "Into #tmp1 "
	Set @From = "FROM grGraphic_Order Gro left join grOriginator gr on gro.order_id = gr.order_id "

	Set @Orderby = "ORDER BY Gro.Graphic_number,Gro.dept_code Desc  "
	Set @Select2 = " SELECT Order_id,Dept_Code ,Graphic_Number,Description,Quantity,Date_Received as [Recv. Date],
	Date_Due as [Due Date],Date_Delivered as [Delivered Date],proof_sent_date as [Proof Date],print_date as [To Print],
	Status,Note, master_cal_id [Master Calendar ID] From #tmp1  ORDER BY dept_code,Graphic_number  "

	Set @Create = "Create table #Assignto_temp(Order_id int, Assignto_names varchar(500))
			Insert into #Assignto_temp(Order_id )
			Select gro.order_id
			From grassignto gro
			Inner join wfpmauser wf
			On gro.user_id = wf.user_id
			group by gro.order_id


 			declare @order_number int, @Assignto varchar(255), @List_names varchar(255),@order_number_prev int
			declare assignto Cursor
			Local
			Forward_only
			for
		
			Select gro.order_id,Wf.First_name+' '+Wf.Last_name as Assignto
			From grassignto gro
			Inner join wfpmauser wf
			On gro.user_id = wf.user_id
			order by order_id 
		
			open assignto
			Fetch Next from assignto into @order_number, @Assignto
			While @@Fetch_Status=0
			begin
					select @List_names=Assignto_names from #Assignto_temp where order_id=@order_number
			 	
				if @List_names is null 
				begin
			
					update #Assignto_temp
					set Assignto_names=@Assignto
					where order_id=@order_number
			
				end
				else
				begin
					update #Assignto_temp
					set Assignto_names=@list_names + ',' + @Assignto
					where order_id=@order_number
				end

				  Fetch Next from assignto into @order_number, @Assignto

			end
			close assignto
			deallocate assignto

	SELECT #tmp1.Order_id,Dept_Code ,Graphic_Number,Description,Quantity,Date_Received as [Recv. Date],
	Date_Due as [Due Date],Date_Delivered as [Delivered Date],proof_sent_date as [Proof Date],print_date as [To Print],
	isnull(tmp.assignto_names,'Not Assigned') as [Assign To],Status,Note, #tmp1.Master_Cal_ID 
	From #tmp1 LEFT Join #Assignto_temp tmp ON #tmp1.order_id = tmp.order_id ORDER BY Dept_Code,Date_Received Desc"

	Select @sql=@Declare + @Select + @Into + @From + ' where Description like ' 
				+ char(39) + '%' + @Description + '%'  + char(39)+ @Orderby  + @create 
	Exec(@sql)
end