?
Solved

Turning a Query into a Function

Posted on 2009-04-08
9
Medium Priority
?
222 Views
Last Modified: 2012-05-06
I have a query that returns 345 records.  I converted it to a function, but the function only returns 1 record (the most recent).

The query and function seem exactly thw same to me.  

Why would the function only return 1 record?
Working Query, returns 345 records:
 
DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @mid INT
SELECT 
    	@Start = '04-06-2009 00:00:00',
    	@End   = '04-06-2009 23:59:59',
	@mid = 3
 
select
	m.IDKey,
    	m.Channel,
	w.CTime,
	w.CDate,
	w.CallDateTime
   
  from
    OesData..utESGMarketChannelMap m 
      left outer join
    (
    SELECT
        h1.ivrs + h2.ivrs as civrs,
     CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN 
		CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                      + ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END 
		ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                      + ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END 
		END AS CTime, 
		h1.CallDateTime, 
		CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
      FROM  
        oesdata..uvTSIBHistory h1  
          left outer join  
        oesdata..uvTSIBHistory h2  
            ON  
              h1.phonenum = h2.phonenum  
              and h1.dnis = h2.dnis  
              and h1.calldatetime < h2.calldatetime  
              and h1.historyid < h2.historyid  
              and h2.projectid != 2000
              and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
      WHERE  
        h1.calldatetime BETWEEN @Start AND @End
        AND h2.calldatetime BETWEEN @Start AND @End 
        AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')    
        AND  h1.dnis != 2985  
        AND  h2.dnis != 2985  
        AND h1.projectid = 2000
    ) as w
        on
          w.civrs = m.IvrsPath
where m.IDKey = @mid
order by calldatetime desc
 
 
Call to function: select * from fnMktCallsHalfHour('04-06-2009', '04-06-2009', 3)
 
Function that returns only 1 record:
 
ALTER FUNCTION fnMktCallsHalfHour 
(	
	@StartDate datetime,
	@Enddate datetime,
	@MIDX int
)
RETURNS @MCHH TABLE (
	IDKey Int null,
	Channel varchar(50) null,
	CTime varchar(50) null,
	CDate varchar(50) null,
	CallDateTime DateTime null
 ) AS
 
BEGIN
DECLARE 
		@MID int,
		@start DATETIME,
		@end DATETIME,
		@IDKey Int,
		@Channel varchar(50),
		@CTime varchar(50),
		@CDate varchar(50),
		@CallDateTime DateTime
 
 
 set   @Start = @StartDate + ' 00:00:00'
 set   @End   = @EndDate + ' 23:59:59'
 set	@MID=@MIDX
 
select
	@IDKey = m.IDKey,
    @Channel= m.Channel,
	@CTime =w.CTime,
	@CDate = w.CDate,
	@CallDateTime = w.CallDateTime
   
  from
    OesData..utESGMarketChannelMap m 
      left outer join
    (
	    SELECT
		h1.ivrs + h2.ivrs as civrs,
		CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN 
			CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
			      + ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END 
			ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
			      + ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END 
			END AS CTime, 
			h1.CallDateTime, 
			CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
	      FROM  
		oesdata..uvTSIBHistory h1  
		  left outer join  
		oesdata..uvTSIBHistory h2  
		    ON  
		      h1.phonenum = h2.phonenum  
		      and h1.dnis = h2.dnis  
		      and h1.calldatetime < h2.calldatetime  
		      and h1.historyid < h2.historyid  
		      and h2.projectid != 2000
		      and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
	      WHERE  
		h1.calldatetime BETWEEN @Start AND @End
		AND h2.calldatetime BETWEEN @Start AND @End 
		AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')    
		AND  h1.dnis != 2985  
		AND  h2.dnis != 2985  
		AND h1.projectid = 2000
    ) as w
 on
    w.civrs = m.IvrsPath
where 
	m.IDKey = @MID
 
INSERT @MCHH
SELECT 	@IDKey, @Channel, @CTime, @CDate, @CallDateTime;
RETURN;
END;
GO

Open in new window

0
Comment
Question by:jriggin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 24096954
Hi,

When you assign result from a select to variables you only get one row.

Do like this instead:


/peter
INSERT INTO @MCHH
select m.IDKey,
    m.Channel,
        w.CTime,
        w.CDate,
        w.CallDateTime
  from
    OesData..utESGMarketChannelMap m 
      left outer join
    (
            SELECT
                h1.ivrs + h2.ivrs as civrs,
                CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN 
                        CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                              + ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END 
                        ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                              + ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END 
                        END AS CTime, 
                        h1.CallDateTime, 
                        CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
              FROM  
                oesdata..uvTSIBHistory h1  
                  left outer join  
                oesdata..uvTSIBHistory h2  
                    ON  
                      h1.phonenum = h2.phonenum  
                      and h1.dnis = h2.dnis  
                      and h1.calldatetime < h2.calldatetime  
                      and h1.historyid < h2.historyid  
                      and h2.projectid != 2000
                      and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
              WHERE  
                h1.calldatetime BETWEEN @Start AND @End
                AND h2.calldatetime BETWEEN @Start AND @End 
                AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')    
                AND  h1.dnis != 2985  
                AND  h2.dnis != 2985  
                AND h1.projectid = 2000
    ) as w
 on
    w.civrs = m.IvrsPath
where 
        m.IDKey = @MID

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24096961
That's because you are only assigning one value to variables, then inserting the variables into the function.

Try:

Function that returns only 1 record:
 
ALTER FUNCTION fnMktCallsHalfHour 
(       
        @StartDate datetime,
        @Enddate datetime,
        @MIDX int
)
RETURNS @MCHH TABLE (
        IDKey Int null,
        Channel varchar(50) null,
        CTime varchar(50) null,
        CDate varchar(50) null,
        CallDateTime DateTime null
 ) AS
 
BEGIN
DECLARE 
                @MID int,
                @start DATETIME,
                @end DATETIME,
                @IDKey Int,
                @Channel varchar(50),
                @CTime varchar(50),
                @CDate varchar(50),
                @CallDateTime DateTime
 
 
 set   @Start = @StartDate + ' 00:00:00'
 set   @End   = @EndDate + ' 23:59:59'
 set    @MID=@MIDX
 
INSERT @MCHH(IDKey,Channel,CTime,CDate,CallDateTime)
select
        m.IDKey,
        m.Channel,
        w.CTime,
        w.CDate,
        w.CallDateTime
   
  from
    OesData..utESGMarketChannelMap m 
      left outer join
    (
            SELECT
                h1.ivrs + h2.ivrs as civrs,
                CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN 
                        CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                              + ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END 
                        ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                              + ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END 
                        END AS CTime, 
                        h1.CallDateTime, 
                        CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
              FROM  
                oesdata..uvTSIBHistory h1  
                  left outer join  
                oesdata..uvTSIBHistory h2  
                    ON  
                      h1.phonenum = h2.phonenum  
                      and h1.dnis = h2.dnis  
                      and h1.calldatetime < h2.calldatetime  
                      and h1.historyid < h2.historyid  
                      and h2.projectid != 2000
                      and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
              WHERE  
                h1.calldatetime BETWEEN @Start AND @End
                AND h2.calldatetime BETWEEN @Start AND @End 
                AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')    
                AND  h1.dnis != 2985  
                AND  h2.dnis != 2985  
                AND h1.projectid = 2000
    ) as w
 on
    w.civrs = m.IvrsPath
where 
        m.IDKey = @MID
 
 
 
RETURN;
END;
GO

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24096978
query in function will work like loop, each time you assign
  @IDKey = m.IDKey,
    @Channel= m.Channel,
        @CTime =w.CTime,
        @CDate = w.CDate,
        @CallDateTime = w.CallDateTime

so each time it overwrite by the recent value and finally when you return it, it will return the most recent value?

is there any specific reason to make function? why don't you make SP?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097012
> "is there any specific reason to make function? why don't you make SP?"

One reason I can think of, on behalf of the asker, is that they may need to JOIN to it.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24097036
do like this...
create proc testProc ( 
 @start DATETIME,
 @end DATETIME,
@mid INT
)
as
begin
select
        m.IDKey,
        m.Channel,
        w.CTime,
        w.CDate,
        w.CallDateTime
   
  from
    OesData..utESGMarketChannelMap m 
      left outer join
    (
    SELECT
        h1.ivrs + h2.ivrs as civrs,
     CASE WHEN (datename(n, h1.CallDateTime) < 30) THEN 
                CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                      + ':00' ELSE datename(hh, h1.CallDateTime) + ':00' END 
                ELSE CASE WHEN (datename(hh, h1.CallDateTime) < 10) THEN '0' + datename(hh, h1.CallDateTime) 
                      + ':30' ELSE datename(hh, h1.CallDateTime) + ':30' END 
                END AS CTime, 
                h1.CallDateTime, 
                CONVERT(varchar(50), h1.CallDateTime, 101) + ' ' + DATENAME(dw, h1.CallDateTime) AS CDate
      FROM  
        oesdata..uvTSIBHistory h1  
          left outer join  
        oesdata..uvTSIBHistory h2  
            ON  
              h1.phonenum = h2.phonenum  
              and h1.dnis = h2.dnis  
              and h1.calldatetime < h2.calldatetime  
              and h1.historyid < h2.historyid  
              and h2.projectid != 2000
              and datediff(ss,(dateadd(ss,h1.connecttime,h1.calldatetime)),h2.calldatetime) < 5
      WHERE  
        h1.calldatetime BETWEEN @Start AND @End
        AND h2.calldatetime BETWEEN @Start AND @End 
        AND h2.ProjectID IN(SELECT projectid FROM uvTSProjects WHERE projectname like '%ESG%')    
        AND  h1.dnis != 2985  
        AND  h2.dnis != 2985  
        AND h1.projectid = 2000
    ) as w
        on
          w.civrs = m.IvrsPath
where m.IDKey = @mid
order by calldatetime desc
end
GO
 
--run like this
exec testProc @Start = '04-06-2009 00:00:00',@End   = '04-06-2009 23:59:59',@mid = 3

Open in new window

0
 
LVL 1

Author Closing Comment

by:jriggin
ID: 31568013
Peter (pivar) was first and correct.

Thanks to all!  
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097166
A correction to my code after looking at your date handling:


 set   @Start = @StartDate + ' 00:00:00'
 set   @End   = @EndDate + ' 23:59:59'


Should be:

 set   @Start = dateadd(d, datediff(d, 0, @StartDate),0)
 set   @End   = dateadd(d, datediff(d, 0, @EndDate)+1,0)


And your use of it:

                h1.calldatetime BETWEEN @Start AND @End
                AND h2.calldatetime BETWEEN @Start AND @End


Should be:

                h1.calldatetime >= @Start AND h1.calldatetime < @End
                AND h2.calldatetime >= @Start AND h2.calldatetime < @End



It's a more reliable way of doing it since will always include the FULL day of @EndDatetime.  Your way would miss everything that occurs in the last second of the day.  Would it be a noticeable problem, probably not.  But it can happen and it's not correct.
0
 
LVL 1

Author Comment

by:jriggin
ID: 24097172
BTW - could not use proc as need to use results in view.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097201
"Peter (pivar) was first and correct."

While true, we posted at nearly the same time, both providing a good explanation and working code. :)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question