|
[x]
Posted via EE Mobile
|
|
| Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again. |
|
|
|
|
Asked by RQuadling in SQL Server 2005, SQL Query Syntax, MS SQL Server
Hi.
I've got a DB creation script which requires some setup depending upon the server the user is using.
The script has several batches of commands (create procedure and views, etc.) which have to exist as the first entry in the batch.
What I am sort of lost is how do I quit the entire script when the required conditions aren't met.
Below is are some simplified examples of what I've tried.
How do I abort the current script - not the batch, the entire script.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
|
-- Example 1
DECLARE @i_MustNotBeNull int
-- You must uncomment the appropriate line
-- SELECT @i_MustNotBeNull = 1
-- SELECT @i_MustNotBeNull = 2
IF @i_MustNotBeNull IS NULL
RETURN
PRINT 'Never reached if @i_MustNotBeNull IS NULL'
GO
PRINT 'Should never be reached if @i_MustNotBeNull IS NULL - assume this is a CREATE PROCEDURE line and MUST be the first line in the batch'
GO
/* Outputs
Should never be reached if @i_MustNotBeNull IS NULL - assume this is a CREATE PROCEDURE line and MUST be the first line in the batch
*/
-- Example 2
BEGIN TRANSACTION
DECLARE @i_MustNotBeNull int
-- You must uncomment the appropriate line
-- SELECT @i_MustNotBeNull = 1
-- SELECT @i_MustNotBeNull = 2
SELECT @i_MustNotBeNull
IF @i_MustNotBeNull IS NULL
BEGIN
ROLLBACK TRANSACTION
RETURN
END
PRINT 'Never reached'
GO
PRINT 'Should never be reached if @i_MustNotBeNull IS NULL - assume this is a CREATE PROCEDURE line and MUST be the first line in the batch'
GO
COMMIT TRANSACTION
/* Outputs
Should never be reached if @i_MustNotBeNull IS NULL - assume this is a CREATE PROCEDURE line and MUST be the first line in the batch
Msg 3902, Level 16, State 1, Line 2
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
*/
|
20091118-EE-VQP-93 - Hierarchy / EE_QW_3_20080625
