SQL Team Weblog issues? - 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

Site Related Forums

Site Related Discussions

SQL Team Weblog issues?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2007-02-19 : 04:45:31

Hi all,

Trying to post a response (admittedly a LONG one) on Brett's Weblog, and I tried twice on Friday - both times it looks like my IE 7 tabs died/hung (left on for the weekend for the 2nd one).

the attempted comments was (url of the weblog comment section prvodied):
--------------------------------------------------------------------
http://weblogs.sqlteam.com/brettk/archive/2007/02/12/60090.aspx#comment

Interesting that I should find your blog today, on a day that I did something quite similar. I needed to write a conversion routine for converting ISBN13 to ISBN10 values. I wrote it as a function so that we could simply re-use if the need came up again. I am not convinced that the function I wrote are necessarily the most efficient (please feel free to comment/improve) but they were being done quite quickly.

Having said the above, I did take the time (it was only 10 minutes anyway), to write the functions in 3 different methods - 1 using multiple sets, another using selects, and still another using selects, and using convert rather than cast. The upshot - on a 100000 iteration test of all 3 functions, the following results:

CodeTested OverallDurationMS AverageDurationMS
------------------------- ----------------- -----------------
ISBN13to10Conversion_1 106203 1.06203
ISBN13to10Conversion_2 106670 1.06670
ISBN13to10Conversion_3 115953 1.15953

For those interested:
the code (written for SQL 2000):
IF OBJECT_ID (N'dbo.ISBN13to10Conversion_1', N'FN') IS NOT NULL
DROP FUNCTION dbo.ISBN13to10Conversion_1;
GO
Create Function dbo.ISBN13to10Conversion_1 (@ISBN0 Char(13))
Returns Char(10)
as
begin
Declare @NewISBN varChar(10),@i int,@n int,@v INT,@P INT
Set @NewISBN = SubString(@ISBN0, 4, 9)
Set @n = 0
Set @i = 1
SET @p = 10
-- Loop
WHILE @i <= 9 BEGIN
Set @v = cast((SubString(@NewISBN, @i, 1)) as int)
SET @N = @N+(@p*@v)
Set @i = @i + 1
SET @P = @P - 1
End
Set @n = @n%11
SET @n = 11-@n
SET @n = @n%11
IF @N = 10 SET @NewISBN=@NewISBN + 'X'
ELSE set @NewISBN=@NewISBN + cast(@n as char(1))
Return (@NewISBN)
end
GO

IF OBJECT_ID (N'dbo.ISBN13to10Conversion', N'FN') IS NOT NULL
DROP FUNCTION dbo.ISBN13to10Conversion;
GO
Create Function dbo.ISBN13to10Conversion (@ISBN0 Char(13))
Returns Char(10)
as
begin
Declare @NewISBN varChar(10),@i int,@n int,@v INT,@P INT
SELECT @NewISBN = SubString(@ISBN0, 4, 9) ,@n = 0 , @i = 1,@p = 10
WHILE @i <= 9 BEGIN
SELECT @v = cast((SubString(@NewISBN, @i, 1)) as int),@N = @N+(@p*@v),@i = @i + 1 ,@P = @P - 1
End
SET @n = (11-(@n%11))%11
IF @N = 10 SET @NewISBN=@NewISBN + 'X'
ELSE set @NewISBN=@NewISBN + cast(@n as char(1))
Return (@NewISBN)
end
GO

IF OBJECT_ID (N'dbo.ISBN13to10Conversion_3', N'FN') IS NOT NULL
DROP FUNCTION dbo.ISBN13to10Conversion_3;
GO
Create Function dbo.ISBN13to10Conversion_3 (@ISBN0 Char(13))
Returns Char(10)
as
begin
Declare @NewISBN varChar(10),@i int,@n int,@v INT,@P INT
SELECT @NewISBN = SubString(@ISBN0, 4, 9) ,@n = 0 , @i = 1,@p = 10
WHILE @i <= 9 BEGIN
SELECT @v = convert(INT,(SubString(@NewISBN, @i, 1)) ),@N = @N+(@p*@v),@i = @i + 1 ,@P = @P - 1
End
SET @n = (11-(@n%11))%11
IF @N = 10 SET @NewISBN=@NewISBN + 'X'
ELSE set @NewISBN=@NewISBN + convert(CHAR(1),@n)
Return (@NewISBN)
end
GO

the test harnass (let me know what you think of the harnass as I would like to enhance/improve it where possible):
--################################################## COMMENTS ########################################################################################

--Created By: Regan Galbraith
--Created On: 2006-06-16

--Version: 0.1 - initial creation

--SQL Version: 2005 SP1.

--Overview:
-- This script is a generic load test harnass. It uses looping to test some SQL code iteratively, with DBCC DROPCLEANBUFFERS and FREEPROCCACHE to
-- ensure fair comparison.

