Returning Table Value in Function for Select Statement

[xcaliber2222]

Hello, I've used user defined functions but never to return a resultset (as you would a view) and then use that as part of a select statement.

Here is my select statement:

SELECT count(*) AS numberOfClaims 
FROM DIST_DEALER_XREF RIGHT JOIN Claim_Dist_Owner ON DIST_DEALER_XREF.DEALER_NUM = Claim_Dist_Owner.CD_DLR_NUM 
Where (Claim_Dist_Owner.CD_DIST_NUM='12345') OR (DIST_DEALER_XREF.DIST_NUM='12345') OR (Claim_Dist_Owner.CD_DIST_NUM='AB123') OR 
(DIST_DEALER_XREF.DIST_NUM='AB123')

I need to replace the DIST_DEALER_XREF with a function that uses this query which returns the same result set:

select 
	 convert(nvarchar(8),U.user_name) as DEALER_NUM, 
	 convert(nvarchar(6),d.value_string) as DIST_NUM,
	 ptd.Dealer_Name, 
	 ptd.Contact_name,
	 ptd.Address, 
	 ptd.City, 
	 ptd.state as ST, 
	 ptd.ZIP,
	 ptd.Country, 
	 ptd.Phone,
	 ptd.Phone_Toll_Free, 
	 ptd.Fax,
	 ptd.OEM_Service, 
	 ptd.Full_Service,
	 ptd.website as Web_Site_URL, 
	 ptd.Email,
	 ptd.show_on_dist_map as MAP_DISPLAY_FL
from  
	 TEST2.PT.dbo.dynamic_data_tbl D,
 	 TEST2.PT.dbo.users U,
 	 TEST2.PT.dbo.PTDealer PTD
where 
  	d.object_id = u.user_id
  	and u.user_name = ptd.company_id

I know I can create a function like so:

CREATE FUNCTION dbo.MyFunction
(
	@Param1, ... several parameters ...
)
RETURNS TABLE
AS
RETURN
	SELECT
		[KEY_ID] = MyID
	FROM	dbo.MyTable
	WHERE
		    (@Param1 IS NULL OR Col1 = @Param1)
		AND ... several more ...
GO

Then use it in my query like so:

SELECT TOP 500 Col1, Col2, ...
FROM	dbo.MyFunction(@Param1, ... )
	JOIN dbo.MyTable
		 ON MyID = KEY_ID

But in this case I'm having trouble trying to figure out how to actually get the query with the join to return the values properly. I believe with what I'm trying to do I don't even need to pass parameters to the function because I'm filtering the results in the final select statement.
I've tried several approaches but I think my syntax is way off. If someone could please show me the correct way to do this or a better approach completely I would really appreciate it.

Thank you,
Alejandro

 

[philhege]

If you're not passing parameters, you don't need a function, right?

You can treat a static SQL statement as a table in a join. You could even use parameters passed to the procedure in the statement. I don't think a function is your best choice.

Give us a sample data set and what you would expect as results from the query, and we can work it out.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[ArunPrithviraj]

SELECT count(*) AS numberOfClaims
FROM
(
select
convert(nvarchar(8),U.user_name) as DEALER_NUM,
convert(nvarchar(6),d.value_string) as DIST_NUM,
ptd.Dealer_Name,
ptd.Contact_name,
ptd.Address,
ptd.City,
ptd.state as ST,
ptd.ZIP,
ptd.Country,
ptd.Phone,
ptd.Phone_Toll_Free,
ptd.Fax,
ptd.OEM_Service,
ptd.Full_Service,
ptd.website as Web_Site_URL,
ptd.Email,
ptd.show_on_dist_map as MAP_DISPLAY_FL
from
TEST2.PT.dbo.dynamic_data_tbl D,
TEST2.PT.dbo.users U,
TEST2.PT.dbo.PTDealer PTD
where
d.object_id = u.user_id
and u.user_name = ptd.company_id
) AS DIST_DEALER_XREF RIGHT
RIGHT JOIN
Claim_Dist_Owner
ON
DIST_DEALER_XREF.DEALER_NUM = Claim_Dist_Owner.CD_DLR_NUM
Where
(Claim_Dist_Owner.CD_DIST_NUM='12345')
OR
(DIST_DEALER_XREF.DIST_NUM='12345')
OR
(Claim_Dist_Owner.CD_DIST_NUM='AB123')
OR
(DIST_DEALER_XREF.DIST_NUM='AB123')


Can you please try the above statement?
I believe this will help u.
I also don't think u need a function in ur case until unless u have repetative use of the above DIST_DEALER_XREF table.

In case you really want to use function due to some design reasons already committed as it may happen.
declare a function as u mentioned and then
while calling it call like:
main FROM
(Select dbo.Urfunction) AS DIST_DEALER_XREF
and use as u need.

Thanks,
AP
MCP, MCSD, MCSE, MCDBA & MCTS