mo.notono.us

Friday, May 28, 2004

SQL Server development techniques and sql scripts

Nigel Rivett has quite a collection of SQL Server development techniques and sql scripts. The sp_executeSQL tip came in handy when I had to build the following procedures:
----------------------------------------------------------------------------------------------------
--Created: Friday, May 28, 2004, by Oskar Austegard
--Purpose: Generic procedure to get the EditedTS for any given table and ID
----------------------------------------------------------------------------------------------------
ALTER  PROC ConcurrencyGetEditedTS 
	@TableName nvarchar(100),
	@IDName nvarchar(100),
	@ID int,
	@EditedTS datetime OUTPUT
AS
DECLARE @SQL nvarchar(250)
SET @SQL = N'SELECT @EditedTS = EditedTS FROM ' + @TableName
         + N' WHERE ' + @IDName + '=' + CONVERT(nvarchar(9), @ID)
EXEC sp_executesql @SQL, N'@EditedTS datetime OUTPUT', @EditedTS OUTPUT


----------------------------------------------------------------------------------------------------
--Created: Friday, May 28, 2004, by Oskar Austegard
--Purpose: Generic procedure to check for concurrency violation for any table and ID
----------------------------------------------------------------------------------------------------
ALTER   PROC ConcurrencyCheck
	@TableName nvarchar(100),
	@IDName nvarchar(100),
	@ID int,
	@EditedTS datetime
AS
DECLARE @ExistingTS datetime
EXEC ConcurrencyGetEditedTS @TableName, @IDName, @ID, @ExistingTS OUTPUT
IF @ExistingTS IS NULL
	RETURN -100
ELSE IF @ExistingTS <> @EditedTS
BEGIN
	RAISERROR ('The Edited Time Stamp for the %s record with %s=%d differs from that stored in the database.',
      16, 1, @TableName, @IDName, @ID)
	RETURN -999
END
RETURN @ID

Labels:

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home