--ToDo:
-- <ToDo>

--###########SELECT####################################### DECLARE & SET VARIABLES #########################################################################
--###################################################### describe script #############################################################################
DECLARE @RunDate DATETIME
SET @RunDate = CURRENT_TIMESTAMP
PRINT '>>> This script LOAD TESTS the following Stored Procedures: <<<'
PRINT ' >>> Name of Code testing Versions versions <<<'
PRINT '>>> This execution on server: ['+@@SERVERNAME+'] started at: ['+CONVERT(VARCHAR,@RunDate,113)+'] <<<'
PRINT ''
--
SET NOCOUNT ON
DECLARE
@LoopsToDo INT,
@LoopCounter INT,
@StartDatetime DATETIME,
@EndDatetime DATETIME,
@CodeTested VARCHAR(100),
@ReturnCode INT,
@DebugLevel TINYINT

DECLARE
@DurationResult TABLE
( UID INT IDENTITY(1,1),
CodeTested VARCHAR(100),
OverallDurationMS INT ,
AverageDurationMS DECIMAL(15,5))

IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE [Name] = '##TestData')
DROP TABLE ##TestData
CREATE TABLE ##TestData
( RuleText NVARCHAR(MAX))

DECLARE
@RuleSetText NVARCHAR(MAX),
@SQLImportCMD VARCHAR(2048),
@RuleFilePath VARCHAR(255),
@RuleSetToken NCHAR(13),
@CalculateToken DATETIME

--################################################## Set Global Variables ############################################################################
SET @LoopsToDo = 100000
SET @LoopCounter = 0
-- 0 = display no variables, 1 = display table changes, 2 = display table changes + variables
SET @DebugLevel = 0
SET @CalculateToken = DATEADD(DAY,2,CURRENT_TIMESTAMP)

IF (@DebugLevel > 1) SELECT @CalculateToken
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> BEGIN TEST CODE BLOCK<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--Declare, Set and infrom begin of CODE BLOCK
-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code
SET @CodeTested = 'ISBN13to10Conversion_1'
PRINT '--- TESTING CODE BLOCK : ['+@CodeTested+']'

--Set Local variables
-- This will be necessary for a specific block, and may be necessary for several blocks of code.
-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,
-- as far as possible, be done outside the measuring zone.

IF (@DebugLevel > 1)

--################################################## Execute Code Start ##############################################################################
--################################################## Clear Caches ####################################################################################
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
--prepare for loop
SET @LoopCounter = 0
------------------------------------------------------------Measuring Zone START
SET @StartDatetime = CURRENT_TIMESTAMP --Work Starting

WHILE @LoopCounter < @LoopsToDo BEGIN
SET @LoopCounter = @LoopCounter + 1
SELECT 9780196392554,dbo.ISBN13to10Conversion_1 ('9780196392554')
print @CodeTested
END
SET @EndDatetime = CURRENT_TIMESTAMP --Work Done

------------------------------------------------------------Measuring Zone END
-- log Duration
INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )
SELECT @CodeTested,
DATEDIFF(ms,@StartDatetime,@EndDatetime),
CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))
--################################################## Execute Code Finish #############################################################################
--report of data changed

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> END TEST CODE BLOCK <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> BEGIN TEST CODE BLOCK<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--Declare, Set and infrom begin of CODE BLOCK
-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code
SET @CodeTested = 'ISBN13to10Conversion_2'
PRINT '--- TESTING CODE BLOCK : ['+@CodeTested+']'

--Set Local variables
-- This will be necessary for a specific block, and may be necessary for several blocks of code.
-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,
-- as far as possible, be done outside the measuring zone.

--################################################## Execute Code Start ##############################################################################
--################################################## Clear Caches ####################################################################################
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
--prepare for loop
SET @LoopCounter = 0
------------------------------------------------------------Measuring Zone START
SET @StartDatetime = CURRENT_TIMESTAMP --Work Starting

WHILE @LoopCounter < @LoopsToDo BEGIN
SET @LoopCounter = @LoopCounter + 1
SELECT dbo.ISBN13to10Conversion_2 ('9780196392554')
print @CodeTested
END
SET @EndDatetime = CURRENT_TIMESTAMP --Work Done

------------------------------------------------------------Measuring Zone END
-- log Duration
INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )
SELECT @CodeTested,
DATEDIFF(ms,@StartDatetime,@EndDatetime),
CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))
--################################################## Execute Code Finish #############################################################################
--report of data changed

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> END TEST CODE BLOCK <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> BEGIN TEST CODE BLOCK<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
--Declare, Set and infrom begin of CODE BLOCK
-- Name the section of Code you are testing. This is Specifically useful when doing comparitive testing of several version of code
SET @CodeTested = 'ISBN13to10Conversion_3'
PRINT '--- TESTING CODE BLOCK : ['+@CodeTested+']'

