what is the difference between these stored procedures!

I have two stored procedures.
One returns data - one does not! However I do not see the cause or the difference.
this one returns data
USE [acisql2]
GO
/****** Object:  StoredProcedure [dbo].[reportCalloutHistory]    Script Date: 10/05/2011 15:33:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[reportCalloutHistory]
	@startDate DATETIME = NULL,
	@endDate DATETIME = NULL,
	@utilityKey VARCHAR(38),
	@autodate int = null
AS
BEGIN
	DECLARE @report VARCHAR(4000),
			@orderby VARCHAR (4000)

	set @startDate = [dbo].[getAutoDateStart](@autodate, @startDate, getDate())
	set @endDate = [dbo].[getAutoDateEnd](@autodate, @endDate, getDate())

	BEGIN 
	set @report =
		' SELECT
			cc_calloutlog.CLID,
			CLEmpID,
			CLInfo1,
			CLInfo2,
			CLInfo3,
			CLInfo4,
			CLStartCallTime,
			CLAttempts,
				case CLResultofLastAttempt
			when ''Paused'' then ''Pager Answered''
			else CLResultofLastAttempt
			end
			as CLResultofLastAttempt,
			case CLACCEPTED
				when '''' then
					case CLResultoflastattempt
						when ''Acknowledged'' then ''Yes''
						when ''Accept'' then ''Yes''
						when ''Reject (Manual)'' then ''No''
						when ''Accept (Manual)'' then ''Yes''
						else ''N/A''
					end
				else
					claccepted
			end
			as CLAccepted,
			CLPhone1,
			CLPhone2,
			CLPhone3,
			CLPhone4,
			CLCallInfo1,
			CLCallInfo2,
			CLCallInfo3,
			CLCallInfo4,
			CLPriority,
			CLDistrict as LocationID,
			CLUserid,
			CLChannelstatus,
			CLCNCrewName,
			cldatetime,
			clphonenumber,
			clresult,
			clutilityid
		FROM
			CC_CalloutLog 
			join  cc_calllog on cc_calllog.clid = cc_calloutlog.clid
			where clutilityid  = ''' + @utilityKey + '''
			and clstartcalltime between ''' + CAST(@startDate AS VARCHAR(20)) + ''' AND ''' + CAST(@endDate AS VARCHAR(20)) + '''
			and 
			(
				(clattempts > 0 or 
						(clresultoflastattempt <> '''' and clresultoflastattempt not like ''Delete%'')
				)
					or (clresult like ''%page%'' or clresult = ''acknowledged'' or claccepted = ''Yes'')
				)'
	END
exec (@report + @orderby)
END
GRANT EXECUTE ON reportCalloutHistory TO porchesys

Open in new window

this one returns no data
USE [acisql2]
GO
/****** Object:  StoredProcedure [dbo].[CC_Report_CalloutHistory]    Script Date: 10/05/2011 15:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[CC_Report_CalloutHistory]
@Utilitykey as varchar(38), @StartTime datetime, @EndTime datetime
as
set @starttime = isnull(@Starttime,'01/01/1900')
set @endtime = isnull(@endtime,'01/01/2090')

set @UtilityKey = isnull(@UtilityKey, '%')
SELECT
    cc_calloutlog.CLID,
    CLEmpID,
    CLInfo1,
    CLInfo2,
    CLInfo3,
    CLInfo4,
    CLStartCallTime,
    CLAttempts,
        case CLResultofLastAttempt
	when 'Paused' then 'Pager Answered'
	else CLResultofLastAttempt
	end
    as CLResultofLastAttempt,
    case CLACCEPTED
		when '' then
			case CLResultoflastattempt
				when 'Acknowledged' then 'Yes'
				when 'Accept' then 'Yes'
				when 'Reject (Manual)' then 'No'
				when 'Accept (Manual)' then 'Yes'
				else 'N/A'
			end
		else
			claccepted
	end
    as CLAccepted,
    CLPhone1,
    CLPhone2,
    CLPhone3,
    CLPhone4,
    CLCallInfo1,
    CLCallInfo2,
    CLCallInfo3,
    CLCallInfo4,
    CLPriority,
    CLDistrict as LocationID,
    CLUserid,
    CLChannelstatus,
    CLCNCrewName,
    cldatetime,
    clphonenumber,
    clresult
FROM
    CC_CalloutLog 
    join  cc_calllog on cc_calllog.clid = cc_calloutlog.clid
    where '{' + cast(clutilityid as varchar(36)) + '}' like @UtilityKey
    and clstartcalltime between @starttime and @endtime
    and 
    (
		(clattempts > 0 or 
				(clresultoflastattempt <> '' and clresultoflastattempt not like 'Delete%')
		)
			or (clresult like '%page%' or clresult = 'acknowledged' or claccepted = 'Yes')
		)
    order by clstartcalltime, clindex, cldatetime

Open in new window

TagomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
           where clutilityid  = ''' + @utilityKey + '''
      
    where '{' + cast(clutilityid as varchar(36)) + '}' like @UtilityKey
0
TagomAuthor Commented:
hmmm, that should not make a difference in data which is returned though....or at least I would not think so
0
HainKurtSr. System AnalystCommented:
say you pass 'ABC'

where clutilityid  = ''' + @utilityKey + '''
-->
where  clutilityid  = 'ABC'

where '{' + cast(clutilityid as varchar(36)) + '}' like @UtilityKey
-->
where '{' + cast(clutilityid as varchar(36)) + '}' like 'ABC'

maybe you should do this

where cast(clutilityid as varchar(36)) like '%' + @UtilityKey + '%'

whats the purpose of adding {...}
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

TagomAuthor Commented:
This is an old crystal report procedure that I am trying to convert...not sure.
0
Hernan_CanoCommented:
Maybe it's too obvious, but here it goes: the order of the parameters is important if you don't execute the SP naming the destination parameters.

About the  {}, maybe the Key is in a GUID-like format. I've seen some people formatting CD keys like that. i.e {SJGH-SDJH-sSD-ABc}.
0
TagomAuthor Commented:
That very well could be.
0
jogosCommented:
And if you say your not convinced that this is not a huge candidate for giving another result, most people  think otherwise (when given the same input).

where  clutilityid  = 'ABC'
where '{' + cast(clutilityid as varchar(36)) + '}' like 'ABC'
-> unless you don't pass NULL or % you should always include pass the {} so
where '{' + cast(clutilityid as varchar(36)) + '}' like '{ABC}'
or better, just drop them at the other side
where cast(clutilityid as varchar(36)) like 'ABC'

Maybe you could give the facts
1) With wich parameters you do the call both procedures
2) what is the result off the one that is giving result
3) the datatype of clutilityid in the table

Proc1
only selects when equal
could change your begin/end-date because there is a function
uses dynamic sql only for the sake off a dynamic order clause (which is always NULL !!!!), this makes this unneeded vulnarable for sql injection (ok with limit off 36 chars)

Proc2
like (see higher)
leaves original dates
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.