T-SQL Stored procedure insert if exists with out parameters error

Question

I have a problem that i do not seem to get fixed. The stored procedure throws an error when creating if I decomment the following part out of the full code:

-- SET @ResultType = 'Success'
-- SET @ResultMessage = 'Environment Created'

How can I solve this?

CREATE PROC [dbo].[usp_InsertEnvironment] 
    @EnvironmentName nvarchar(50),
    @EnvironmentDescription nvarchar(250),
    @ResultType as Nvarchar(50) OUTPUT,
    @ResultMessage as Nvarchar(250) OUTPUT

AS 
    --SET NOCOUNT ON 
    --SET XACT_ABORT ON  

BEGIN TRANSACTION

    IF  NOT EXISTS(SELECT * FROM dbo.Environment WHERE EnvironmentName = @EnvironmentName)
        --This environmentName does not exists, so insert...
        INSERT INTO dbo.Environment 
        SELECT  @EnvironmentName, @EnvironmentDescription

            -- I get an error if I decomment the 2 lines underneath :-(
        --SET @ResultType = 'Success'
        --SET @ResultMessage = 'Environment Created'

    ELSE
        --This environmentName does not exists so throw error

        SET @ResultType = 'Error'
        SET @ResultMessage = 'Environment already exists'

COMMIT

You need `BEGIN ... END` if you want multiple statements. [This code is not thread safe anyway](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Aug 14 '13 at 11:49

score 2 · Accepted Answer · answered Aug 14 '13 at 11:50

Change the code to use BEGIN...END blocks.

From IF...ELSE (Transact-SQL)

IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ]

Boolean_expression
Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{ sql_statement| statement_block }

Is any Transact-SQL statement or statement grouping as defined by using a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement.

To define a statement block, use the control-of-flow keywords BEGIN and END.

Something like

BEGIN TRANSACTION

    IF  NOT EXISTS(SELECT * FROM dbo.Environment WHERE EnvironmentName = @EnvironmentName)
    BEGIN
        --This environmentName does not exists, so insert...
        INSERT INTO dbo.Environment 
        SELECT  @EnvironmentName, @EnvironmentDescription

            -- I get an error if I decomment the 2 lines underneath :-(
        --SET @ResultType = 'Success'
        --SET @ResultMessage = 'Environment Created'
    END
    ELSE
    BEGIN
        --This environmentName does not exists so throw error

        SET @ResultType = 'Error'
        SET @ResultMessage = 'Environment already exists'
    END

COMMIT

T-SQL Stored procedure insert if exists with out parameters error

1 Answers1