Click here to Skip to main content
14,241,806 members

The Weird and The Wonderful

   

The Weird and The Wonderful forum is a place to post Coding Horrors, Worst Practices, and the occasional flash of brilliance.

We all come across code that simply boggles the mind. Lazy kludges, embarrassing mistakes, horrid workarounds and developers just not quite getting it. And then somedays we come across - or write - the truly sublime.

Post your Best, your worst, and your most interesting. But please - no programming questions . This forum is purely for amusement and discussions on code snippets. All actual programming questions will be removed.

 
GeneralRe: Best Practices turned into Coding Horrors. Pin
BobJanova17-Apr-13 5:53
memberBobJanova17-Apr-13 5:53 
GeneralRe: Best Practices turned into Coding Horrors. Pin
KP Lee22-Apr-13 0:13
memberKP Lee22-Apr-13 0:13 
GeneralRe: Best Practices turned into Coding Horrors. Pin
KP Lee22-Apr-13 0:04
memberKP Lee22-Apr-13 0:04 
AnswerRe: Best Practices turned into Coding Horrors. Pin
Clifford Nelson22-Apr-13 9:13
mvaClifford Nelson22-Apr-13 9:13 
GeneralRe: Best Practices turned into Coding Horrors. Pin
Paulo Zemek22-Apr-13 9:15
professionalPaulo Zemek22-Apr-13 9:15 
GeneralRe: Best Practices turned into Coding Horrors. Pin
Tim Yen22-Apr-13 14:02
memberTim Yen22-Apr-13 14:02 
GeneralRe: Best Practices turned into Coding Horrors. Pin
Paulo Zemek22-Apr-13 15:21
professionalPaulo Zemek22-Apr-13 15:21 
GeneralLogical thinking could get very complicated Pin
Tomz_KV12-Apr-13 14:53
memberTomz_KV12-Apr-13 14:53 
Here is a simple stored procedure in company database that does a search and returns results in less than a second:

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

But it used to be a really "BIG" stored procedure as shown below that did the same search and took about 5 minutes to return results. It did return the same results as the above does.

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

TOMZ_KV


modified 13-Apr-13 8:57am.

GeneralRe: Logical thinking could get very complicated Pin
Brisingr Aerowing12-Apr-13 16:10
professionalBrisingr Aerowing12-Apr-13 16:10 
GeneralRe: Logical thinking could get very complicated Pin
OriginalGriff12-Apr-13 23:16
protectorOriginalGriff12-Apr-13 23:16 
GeneralRe: Logical thinking could get very complicated Pin
ENOTTY13-Apr-13 20:40
memberENOTTY13-Apr-13 20:40 
GeneralRe: Logical thinking could get very complicated Pin
Tomz_KV15-Apr-13 1:40
memberTomz_KV15-Apr-13 1:40 
JokeRe: Logical thinking could get very complicated Pin
Bernhard Hiller14-Apr-13 22:01
memberBernhard Hiller14-Apr-13 22:01 
GeneralRe: Logical thinking could get very complicated Pin
Tomz_KV15-Apr-13 1:42
memberTomz_KV15-Apr-13 1:42 
GeneralRe: Logical thinking could get very complicated Pin
ENOTTY15-Apr-13 4:25
memberENOTTY15-Apr-13 4:25 
GeneralRe: Logical thinking could get very complicated Pin
YDaoust15-Apr-13 20:27
memberYDaoust15-Apr-13 20:27 
GeneralRe: Logical thinking could get very complicated Pin
Tomz_KV16-Apr-13 1:34
memberTomz_KV16-Apr-13 1:34 
GeneralUnambiguously Ambiguous Pin
AspDotNetDev10-Apr-13 15:53
protectorAspDotNetDev10-Apr-13 15:53 
GeneralRe: Unambiguously Ambiguous Pin
Brisingr Aerowing10-Apr-13 17:13
professionalBrisingr Aerowing10-Apr-13 17:13 
GeneralRe: Unambiguously Ambiguous Pin
Wendelius10-Apr-13 18:37
mveWendelius10-Apr-13 18:37 
GeneralRe: Unambiguously Ambiguous Pin
Jim Meadors10-Apr-13 19:11
memberJim Meadors10-Apr-13 19:11 
GeneralRe: Unambiguously Ambiguous Pin
lw@zi 10-Apr-13 22:07
professional lw@zi 10-Apr-13 22:07 
GeneralLet's "switch" to Something Else Pin
Tom Delany2-Apr-13 9:31
memberTom Delany2-Apr-13 9:31 
GeneralRe: Let's "switch" to Something Else Pin
Brisingr Aerowing2-Apr-13 12:13
professionalBrisingr Aerowing2-Apr-13 12:13 
GeneralRe: Let's "switch" to Something Else Pin
Freak302-Apr-13 21:44
memberFreak302-Apr-13 21:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.