Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to call a function returning a scaler (String) in a view. Expand / Collapse
Author
Message
Posted Wednesday, January 15, 2014 8:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:35 PM
Points: 9, Visits: 18
Hi,

I have created a function which is returns a Sql query as string [nvarchar(max)]
I want to execute this function using a view so that view will return a table .
Can any one provide me the syntax please?

Regards
Pooja Sharma
Post #1531375
Posted Wednesday, January 15, 2014 10:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 839, Visits: 5,399
Can you just create it as a table-valued function?

 CREATE FUNCTION [dbo].[GenerateCalendar] 
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp

GO



And then query it like a table?

SELECT *
FROM dbo.GenerateCalendar(GETDATE(),10);

Post #1531394
Posted Wednesday, January 15, 2014 10:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:35 PM
Points: 9, Visits: 18
Hi,

Actually my function is returning a dynamic query .


Step 1 : Create a function returning property names in CSV format
Create FUNCTION dbo.fxnGetPropertyColsQuery
(

)
RETURNS NVARCHAR(MAX)
AS
BEGIN

declare @Return NVARCHAR(MAX)
select @Return = STUFF((SELECT distinct ',' + QUOTENAME(c.propName)
FROM PropertyDef c where objecttypeid = 3
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
RETURN @return
End

Step 2: Create a function accepting return value from dbo.fxnGetPropertyColsQuery as a parameter

create FUNCTION dbo.fxnGetPropertiesQuery
(
@cols NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

declare @Return NVARCHAR(MAX)


select @Return =
'Select EquipmentNo,' + @cols + '
From
(
Select EquipmentNo,PropValue,PropName,PropertyDef.PropertyDefID
From PropertyDef Inner Join EPropertyData
ON PropertyDef.PropertyDefID=EPropertyData.PropertyDefID
INNER JOIN Equipment ON Equipment.EquipID=EPropertyData.ObjectID
WHERE ObjectTypeID In(3,4)
)AS SourceTable
PIVOT
(
max(PropValue)
For
PropName In (' + @cols + ')


)AS PivotTable;'

RETURN @return
End


I want to create a view executing function fxnGetPropertiesQuery returning table.
Post #1531395
Posted Wednesday, January 15, 2014 10:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
pietlinden (1/15/2014)
Can you just create it as a table-valued function?

 CREATE FUNCTION [dbo].[GenerateCalendar] 
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp

GO



And then query it like a table?

SELECT *
FROM dbo.GenerateCalendar(GETDATE(),10);



Technically that's a schema-bound, in-line table valued function (iTVF) and it looks mighty familiar!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531398
Posted Wednesday, January 15, 2014 10:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
pooja.sharma 54426 (1/15/2014)
Hi,

Actually my function is returning a dynamic query .


Step 1 : Create a function returning property names in CSV format
Create FUNCTION dbo.fxnGetPropertyColsQuery 
(

)
RETURNS NVARCHAR(MAX)
AS
BEGIN

declare @Return NVARCHAR(MAX)
select @Return = STUFF((SELECT distinct ',' + QUOTENAME(c.propName)
FROM PropertyDef c where objecttypeid = 3
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
RETURN @return
End


Step 2: Create a function accepting return value from dbo.fxnGetPropertyColsQuery as a parameter

create FUNCTION dbo.fxnGetPropertiesQuery
(
@cols NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

declare @Return NVARCHAR(MAX)


select @Return =
'Select EquipmentNo,' + @cols + '
From
(
Select EquipmentNo,PropValue,PropName,PropertyDef.PropertyDefID
From PropertyDef Inner Join EPropertyData
ON PropertyDef.PropertyDefID=EPropertyData.PropertyDefID
INNER JOIN Equipment ON Equipment.EquipID=EPropertyData.ObjectID
WHERE ObjectTypeID In(3,4)
)AS SourceTable
PIVOT
(
max(PropValue)
For
PropName In (' + @cols + ')


)AS PivotTable;'

RETURN @return
End


I want to create a view executing function fxnGetPropertiesQuery returning table.


I would say that using either of those functions in a view is going to be rather slow performing. It appears to me that both could be converted into iTVFs along the pattern pietlinden provided. You would need to call them differently (e.g., with a CROSS APPLY).

Edit: Put the SQL within sql code tags.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531399
Posted Wednesday, January 15, 2014 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:35 PM
Points: 9, Visits: 18
HI,

I am not getting how to use Table valued functions as columns that i need to fetch are not constants rather they are dynamic "' + @cols + '" .
They can be of any number.

Post #1531402
Posted Wednesday, January 15, 2014 11:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
pooja.sharma 54426 (1/15/2014)
HI,

I am not getting how to use Table valued functions as columns that i need to fetch are not constants rather they are dynamic "' + @cols + '" .
They can be of any number.



If you show the query that returns the values you want to pass into the FUNCTION as the @cols parameter, we might be able to offer you a revision of your scalar-valued UDF as an iTVF and its corresponding call signature.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531407
Posted Wednesday, January 15, 2014 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:35 PM
Points: 9, Visits: 18
declare @cols NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.propName)
FROM PropertyDef c where objecttypeid = 3
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
Post #1531408
Posted Wednesday, January 15, 2014 11:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
pooja.sharma 54426 (1/15/2014)
declare @cols NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.propName)
FROM PropertyDef c where objecttypeid = 3
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')


So you're saying that you're trying to pass @cols into the second function as a comma delimited list?

I'm thinking you don't need 2 functions at all. Can you provide DDL and some consumable sample data for your PropertyDef table?

With that I (or someone else if I'm not around) would surely be able to provide you some tested code.

You should also show us exactly how the final output list should look.

Note: You're thinking row by agonizing row (RBAR) here. You need to shift your thinking to set-based to get to a proper solution.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531413
Posted Thursday, January 16, 2014 2:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:35 PM
Points: 9, Visits: 18

Hi,

I have created a Sample DB back and screen shot for my expectation .
Please refer the attachments.

Regards
Pooja


  Post Attachments 
SampleProperty.zip (2 views, 111.30 KB)
ExpectedResult.png (4 views, 3.19 KB)
Post #1531458
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse