You can often get away with one generic exception handler.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.ExceptionHandler

AS

SET NOCOUNT ON

DECLARE @ErrorNumber int = ERROR_NUMBER()

,@ErrorSeverity int = ERROR_SEVERITY()

,@ErrorState int = ERROR_STATE()

,@ErrorProcedure nvarchar(126) = ERROR_PROCEDURE()

,@ErrorLine...

Something like the following may be worth a try:

SELECT o_num

INTO #orders

FROM orders

WHERE o_type IN (9, 30)

AND trn_id IN (24, 45, 25, 0)

ALTER TABLE #orders

ADD PRIMARY KEY (o_num)

-- This assumes that...

The sp_executesql procedure works like a dynamic SP so you can only pass values, and not object names, as parameters.

To guard against SQL injection you can validate column names against...

Master.dbo.spt_values is alright for quick queries but for production code I would be inclinded to generate a Numbers table in the relevant DB.

WHERE N.type = 'P' AND N.number BETWEEN 0 AND 29

It is best to search on a range so that there are no functions on J.JobTimeStamp, making it SARGable:

-- either

SELECT DATEADD(d, DATEDIFF(d, 0, CURRENT_TIMESTAMP), - N.number) AS MissingDate

FROM [master].dbo.spt_values N

WHERE...

Given your table definition, this should generate some usable test data:

SELECT TOP (100)

'UNION ALL SELECT '

+ CAST(ID AS varchar(20)) + ' AS ID,'

+ CAST(MainID AS varchar(20)) + ' AS MainID,'

+...

C# Screw (11/5/2010)

---

Not Quite!

simply locking the row would have been fine.

This I could do in my FoxPro days, but in SQL it seems that at least a page locked.

For example...

Here is a CLR function which could be used in Sergiy's code:

http://www.pluralsight-training.net/community/blogs/dan/archive/2006/07/27/32597.aspx

or if the operators are simple enough, just use the XML capability.

WHERE ColumnA LIKE

CASE

WHEN @Type = 'TWO' THEN N'%' + N' 20-' + N'%'

ELSE N'%' + N' [3-4]0-' + N'%'

END

It is a very good plan...

The following gives an example of the sort of code you need to be careful with:

http://en.wikipedia.org/wiki/Snapshot_isolation

As a concrete example, imagine V1 and V2 are two balances held by a single...

While snapshot isolation can be very useful, you should google 'write skew anomolies', check your code and do a lot of testing.

The classic way around this is to add a nonclustered index for the FK check to use.

Something like the following should work:

ALTER TABLE test1

ADD CONSTRAINT uq_test1 UNIQUE NONCLUSTERED (a)

You can enforce this without triggers by...