For the two date formats given the following will work:

-- set to european format.

SET DATEFORMAT dmy

DECLARE @t TABLE

(

CharDate char(10) NOT NULL

)

INSERT INTO @t

SELECT '31/12/2009' UNION ALL

SELECT '2009-06-30'

SELECT ISDATE(REPLACE(Chardate, '-', ''))...

With SQL2008 and the latest version of ADO you can even pass a table.

Without test data, and expected results, it is difficult to tell but maybe something like:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

CREATE PROCEDURE dbo.P_SRE_T_CAL

@CURDATE datetime,

@ERROR varchar(50) OUTPUT,

@TRANIDUP varchar(8000)...

Either use exception handling in the calling procedure or try the following:

--Run subproc

DECLARE @err int

EXEC @err = spNewSubProc @params

IF @err 0

GOTO OnError

Try the universal data format:

SELECT count(*)

,[date]

FROM tbl1

WHERE [date] BETWEEN '20090501' AND '20090531 23:59:59.997'

GROUP BY [date]

Difficult to tell without sample data and expected results, but you seem not to be restricting the subqueries

by the STVTERM_DESC. In this case I would forget about subqueries and just...

Logically the INNER JOIN is done first. ie The INNER JOIN is the nested join.

Maybe something like:

-- *** Test Data ***

DECLARE @t TABLE

(

Item int NOT NULL

,Sales_Date smalldatetime NOT NULL

,Quantity int NOT NULL

,Amount money NOT NULL

)

INSERT INTO @t

SELECT 1, '20090222', 1, 5.00 UNION ALL

SELECT 1,...

This is a nested join. To make it readable I would recommend that you always use brakets:

SELECT *

FROM dbo.demog dm

RIGHT JOIN

(

dbo.packlist pl

JOIN dbo.kittype kt

ON pl.kittype = kt.kittype

)

ON dm.siteid =...

You should check this yourself by looking at the query plans.

I suspect the plans will be the same as the optimizer should sort out the best access path.

You could try putting sp_releaseapplock in the CATCH block as well as the TRY block.

As you seem to be using SESSION applocks I think you would have difficulty is determining...

WHERE Title LIKE SearchText + '%'

AND [Description] LIKE SearchText+ '%'

AND link LIKE SearchText + '%'

Correct - the rollback stops the row being added.

Rolling back to a savepoint...

If you look...

Nice idea Mark.

This may be a slightly simpler way to catch the gap in RangeId

;WITH CTE AS (

SELECT RangeId,

CountryCode,

...