What does this...

At least use

SET @CurrencyAmountID = SCOPE_IDENTITY()

instead of

SET @CurrencyAmountID = @@IDENTITY

@@IDENTITY will give you the latest created identity value in the database, no matter which table or user created it. In...

Limit the number of RecordID to do the correlated subquery for.

As of now, you are grouping later and subquery first, for all RecordID

INSERT#TestResult

SELECTr.RecordID,

STUFF(f.Value, 1, 2, '') AS Value

FROM(

SELECTRecordID

FROM#Test

GROUP BYRecordID

) AS...

WHERE(Value1 LIKE @Param1 + '%' OR @Param1 IS NULL)

AND (Value2 LIKE @Param2 + '%' OR @Param2 IS NULL)

AND (Value3 LIKE @Param3 + '%' OR @Param3 IS NULL)

Can one of you gurus please test this. It's something I came up with today.

SET STATISTICS IO ON returns

Table '#619B8048'. Scan count 1, logical reads 1, physical reads 0, read-ahead...

DECLARE@Sample TABLE

(

ID INT NOT NULL

)

INSERT@Sample

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 9 UNION ALL

SELECT11

-- Get the missing ID's...

This is one way

DECLARE@Sample TABLE

(

PtID INT NOT NULL,

EntryDate DATETIME NOT NULL

)

INSERT@Sample

(

PtID,

EntryDate

)

VALUES(18271999, '2010-08-03 07:39'),

(18271999, '2010-08-03 07:40'),

(18271999, '2010-08-03 07:41'),

(18271999, '2010-08-03 07:41'),

(18271999, '2010-08-03 07:42'),

(18271999, '2010-08-03 07:43'),

(18271999, '2010-08-03 07:44'),

(18271999, '2010-08-03 07:45'),

(18271999, '2010-08-03 07:46'),

(18271999, '2010-08-03...

You can create a column with the IDENTITY attribute for DECIMAL(x, 0), SMALLINT, TINYINT, INT and BIGINT columns.

I apologize for posting a solution that was performance oriented.

Most people are interested in that.

Now I realize you wanted a more maintanable query at the expense of performance.

You know you posted your question in a SQL Server 2005 forum?

Perhaps the file size is more than 2GB?

Anyways, have you tried using SQLCLR for this?

Or, as an inline function

CREATE FUNCTION dbo.fn_JulianDateConversion

(

@JulianDate VARCHAR(13)

)

RETURNS DATETIME

AS

BEGIN

RETURN(

DATEADD(YEAR, LEFT(@JulianDate, 4) - 1900, SUBSTRING(@JulianDate, 5, 3) - 1)

+ STUFF(STUFF(RIGHT(@JulianDate, 6), 3, 0, ':'), 6, 0, ':')

)

END

GO

SELECT dbo.fn_JulianDateConversion('1997090101636')

Keep it simple...

DECLARE@Source CHAR(13) = '1997090101636'

SELECTDATEADD(YEAR, LEFT(@Source, 4) - 1900, SUBSTRING(@Source, 5, 3) - 1) + STUFF(STUFF(RIGHT(@Source, 6), 3, 0, ':'), 6, 0, ':')

See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx

How are you suggesting SQL Server builds the...