--Set Local variables
-- This will be necessary for a specific block, and may be necessary for several blocks of code.
-- The principle when testing several pieces of code is that we want to use the same variables, and that creation of variables, values etc. should,
-- as far as possible, be done outside the measuring zone.

--################################################## Execute Code Start ##############################################################################
--################################################## Clear Caches ####################################################################################
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
--prepare for loop
SET @LoopCounter = 0
------------------------------------------------------------Measuring Zone START
SET @StartDatetime = CURRENT_TIMESTAMP --Work Starting

WHILE @LoopCounter < @LoopsToDo BEGIN
SET @LoopCounter = @LoopCounter + 1
SELECT dbo.ISBN13to10Conversion_3 ('9780196392554')
print @CodeTested
END
SET @EndDatetime = CURRENT_TIMESTAMP --Work Done

------------------------------------------------------------Measuring Zone END
-- log Duration
INSERT INTO @DurationResult (CodeTested, OverallDurationMS, AverageDurationMS )
SELECT @CodeTested,
DATEDIFF(ms,@StartDatetime,@EndDatetime),
CONVERT(DECIMAL(15,5),((CONVERT(DECIMAL(15,5),DATEDIFF(ms,@StartDatetime,@EndDatetime)))/@LoopCounter))
--################################################## Execute Code Finish #############################################################################
--report of data changed

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> END TEST CODE BLOCK <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

--###################################################### end script ##################################################################################
--Report on script results and duration
SELECT CodeTested, OverallDurationMS, AverageDurationMS FROM @DurationResult ORDER BY AverageDurationMS ASC
PRINT ''
PRINT '>>> This execution on server: ['+@@SERVERNAME+'] completed at: ['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
PRINT '>>> The execution duration was: ['+CONVERT(VARCHAR(10),DATEDIFF(ms,CONVERT(VARCHAR,@RunDate,113),CURRENT_TIMESTAMP))+'] Milliseconds <<<'
--------------------------------------------------------------------

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-19 : 06:01:03

[code]CREATE FUNCTION dbo.fnISBN13_to_ISBN10
(
@ISBN CHAR(13)
)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @Value INT,
@Index TINYINT

SET @Index = 4

WHILE @Index <= 12
SELECT @Value = (14 - @Index) * SUBSTRING(@ISBN, @Index, 1),
@Index = @Index + 1

SET @Value = 11 - @Value % 11

IF @Value = 10
SET @ISBN = SUBSTRING(@ISBN, 4, 9) + 'x'
ELSE
SET @ISBN = SUBSTRING(@ISBN, 4, 9) + CAST(@Value AS VARCHAR)

RETURN @ISBN
END[/code]

Peter Larsson
Helsingborg, Sweden

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2007-02-19 : 06:11:06

Thanks for the response, Peso. I'll add that to the test base.

any ideas on the weblog side?

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-02-19 : 06:34:48

Hmmm. No idea on the weblog. I'll do some testing with very large comments.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

JoeUser
Starting Member

2 Posts

Posted - 2007-03-21 : 21:39:21

Hi,

Sorry for bringing up an old thread, but I was wondering if you had a reverse function for code posted? (ISBN10 to 13)

Thanks

-Joe

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2007-03-22 : 05:41:51

Joe, yes I do - I had a request about 1 week after the above was required. The reverse function is very similar:


Create Function [dbo].[ISBN13to10Conversion] (@ISBN0 Char(13)) 
Returns Char(10)
as
begin
    Declare @NewISBN varChar(10),@i int,@n int,@v INT,@P INT
    SELECT @NewISBN = SubString(@ISBN0, 4, 9) ,@n = 0 , @i = 1,@p = 10
    WHILE @i <= 9 BEGIN
		SELECT @v = cast((SubString(@NewISBN, @i, 1)) as int),@N = @N+(@p*@v),@i = @i + 1 ,@P = @P - 1
    End
	SET		@n = (11-(@n%11))%11
	IF		@N = 10 SET @NewISBN=@NewISBN + 'X'
	ELSE			set @NewISBN=@NewISBN + cast(@n as char(1))
    Return (@NewISBN)
end

as always with things from the WEB, Test to ensure it works for you, and no warranties etc. etc.

Hope it helps you

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

JoeUser
Starting Member

2 Posts

Posted - 2007-03-22 : 11:16:56

Thank you Wanderer,

Unfortunately I was looking for 10to13, that one is 13to10.

You wouldn't happen to have a 10 to 13 example would you?

Strangely, there's a real lack of examples on the web for something like that in TSQL.(I'm really new at SQL functions too, so the math, string manipulation, and looping well...throws me for a loop)

Thank you for your help.

-Joe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources