Using Select inside function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Using Select inside function

Author

Topic

jeffki
Starting Member

1 Post

Posted - 2014-01-01 : 13:42:18

I am trying to return the slope of a line given a set of x/y coordinates passed in to a function as two comma delimited lists. I have the general solution, but I am getting 2 errors. One just says Syntax error near Select the other "Select statements included within a function cannot return data to a client. Can someone look through this and tell me how I get the slope calculated from the table variable returned to the caller?

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cudf_Test_Calculate_Simple_Linear_Regression_Model_Without_The_Intercept_Term]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.cudf_Test_Calculate_Simple_Linear_Regression_Model_Without_The_Intercept_Term
GO

CREATE FUNCTION cudf_Test_Calculate_Simple_Linear_Regression_Model_Without_The_Intercept_Term
(
@cXPoints VARCHAR(MAX)
,@cYPoints VARCHAR(MAX)
)

RETURNS FLOAT
AS
BEGIN

-- Declare the return variable here

DECLARE @Result FLOAT;

SET @Result = NULL;

set nocount on
declare @n int
declare @lr table (rw int not null, X real not null, Y real not null)

declare @idx int
declare @slice varchar(8000)
declare @rw int
set @rw = 0
declare @Delimiter char(1)
set @Delimiter =','

-- return if no input
select @idx = 1
if len(@cXPoints)<1 or @cXPoints is null return @Result

-- Place the x values into the temp table
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@cXPoints)
if @idx!=0
set @slice = left(@cXPoints,@idx - 1)
else
set @slice = @cXPoints

if(len(@slice)>0)
begin
insert into @lr (rw, X) values(@rw, CAST(@slice AS real) )
set @rw = @rw + 1
end
set @cXPoints = right(@cXPoints,len(@cXPoints) - @idx)
if len(@cXPoints) = 0 break
end

set @rw = 0

-- return if no input
select @idx = 1
if len(@cYPoints)<1 or @cYPoints is null return @Result

-- Place the y values into the temp table
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@cYPoints)
if @idx!=0
set @slice = left(@cYPoints,@idx - 1)
else
set @slice = @cYPoints

if(len(@slice)>0)
begin
update @lr set Y = CAST(@slice AS real) where rw = @rw
set @rw = @rw + 1
end
set @cYPoints = right(@cYPoints,len(@cYPoints) - @idx)
if len(@cYPoints) = 0 break
end

-- Calculate the result
-- ERRORS IN THE FOLLOWING TWO SELECT STATEMENTS
SET @n = Select Count(*) FROM @lr
@Result = Select
((@n * Sum(X*Y)) - (Sum(X) * Sum(Y)))/
((@n * Sum(X*X)) - (Sum(X) * Sum(X)))
FROM @lr

-- Return the result of the function

RETURN @Result;

END
GO

SELECT dbo.cudf_Test_Calculate_Simple_Linear_Regression_Model_Without_The_Intercept_Term('1,2,3','1,2,3') AS SLOPE -- Result = 1

SELECT dbo.cudf_Test_Calculate_Simple_Linear_Regression_Model_Without_The_Intercept_Term('1,2,3','3,2,1') AS SLOPE -- Result = -1

SELECT dbo.cudf_Test_Calculate_Simple_Linear_Regression_Model_Without_The_Intercept_Term ('1.47,1.50,1.52,1.55,1.57,1.60,1.63,1.65,1.68,1.70,1.73,1.75,1.78,1.80,1.83','52.21,53.12,54.48,55.84,57.20,58.57,59.93,61.29,63.11,64.47,66.28,68.10,69.92,72.19,74.46') AS SLOPE -- Result = 61.2721865421079

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-01 : 14:19:05

This?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77311

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources