Author |
Topic |
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-22 : 23:10:50
|
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 ENDThanks in advance. If i am understand right. Function can return only one value. am i right? |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-22 : 23:31:07
|
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[spoiler]Time is always against us[/spoiler] |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-23 : 01:16:07
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-23 : 02:04:30
|
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 resultsetseehttp://www.sqlteam.com/article/user-defined-functions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-23 : 02:47:04
|
Thank for your reply, Quick question, If I am getting multiple value hereSELECT@OID = O.OId,@OTID = O.OTIdFrom dbo.Order OINNER JOIN dbo.PList PL ON PL.PId = O.PIdINNER JOIN dbo.Ens E ON E.EId = O.EIdWHERE PL.PId = @PIDAND E.EId = @EidHow its going to treat here?SELECT@Pist =dbo.udfOrGetDetail (@OID,@OTID) +'<br />'FROM dbo.PList TPLINNER JOIN dbo.Ens E ON E.PId = TPL.PIdINNER JOIN dbo.Order O ON O.EId = E.EIdWHEREE.EId = @EIDAND TPL.PId = @PIDSet @Pist= ISNULL(@Pist,'')RETURN @PistEND |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-23 : 03:24:12
|
it will assign a random value to @Pist as variable can store only a single value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-23 : 03:27:17
|
How i can handle this situation, if i receive multiple and i want to use each value and get all values.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-23 : 03:31:12
|
you need to make it a table valued UDF and call it just as a table in query------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-23 : 03:34:03
|
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)asbeginDeclare @Pist varchar(1000)=''Declare @OID as varchar(2000) = ''Declare @OTID as varchar(2000) = ''SELECT@OID = O.OId,@OTID = O.OTIdFrom dbo.Order OINNER JOIN dbo.PList PL ON PL.PId = O.PIdINNER JOIN dbo.Ens E ON E.EId = O.EIdWHERE PL.PId = @PIDAND E.EId = @EidSELECT@Pist =dbo.udfOrGetDetail (@OID,@OTID) +'<br />'FROM dbo.PList TPLINNER JOIN dbo.Ens E ON E.PId = TPL.PIdINNER JOIN dbo.Order O ON O.EId = E.EIdWHEREE.EId = @EIDAND TPL.PId = @PIDSet @Pist= ISNULL(@Pist,'')RETURN @PistEND |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-23 : 03:40:37
|
first explain what dbo.udfOrGetDetail does------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2013-02-23 : 03:46:40
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-24 : 23:34:41
|
so is it returning single value or a resultset?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|