Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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

0
Tagom
Asked:
Tagom
3 Solutions
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now