March 2009 Blog Posts
The scenario is very simple. You want to get all ID’s from one table that does not exists in another table.
This is how the two simple tables are set up.
DECLARE @Table1 TABLE
(
ID INT
)
INSERT @Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
DECLARE @Table2 TABLE
(
ID INT
)
INSERT @Table2
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION...
In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this:
DECLARE @String VARCHAR(MAX),
@Size TINYINT
SELECT @String = 'Hello my name is Jeff. I need some help on a project because ',
@String = @String + 'right now this is how the application i am working ',
@String = @String + 'with displays data.',
@Size = 32
SELECT 1 + Number AS Part,
SUBSTRING(@String, Number * @Size, @Size)
FROM master..spt_values
WHERE Type = 'P'...
DECLARE @Stats TABLE ( SomeDate DATETIME ) INSERT @Stats SELECT 20000 + ABS(CHECKSUM(NEWID())) % 30000 FROM master..spt_values DECLARE @Style INT ...
CREATE FUNCTION dbo.DATEDIFF360 ( @source DATETIME, @target DATETIME, @style BIT = 0 ) RETURNS INT AS BEGIN RETURN CASE @style -- European style WHEN 1 THEN CASE WHEN DATEPART(DAY, @target) = 31...
I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables.
CREATE FUNCTION dbo.fnErf
(
@z1 FLOAT,
@z2 FLOAT,
@MaxIterations TINYINT = 10
)
RETURNS FLOAT
AS
BEGIN
IF @z1 IS NULL OR @z2 IS NULL
RETURN NULL
DECLARE @n TINYINT,
@s1 FLOAT,
@s2 FLOAT,
@p1 FLOAT,
@p2 FLOAT,
@a1 FLOAT,
@a2 FLOAT
SELECT @n = 1,
@p1 = 1,
@p2 = 1,
@a1 = @z1,
@a2 = @z2,
@MaxIterations = COALESCE(ABS(@MaxIterations), 10)
WHILE @p1 <> 0.0E AND @p2 <> 0.0E AND @n <= @MaxIterations
SELECT @s1 = @z1 /(2.0E * @n + 1.0E),
@s2 = @z2 /(2.0E *...
This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with
SELECT dbo.fnErf(0.35, DEFAULT)
However, if you feel the need to more accuracy, replace second parameter with a value of your choice.
Here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone-for-two-variables.aspx is also a solution for two variables.
CREATE FUNCTION dbo.fnErf
(
@z FLOAT,
@MaxIterations TINYINT = 10
)
RETURNS FLOAT
AS
BEGIN
IF @z IS NULL
RETURN NULL
DECLARE @n TINYINT,
@s FLOAT,
@p FLOAT,
@a FLOAT
SELECT @n = 1,
@p = 1,
@a = @z,
@MaxIterations = COALESCE(ABS(@MaxIterations), 10)
WHILE @p <> 0.0E AND @n <= @MaxIterations
SELECT @s = @z /(2.0E * @n...
Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values.
I did some reasearch first to see which was the most common method to deal with this situation and not surprisingly the method of iterating all records from start to end was used.
That method is not efficient. What if you have 1 million records and there is only 1 gap at...
Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file.
I often use this to determine what kind of XML the file is by comparing the returned resultset with a lookup-table.
See comment in code to understand what happens
CREATE PROCEDURE dbo.uspGetFileStructureXML
(
@FileName NVARCHAR(256)
)
AS
-- Prevent unwanted resultsets back to client
SET NOCOUNT ON
-- Initialize...