SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 UDF Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

172 Posts

Posted - 02/22/2013 :  23:10:50  Show Profile  Reply with Quote
Hi Guys,

I want to create UDF. Below is my logic. Please guide me where i am wrong.


Alter Function [dbo].[udf_GPList](@EID int,@PID int)




returns varchar(1000)
as

begin
Declare @Pist varchar(1000)=''
Declare @OID as varchar(2000) = ''
Declare @OTID as varchar(2000) = ''

SELECT
@OID = O.OId,
@OTID = O.OTId
From dbo.Order O
INNER JOIN dbo.PList PL ON PL.PId = O.PId
INNER JOIN dbo.Ens E ON E.EId = O.EId
WHERE PL.PId = @PID
AND E.EId = @Eid


SELECT
@Pist =
dbo.udfOrGetDetail (@OID,@OTID) +'<br />'
FROM dbo.PList TPL
INNER JOIN dbo.Ens E ON E.PId = TPL.PId
INNER JOIN dbo.Order O ON O.EId = E.EId
WHERE
E.EId = @EID
AND TPL.PId = @PID



Set @Pist= ISNULL(@Pist,'')

RETURN @Pist


END

Thanks in advance. If i am understand right. Function can return only one value. am i right?

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 02/22/2013 :  23:31:07  Show Profile  Reply with Quote
quote:
I want to create UDF. Below is my logic. Please guide me where i am wrong.

I don't now.

First, what is the issue here ? Can you explain what are you trying to do ? Any error when you create the function ?

Scalar function can only return one value, table function can return a result set


KH
Time is always against us

Go to Top of Page

tooba
Posting Yak Master

172 Posts

Posted - 02/23/2013 :  01:16:07  Show Profile  Reply with Quote
Thanks for your reply. There is no issue here. I am not getting any value. If i am getting multiple value should i create Table function?
Logic is simple if i enter Pid and EID i can get all other information. My question is, just for see, everything looks good to you?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2013 :  02:04:30  Show Profile  Reply with Quote
quote:
Originally posted by tooba

Thanks for your reply. There is no issue here. I am not getting any value. If i am getting multiple value should i create Table function?
Logic is simple if i enter Pid and EID i can get all other information. My question is, just for see everything looks good to you?


yes you need table valued udf for returning multiple values as a resultset

see

http://www.sqlteam.com/article/user-defined-functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

172 Posts

Posted - 02/23/2013 :  02:47:04  Show Profile  Reply with Quote
Thank for your reply,
Quick question,

If I am getting multiple value here

SELECT
@OID = O.OId,
@OTID = O.OTId
From dbo.Order O
INNER JOIN dbo.PList PL ON PL.PId = O.PId
INNER JOIN dbo.Ens E ON E.EId = O.EId
WHERE PL.PId = @PID
AND E.EId = @Eid

How its going to treat here?


SELECT
@Pist =
dbo.udfOrGetDetail (@OID,@OTID) +'<br />'
FROM dbo.PList TPL
INNER JOIN dbo.Ens E ON E.PId = TPL.PId
INNER JOIN dbo.Order O ON O.EId = E.EId
WHERE
E.EId = @EID
AND TPL.PId = @PID



Set @Pist= ISNULL(@Pist,'')

RETURN @Pist


END

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2013 :  03:24:12  Show Profile  Reply with Quote
it will assign a random value to @Pist as variable can store only a single value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

172 Posts

Posted - 02/23/2013 :  03:27:17  Show Profile  Reply with Quote
How i can handle this situation, if i receive multiple and i want to use each value and get all values....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2013 :  03:31:12  Show Profile  Reply with Quote
you need to make it a table valued UDF and call it just as a table in query


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

172 Posts

Posted - 02/23/2013 :  03:34:03  Show Profile  Reply with Quote
Could you please guide me how i can create a table valued UDF from below UDF. Please?



Alter Function [dbo].[udf_GPList](@EID int,@PID int)




returns varchar(1000)
as

begin
Declare @Pist varchar(1000)=''
Declare @OID as varchar(2000) = ''
Declare @OTID as varchar(2000) = ''

SELECT
@OID = O.OId,
@OTID = O.OTId
From dbo.Order O
INNER JOIN dbo.PList PL ON PL.PId = O.PId
INNER JOIN dbo.Ens E ON E.EId = O.EId
WHERE PL.PId = @PID
AND E.EId = @Eid


SELECT
@Pist =
dbo.udfOrGetDetail (@OID,@OTID) +'<br />'
FROM dbo.PList TPL
INNER JOIN dbo.Ens E ON E.PId = TPL.PId
INNER JOIN dbo.Order O ON O.EId = E.EId
WHERE
E.EId = @EID
AND TPL.PId = @PID



Set @Pist= ISNULL(@Pist,'')

RETURN @Pist


END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2013 :  03:40:37  Show Profile  Reply with Quote
first explain what dbo.udfOrGetDetail does

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tooba
Posting Yak Master

172 Posts

Posted - 02/23/2013 :  03:46:40  Show Profile  Reply with Quote
This UDF give me if i enter EID and PID i can get all Client and all Order against that client (I am using this udf (dbo.udfOrGetDetail (@OID,@OTID) to get Order Detail), Make Sense?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/24/2013 :  23:34:41  Show Profile  Reply with Quote
so is it returning single value or a resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000