Creating join in a fuction

Question

Creating join in a fuction

dsrapid

SSC-Addicted

Points: 485
More actions
September 7, 2011 at 8:09 am

#247431

Hi,
I have two tables with below structure:
Table A:
ID, FName, Age, City, Date
Table B:
id, Lname, State, Country, Date
I am creating a function and joining these two tables. I want to get the input from user in where condition for id and date.
I created the below but it is not working:
CREATE FUNCTION fn_name
(
@id int,
@date datetime
)
RETURNS TABLE
AS
RETURN
(
select * from table1 a JOIN table2 b
on (a.id = b.id
and a.date = b.date)
where (a.id = @id
and a.date = @date)
)
GO
Please help me on this..
Thanks in advane !!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Define 'not working'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

Do you get an error when you type

SELECT * FROM fn_name (1,'20110101')?

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

dsrapid SSC-Addicted Points: 485 More actions · Answer 3

I am getting the below error while creating the function:

Column names in each view or function must be unique. Column name 'id' in view or function 'fn_name' is specified more than once.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Don't use select * , specify the column names and, if you want to include both of the ID columns or both date columns you'll need to alias them

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dsrapid SSC-Addicted Points: 485 More actions · Answer 5

I am using column names instead of * still getting the error. Can you please provide me the sql.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

nidhi.ds.rapid (9/7/2011)
I am using column names instead of * still getting the error. Can you please provide me the sql.

Can you post the SQL which is causing the error you describe here?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dsrapid SSC-Addicted Points: 485 More actions · Answer 7

I catched the error.. Its solved.

Thanks all for the support !!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Also, it's much easier to write and test sql in SSMS then wrap it up in a function. Try this:

DECLARE @id int,

@date datetime

SET @id = ?

SET @date = ?

SELECT

a.ID,

a.FName,

b.Lname,

a.Age,

a.City,

b.State,

b.Country,

a.Date

FROM table1 a

INNER JOIN table2 b

on (a.id = b.id

and a.date = b.date)

WHERE (a.id = @id

and a.date = @date)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden