| Author |
 Topic  |
|
|
tooba
Posting Yak Master
106 Posts |
 Posted - 02/22/2013 : 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)
Singapore
16746 Posts |
Posted - 02/22/2013 : 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
Time is always against us
|
 |
|
|
tooba
Posting Yak Master
106 Posts |
Posted - 02/23/2013 : 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
India
47152 Posts |
Posted - 02/23/2013 : 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/
|
|
|
|
tooba
Posting Yak Master
106 Posts |
Posted - 02/23/2013 : 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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47152 Posts |
Posted - 02/23/2013 : 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/
|
|
|
|
tooba
Posting Yak Master
106 Posts |
Posted - 02/23/2013 : 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
India
47152 Posts |
Posted - 02/23/2013 : 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/
|
|
|
|
tooba
Posting Yak Master
106 Posts |
Posted - 02/23/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47152 Posts |
Posted - 02/23/2013 : 03:40:37
|
first explain what dbo.udfOrGetDetail does
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
tooba
Posting Yak Master
106 Posts |
Posted - 02/23/2013 : 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
India
47152 Posts |
Posted - 02/24/2013 : 23:34:41
|
so is it returning single value or a resultset?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
UDF Help
