QUESTION WITH UDF & VARIABLES.

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 2008 Forums

Transact-SQL (2008)

QUESTION WITH UDF & VARIABLES.

Author

Topic

rutvij1984
Starting Member

7 Posts

Posted - 2010-08-19 : 16:04:37

--1). Calcute Sum of all the digits in the string.
I have written a UDF with the use of variables for this. Can anybody Execute this one & let me know the error.

GO
CREATE FUNCTION dbo.ADDITION (@I3 VARCHAR(10))
RETURNS INT
AS
BEGIN
DECLARE @I1 INT
DECLARE @I2 INT
SET @I1 = 1
SET @I2 = 0
WHILE @I1 <= LEN(@I3)
BEGIN
SET @I2 = @I2 + CONVERT(INT , SUBSTRING(@I3,1))
SET @I1 = @I1 + 1
END
GO

SELECT dbo.ADDITION ('1234567890')

rnj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 16:10:40

Add "RETURN @I" as a new line before the "END" line.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 16:14:07

And this is a much faster implementation

DECLARE	@Text VARCHAR(13) = '1234567890'

SELECT	SUM(i)
FROM	(
		VALUES	(0 + SUBSTRING(@Text,  1, 1)),
			(SUBSTRING(@Text,  2, 1)),
			(SUBSTRING(@Text,  3, 1)),
			(SUBSTRING(@Text,  4, 1)),
			(SUBSTRING(@Text,  5, 1)),
			(SUBSTRING(@Text,  6, 1)),
			(SUBSTRING(@Text,  7, 1)),
			(SUBSTRING(@Text,  8, 1)),
			(SUBSTRING(@Text,  9, 1)),
			(SUBSTRING(@Text, 10, 1)),
			(SUBSTRING(@Text, 11, 1)),
			(SUBSTRING(@Text, 12, 1)),
			(SUBSTRING(@Text, 13, 1))
	) AS c(i)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 16:16:49

[code]CREATE FUNCTION dbo.ADDITION
(
@Text VARCHAR(10)
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT SUM(i)
FROM (
VALUES (0 + SUBSTRING(@Text, 1, 1)),
(SUBSTRING(@Text, 2, 1)),
(SUBSTRING(@Text, 3, 1)),
(SUBSTRING(@Text, 4, 1)),
(SUBSTRING(@Text, 5, 1)),
(SUBSTRING(@Text, 6, 1)),
(SUBSTRING(@Text, 7, 1)),
(SUBSTRING(@Text, 8, 1)),
(SUBSTRING(@Text, 9, 1)),
(SUBSTRING(@Text, 10, 1))
) AS c(i)
)
END
GO

SELECT dbo.ADDITION('1234567890')[/code]

N 56°04'39.26"
E 12°55'05.63"

rutvij1984
Starting Member

7 Posts

Posted - 2010-08-19 : 16:24:05

Peso,
I added a return line just like RETURN @I1 before END line. but still the same problem.
can u write the whole executed query?
thanks

rnj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 16:28:41

[code]ALTER FUNCTION dbo.ADDITION (@I3 VARCHAR(10))
RETURNS INT
AS
BEGIN
DECLARE @I1 INT
DECLARE @I2 INT
SET @I1 = 1
SET @I2 = 0
WHILE @I1 <= LEN(@I3)
BEGIN
SET @I2 = @I2 + CONVERT(INT , SUBSTRING(@I3,@I1, 1))
SET @I1 = @I1 + 1
END
RETURN @I2
END
GO

SELECT dbo.ADDITION ('1234567890')[/code]Now try the speed of this one on a table with a million records, and then try my suggestion on same table. Time them both and post back the results here.

N 56°04'39.26"
E 12°55'05.63"

rutvij1984
Starting Member

7 Posts

Posted - 2010-08-19 : 16:37:00

Hey Peso,
Thnx a lot.
I saw the difference for a bigger records.

How about this?
Generate first 20 numbers is Fibonacci Sequence.
Example: 1 1 2 3 5 8 13 23 ...

rnj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 16:47:27

Google is your friend.
http://weblogs.sqlteam.com/peterl/archive/2009/08/26/Recursive-Fibonacci-number-calculation.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources