Using parameterized function calls in SELECT statements. MS SQL Server
- by geekzlla
I have taken over some code from a previous developer and have come across this SQL statement that calls several SQL functions. As you can see, the function calls in the select statement pass a parameter to the function. How does the SQL statement know what value to replace the variable with? For the below sample, how does the query engine know what to replace nDeptID with when it calls, fn_SelDeptName_DeptID(nDeptID)?
nDeptID IS a column in table Note.
SELECT STATEMENT:
SELECT nCustomerID AS [Customer ID],
nJobID AS [Job ID],
dbo.fn_SelDeptName_DeptID(nDeptID) AS Department,
nJobTaskID AS JobTaskID,
dbo.fn_SelDeptTaskDesc_OpenTask(nJobID, nJobTaskID) AS Task,
nStandardNoteID AS StandardNoteID,
dbo.fn_SelNoteTypeDesc(nNoteID) AS [Note Type],
dbo.fn_SelGPAStandardNote(nStandardNoteID) AS [Standard Note],
nEntryDate AS [Entry Date],
nUserName as [Added By],
nType AS Type,
nNote AS Note FROM Note
WHERE nJobID = 844261
xORDER BY nJobID, Task, [Entry Date]
======================
Function fn_SelDeptName_DeptID:
ALTER FUNCTION [dbo].[fn_SelDeptName_DeptID] (@iDeptID int)
RETURNS varchar(25)
-- Used by DataCollection for Job Tracking
-- if the Deptartment isnt found return an empty string
BEGIN
-- Return the Department name for the given DeptID.
DECLARE @strDeptName varchar(25)
IF @iDeptID = 0
SET @strDeptName = ''
ELSE
BEGIN
SET @strDeptName = (SELECT dName FROM Department WHERE dDeptID = @iDeptID)
IF (@strDeptName IS NULL) SET @strDeptName = ''
END
RETURN @strDeptName
END
==========================
Thanks in advance.
