vw or udf - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

vw or udf

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-06-17 : 18:17:37

Are you able to pass parameters into a view or udf? I can't find sources that says yes or no. What I'm trying to do is create a vw or function that if parameters are entered, it will use those parameters else default in this case getdate.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 18:19:35

Yes to functions and sort of yes to views.

Could you show us a sample so that we can be more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-17 : 18:42:43

sounds like a job for a table valued function.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 01:23:12

Post your requirement and then we can clearly suggest if you need view or udf

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-06-18 : 09:46:23

This is what I have so far:
(This procedure calls another procedure that does the work)
ALTER PROCEDURE [dbo].[usp_GetReport]
@StartDate as datetime = null,
@EndDate as datetime = null
.
.
.
if @StartDate is null set @StartDate = DATEADD(dd, -1, getdate())
if @EndDate is null set @EndDate = DateAdd(dd, 1, @StartDate)

set @qry = 'execute dbo.usp_Notes ''' + convert(varchar(10),@StartDate,126) + ''', ''' + convert(varchar(10),@EndDate,126) + '''

/*<title>OBV-RBV Report</title><Comments>Report Date ' + CONVERT(varchar, @StartDate, 101)+'</comments>*/'

EXEC Robots.dbo.sp_SendSAM
@sender_email = @Sender,
@recipients = @Recipient,
@Subject = @Subject,
@Message = @Message,
@Query = @qry,
@Attach_Results = 'true',
@Attachment_Format = 'XLS'

(This procedure is what I want to change to either a vw or udf)
ALTER PROCEDURE [dbo].[usp_Notes]
-- Add the parameters for the stored procedure here
@StartDate as Datetime = null,
@EndDate as Datetime

AS
BEGIN
SET NOCOUNT ON;

IF @StartDate = Null
SET @StartDate = DATEADD(dd,-1,CONVERT(varchar(10),GETDATE(),101))
ELSE
--Populates a temporary table with general criteria.
SELECT
iks.ArTransNumber,
CONVERT(varchar(10),tar.ARRecordDate,101) as ARRecordDate,
tar.ArPatsNumber,
tar.OfficeNumber,
tar.ArUserID,
ISNULL(CONVERT(char(25),(SELECT MIN(appt.dt_appt)
FROM Datasync..tbl_appt appt
WHERE appt.stat_Cd = 0
and appt.OfficeNumber = tar.OfficeNumber
and appt.PatsNumber = tar.ARPatsNumber
and appt.dt_appt > GETDATE())),'') as NextApptDate,
iks.Note,
iks.Status1 as OriginCode,
iks.Status2 as StatusCode,
iks.Status3 as ActionCode
INTO #tmpNotes
FROM dbo.Notestable iks
INNER JOIN dbo.tAR tar
on tar.ARTransNumber = iks.ARTransNumber
WHERE tar.ARRecordDate > @StartDate
and tar.ARRecordDate < @EndDate
and tar.ArTransNumber in (select max(ArTransNumber)
from dbo.Notestable
group by case when ArReference = ''then
ArTransNumber else ArReference end)

-- Retrieve specific data from temp tables.
Select
ISNULL(emp.EmpFName+' '+emp.EmpLName,'') AS AccountManager,
iks.OfficeNumber,
addr.EntityCode,
iks.ARRecordDate,
iks.ARUserID as IKSRep,
CONVERT(varchar(25), pats.PatsLastName + ', ' + pats.PatsFirstName) AS 'Patient',
iks.NextApptDate,
iks.Note,
iks.StatusCode,
iks.ActionCode
FROM #tmpNotes iks
INNER JOIN dbo.tPats AS pats WITH (nolock)
ON pats.PatsNumber = iks.ARPatsNumber
AND pats.OfficeNumber = iks.OfficeNumber
INNER JOIN dbo.Addresses AS addr WITH (nolock)
ON addr.ACCOUNTCODE = iks.OfficeNumber
INNER JOIN dbo.ManagerOffice AS mo
ON mo.AddressID = addr.AddressID
INNER JOIN dbo.Employees AS emp
ON emp.EmpID = mo.ManagerID
where (iks.OriginCode Like 'OAR%'
or iks.OriginCode Like 'DAR%')
ORDER BY iks.AccountManager, iks.OriginCode, addr.ENTITYCODE

-- Dropt temp tables.
DROP TABLE #tmpNotes

END

The above store procedures work together just fine and I do get a report however, I cannot break the report out by the AccountManager field on seperate tabs. I've done other procedures with views and I could seperate the data out but these views don't have parameters inputs.

Thanks for the quick responses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources