FilipDeVos

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

IF EXISTS(SELECT * FROM updatetest WHERE id = @id)
  BEGIN
    waitfor delay '00:00:10'
    UPDATE updatetest 
    SET name = @name, 

FilipDeVos

exec updatevalues 7, 'Seven', 777

FilipDeVos

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

BEGIN TRANSACTION
IF EXISTS(SELECT * FROM updatetest (XLOCK, SERIALIZABLE) WHERE id = @id)
  BEGIN
    waitfor delay '00:00:10'
    UPDATE updatetest 

FilipDeVos

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'updatetest' and type = 'U')
 BEGIN
   PRINT 'Dropping table updatetest...'
   DROP TABLE dbo.updatetest
 END
PRINT 'Creating table updatetest...'
GO
CREATE TABLE updatetest 
  (
   id     int PRIMARY KEY, 

FilipDeVos

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

DECLARE @error int
      , @message sysname
      , @severity int
      , @state int
      , @retry bit = 0

FilipDeVos

CREATE DATABASE DefaultLocationDB

FilipDeVos

RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N'c:\backups\DemoDB.bak' WITH  FILE = 1,  
MOVE N'demo_data_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
MOVE N'demo_log_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
NOUNLOAD, REPLACE

FilipDeVos

IF object_id('dbo.fn_get_default_path') is not null 
  DROP FUNCTION dbo.fn_get_default_path 
GO 
CREATE FUNCTION dbo.fn_get_default_path(@log bit) 
  RETURNS nvarchar(260) 
AS 
  BEGIN 
    DECLARE @instance_name nvarchar(200)
          , @system_instance_name nvarchar(200)
          , @registry_key nvarchar(512)

FilipDeVos

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_path(0),
       @log = dbo.fn_get_default_path(1)

SELECT @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',  

FilipDeVos

SELECT object_name(id) AS TableName, * 
FROM dbo.sysindexes 
WHERE groupid = object_id('<yourfilegroup>')