SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to call a function returning a scaler (String) in a view.


How to call a function returning a scaler (String) in a view.

Author
Message
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18267 Visits: 6431
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 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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
pooja.sharma 54426
pooja.sharma 54426
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64177 Visits: 17974
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18267 Visits: 6431
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 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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
pooja.sharma 54426
pooja.sharma 54426
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
Hi,

Please import the attached excel to get data.

Regards
Pooja Sharma
Attachments
SqlScript.xls (5 views, 14.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64177 Visits: 17974
pooja.sharma 54426 (1/16/2014)
Hi,

Please import the attached excel to get data.

Regards
Pooja Sharma


We don't want to download your excel file and then have to spend another 5-10 minutes importing data. We want to be able to copy and paste the insert statements that you create for us. Make it easy for us to work on your problem and you will find lots and lots of people helping.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
pooja.sharma 54426
pooja.sharma 54426
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
INSERT INTO [SampleProperty].[dbo].[Equipment]
([EquipmentNo]
,[Description]
,[EqType]
,[EquipTypeID]
,[ParentID])
VALUES
('E1'
,'TestE1'
,'Motor'
,1
,null)
GO
INSERT INTO [SampleProperty].[dbo].[Equipment]
([EquipmentNo]
,[Description]
,[EqType]
,[EquipTypeID]
,[ParentID])
VALUES
('E2'
,'TestE2'
,'Motor'
,1
,null)
GO
INSERT INTO [SampleProperty].[dbo].[Equipment]
([EquipmentNo]
,[Description]
,[EqType]
,[EquipTypeID]
,[ParentID])
VALUES
('E3'
,'testE3'
,'Heater'
,4
,null)
GO

INSERT INTO [SampleProperty].[dbo].[EquipmentTypes]
([EquipTypeID]
,[EquipmentType])
VALUES
(1
,'Motor1')
GO
INSERT INTO [SampleProperty].[dbo].[EquipmentTypes]
([EquipTypeID]
,[EquipmentType])
VALUES
(2
,'Busbar1')
GO
INSERT INTO [SampleProperty].[dbo].[EquipmentTypes]
([EquipTypeID]
,[EquipmentType])
VALUES
(3
,'Switchboard1')
GO
INSERT INTO [SampleProperty].[dbo].[EquipmentTypes]
([EquipTypeID]
,[EquipmentType])
VALUES
(4
,'Heater')
GO
INSERT INTO [SampleProperty].[dbo].[PropertyDef]
([PropName]
,[Caption]
,[ObjectTypeID])
VALUES
('Color'
,'Color'
,3)
GO
INSERT INTO [SampleProperty].[dbo].[PropertyDef]
([PropName]
,[Caption]
,[ObjectTypeID])
VALUES
('Height'
,'Height'
,3)
GO
INSERT INTO [SampleProperty].[dbo].[PropertyDef]
([PropName]
,[Caption]
,[ObjectTypeID])
VALUES
('Frequency'
,'Frequency'
,3)
GO
INSERT INTO [SampleProperty].[dbo].[EPropertyData]
([ObjectId]
,[PropertyDefID]
,[PropValue])
VALUES
(1
,1
,'Red')
GO

INSERT INTO [SampleProperty].[dbo].[EPropertyData]
([ObjectId]
,[PropertyDefID]
,[PropValue])
VALUES
(2
,1
,'Green')
Go
INSERT INTO [SampleProperty].[dbo].[EPropertyData]
([ObjectId]
,[PropertyDefID]
,[PropValue])
VALUES
(3
,3
,'12')
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64177 Visits: 17974
Thanks for the inserts. We are now almost a week into this problem and we have ddl and sample data. I know Dwain asked this already but what do you want for output? Can you give some insight as to what your query needs to do here?

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
pooja.sharma 54426
pooja.sharma 54426
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
please refer to my previous posts for the scenario in which i want outpt.
I have posted some functions which returns a sql query as string and i want to call this function in a stored procedure and execute the query returned in stored procedure.
the output required is attached as image file.

Regards
Pooja
Attachments
ExpectedResult.png (8 views, 3.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search