January 15, 2014 at 8:42 pm
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
January 15, 2014 at 10:29 pm
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);
January 15, 2014 at 10:38 pm
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.
January 15, 2014 at 10:41 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2014 at 10:47 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2014 at 10:55 pm
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.
January 15, 2014 at 11:04 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2014 at 11:16 pm
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,'')
January 15, 2014 at 11:47 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2014 at 2:06 am
Hi,
I have created a Sample DB back and screen shot for my expectation .
Please refer the attachments.
Regards
Pooja
January 16, 2014 at 3:57 am
Sorry to tell you this but I don't open zip files from unknown sources (virus-phobia).
Post the SQL in line using the SQL code tag to make it pretty.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2014 at 4:09 am
Please create an empty database and run following sqls.
Add some data into them .
USE [SampleProperty]
GO
/****** Object: Table [dbo].[EPropertyData] Script Date: 01/16/2014 16:36:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EPropertyData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ObjectId] [int] NULL,
[PropertyDefID] [int] NULL,
[PropValue] [nvarchar](255) NULL,
CONSTRAINT [aaaaaEPropertyData2_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [SampleProperty]
GO
/****** Object: Table [dbo].[Equipment] Script Date: 01/16/2014 16:37:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Equipment](
[EquipId] [int] IDENTITY(1,1) NOT NULL,
[EquipmentNo] [nvarchar](200) NULL,
[Description] [nvarchar](110) NULL,
[EqType] [nvarchar](50) NULL,
[EquipTypeID] [int] NULL,
[ParentID] [int] NULL,
CONSTRAINT [aaaaaEquipment2_PK] PRIMARY KEY NONCLUSTERED
(
[EquipId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [SampleProperty]
GO
/****** Object: Table [dbo].[EquipmentTypes] Script Date: 01/16/2014 16:37:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EquipmentTypes](
[EquipTypeID] [int] NOT NULL,
[EquipmentType] [nvarchar](50) NULL,
CONSTRAINT [aaaaaEquipmentTypes2_PK] PRIMARY KEY NONCLUSTERED
(
[EquipTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [SampleProperty]
GO
/****** Object: Table [dbo].[PropertyDef] Script Date: 01/16/2014 16:38:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PropertyDef](
[PropertyDefID] [int] IDENTITY(1,1) NOT NULL,
[PropName] [nvarchar](100) NOT NULL,
[Caption] [nvarchar](100) NULL,
[ObjectTypeID] [int] NOT NULL,
CONSTRAINT [aaaaaPropertyDef2_PK] PRIMARY KEY NONCLUSTERED
(
[PropertyDefID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
January 16, 2014 at 7:48 am
pooja.sharma 54426 (1/16/2014)
Please create an empty database and run following sqls.Add some data into them .
Nice job posting the ddl. Can you post some data? Remember we are all volunteers around here and any time we spend generating insert statements is time we aren't working on your problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 16, 2014 at 5:32 pm
Sean Lange (1/16/2014)
pooja.sharma 54426 (1/16/2014)
Please create an empty database and run following sqls.Add some data into them .
Nice job posting the ddl. Can you post some data? Remember we are all volunteers around here and any time we spend generating insert statements is time we aren't working on your problem.
You took the words right outta my mouth Sean!
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2014 at 10:35 pm
Hi,
Please import the attached excel to get data.
Regards
Pooja Sharma
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply