April 13, 2016 at 3:11 pm
I have a table with each row is assigned with a scalar function that returns a nvarchar value.
I have created a sqlfiddle to help understand my structure and data. I have tried various ways to get the output of the function stored in the last column but could not do.
Instead of
SELECT DashboardID, Name, FunctionName FROM tblDashboard
I'm looking for
SELECT DashboardID, Name, <output of function in FunctionName> FROM tblDashboard
I could think of achieving this using cursors but wanted to check with experts here as the last chance to check for other solutions.
April 13, 2016 at 3:18 pm
there's no information on the structure of your function. What does your function do?
Not sure what you mean by "executing functions from Table column"... The solution to this might be really simple, and you many not need a function at all - and if you don't have to use one, don't do it. Regular set-based T-SQL will run circles around scalar functions any day of the week.
Please explain what the input parameters to your function are, and how you're calculating the output. You may not need the function at all, or if you do, you could return a table-valued function or something similar...
April 13, 2016 at 3:58 pm
If you just want the static result of a function call to be inserted to the table, use INSERT INTO ... SELECT rather than INSET INTO ... VALUES:
INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)
SELECT 1, 'Function1', 'Function1', dbo.fnFunction1()
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2016 at 7:16 pm
sarath.tata (4/13/2016)
I have a table with each row is assigned with a scalar function that returns a nvarchar value.I have created a sqlfiddle to help understand my structure and data. I have tried various ways to get the output of the function stored in the last column but could not do.
Instead of
SELECT DashboardID, Name, FunctionName FROM tblDashboard
I'm looking for
SELECT DashboardID, Name, <output of function in FunctionName> FROM tblDashboard
I could think of achieving this using cursors but wanted to check with experts here as the last chance to check for other solutions.
I think you're talking about a "computed column". Here's one way to do it with the understanding that, when it comes to making changes in the future, this is the stuff of nightmares.
/*
DROP TABLE [dbo].[tblDashboard]
DROP FUNCTION [dbo].[fnFunction1],[dbo].[fnFunction2]
*/
Go
CREATE FUNCTION [dbo].[fnFunction1]
(
)
RETURNS VARCHAR(20) WITH SCHEMABINDING
AS
BEGIN
RETURN 'Function1'
END
GO
CREATE FUNCTION [dbo].[fnFunction2]
(
)
RETURNS VARCHAR(20) WITH SCHEMABINDING
AS
BEGIN
RETURN 'Function2'
END
GO
CREATE TABLE [dbo].[tblDashboard]
(
[DashboardID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Description] [nvarchar](2047) NOT NULL,
[FunctionName] AS (CASE WHEN Name = 'Function1' THEN dbo.fnFunction1() WHEN Name = 'Function2' THEN dbo.fnFunction2() ELSE NULL END) PERSISTED,
CONSTRAINT [PK_Dashboard]
PRIMARY KEY CLUSTERED ([DashboardID] 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
INSERT INTO tblDashboard (DashboardId, Name, Description)
VALUES (1, 'Function1', 'Function1')
GO
INSERT INTO tblDashboard (DashboardId, Name, Description)
VALUES (2, 'Function2', 'Function2')
GO
SELECT * FROM dbo.tblDashboard
I'll also strongly recommend against using "tbl" and "fn" and other forms of Hungarian Notation. For me personally, it's a "code smell".
Last but not least, learn how to post code on this forum. I went to SQLFiddle to help you but it's not just you that could benefit and there's no guarantee that your post there will persist forever. I believe you also find that people tend to shy away from links to other places.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2016 at 7:43 am
I may not have conveyed my question properly. This is how I achieved my solution. I understand my code references tbl and fn but I have to code like that as that is how the standards in my organization were setup though I'm personally not inclined to do that way.
CREATE FUNCTION [dbo].[fnFunction1]
(
)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN 'My Function1 Output'
END
GO
CREATE FUNCTION [dbo].[fnFunction2]
(
)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN 'My Function2 Output'
END
GO
CREATE FUNCTION [dbo].[fnFunctionWrapper] (@id INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @output VARCHAR(20)
IF @id = 1
set @output = dbo.fnFunction1()
ELSE IF @id = 2
set @output = dbo.fnFunction2()
return @output
END
GO
CREATE TABLE [dbo].[tblDashboard]
(
[DashboardID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Description] [nvarchar](2047) NOT NULL,
[FunctionName] [nvarchar](511) NULL,
CONSTRAINT [PK_Dashboard]
PRIMARY KEY CLUSTERED ([DashboardID] 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
INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)
VALUES (1, 'Function1', 'Function1', 'dbo.fnFunction1()')
GO
INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)
VALUES (2, 'Function2', 'Function2', 'dbo.fnFunction2()')
GO
SELECT DashboardID, Name, dbo.fnFunctionWrapper(DashboardID) FROM tblDashboard
April 14, 2016 at 7:51 am
UDFs (Scalar and Multi-statement Table Valued Functions) are UNBELIEVABLY BAD!!!! If you want some details, grab a copy of the SQL Server MVP Deep Dives 2 book (proceeds go to charity) and read my chapter entitled "Death by UDF". It's the best chapter in the book!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2016 at 9:42 am
sarath.tata (4/14/2016)
I may not have conveyed my question properly. This is how I achieved my solution. I understand my code references tbl and fn but I have to code like that as that is how the standards in my organization were setup though I'm personally not inclined to do that way.CREATE FUNCTION [dbo].[fnFunction1]
(
)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN 'My Function1 Output'
END
GO
CREATE FUNCTION [dbo].[fnFunction2]
(
)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN 'My Function2 Output'
END
GO
CREATE FUNCTION [dbo].[fnFunctionWrapper] (@id INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @output VARCHAR(20)
IF @id = 1
set @output = dbo.fnFunction1()
ELSE IF @id = 2
set @output = dbo.fnFunction2()
return @output
END
GO
CREATE TABLE [dbo].[tblDashboard]
(
[DashboardID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Description] [nvarchar](2047) NOT NULL,
[FunctionName] [nvarchar](511) NULL,
CONSTRAINT [PK_Dashboard]
PRIMARY KEY CLUSTERED ([DashboardID] 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
INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)
VALUES (1, 'Function1', 'Function1', 'dbo.fnFunction1()')
GO
INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)
VALUES (2, 'Function2', 'Function2', 'dbo.fnFunction2()')
GO
SELECT DashboardID, Name, dbo.fnFunctionWrapper(DashboardID) FROM tblDashboard
Ah... I see. Kevin is absolutely correct. Scalar and mTVF functions are really bad for performance and functions calling functions are the worst from many different aspects but particularly where performance comes into play.
iTVFs (INLINE table valued functions) are a whole lot better (usually at least 7 times better even for simple stuff). Please see the following article on how to simulate an iSF (high performance INLINE scalar function).
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2016 at 9:50 am
I totally agree with you. Initially they were all Stored Procedures. I changed them to functions as I can't really do EXEC in select query unless I write some complex logic of going through each row and capturing result in a temp table.
I understand this is a performance hit using Scalar functions. These functions have a complex logic built-in using / interacting with multiple tables and producing a HTML in varchar to render on UI.
I have decided to use this way as I will never have more than 10 rows in that table and not all of them would be executed at once as we have further filtering in that table to decide which user will have access to which Dashboard item.
April 14, 2016 at 10:03 am
sarath.tata (4/14/2016)
I totally agree with you. Initially they were all Stored Procedures. I changed them to functions as I can't really do EXEC in select query unless I write some complex logic of going through each row and capturing result in a temp table.I understand this is a performance hit using Scalar functions. These functions have a complex logic built-in using / interacting with multiple tables and producing a HTML in varchar to render on UI.
I have decided to use this way as I will never have more than 10 rows in that table and not all of them would be executed at once as we have further filtering in that table to decide which user will have access to which Dashboard item.
Do visit the article I linked to so that you can still take advantage of the functions like you want but with a whole lot better performance. Read the whole article so that you understand what a difference the simulated iSFs can make.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply