Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 UDF Help

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


END

Thanks 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]

Go to Top of Page

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?
Go to Top of Page

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 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

224 Posts

Posted - 2013-02-23 : 02:47:04
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

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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....
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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)
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

52326 Posts

Posted - 2013-02-23 : 03:40:37
first explain what dbo.udfOrGetDetail does

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

Go to Top of Page

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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -