Want more features on Pastebin? Sign Up, it's FREE!
Guest

Patrick Ge Code

By: a guest on Aug 22nd, 2012  |  syntax: T-SQL  |  size: 1.12 KB  |  views: 30  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. DBCC FREEPROCCACHE;
  2. USE tempdb;
  3. GO
  4. CREATE PROCEDURE dbo.Demo
  5. AS
  6. BEGIN
  7.     CREATE TABLE #Demo (i int);
  8.  
  9.     SELECT
  10.         t.name,
  11.         t.object_id,
  12.         t.type_desc,
  13.         t.create_date
  14.     FROM
  15.         sys.tables AS t
  16.     WHERE
  17.         t.name LIKE N'#Demo%';
  18.  
  19.     IF EXISTS
  20.     (
  21.         SELECT *
  22.         FROM #Demo
  23.         WHERE i = 1
  24.     )
  25.     BEGIN
  26.         --NEVER HAPPENS
  27.         BEGIN TRANSACTION;
  28.  
  29.         UPDATE #Demo
  30.         SET i = i + 1;
  31.  
  32.         WAITFOR DELAY '00:00:05';
  33.        
  34.         COMMIT TRANSACTION;
  35.     END
  36.     ELSE
  37.     BEGIN
  38.         --BEGIN TRAN
  39.         INSERT  INTO #Demo
  40.         VALUES (1)
  41.         --WAITFOR DELAY '00:00:05';
  42.         --COMMIT
  43.     END;
  44.    
  45.     SELECT *
  46.     FROM #Demo;
  47.  
  48.     DROP TABLE #Demo;
  49.  
  50.     SELECT
  51.         t.name,
  52.         t.object_id,
  53.         t.type_desc,
  54.         t.create_date
  55.     FROM
  56.         sys.tables AS t
  57.     WHERE
  58.         t.name LIKE N'#________';
  59. END;
  60. GO
  61. --DBCC FREEPROCCACHE;
  62. --WAITFOR DELAY '00:00:05';
  63. --GO
  64. EXECUTE dbo.Demo;
  65. GO
  66. --DBCC FREEPROCCACHE;
  67. --WAITFOR DELAY '00:00:05';
  68. --GO
  69. EXECUTE dbo.Demo;
  70. GO
clone this paste RAW Paste Data