T-SQL存储过程插入如果存在参数错误。

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

2 个解决方案

#1

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

更改代码以开始使用……块结束。

From IF...ELSE (Transact-SQL)

从如果…其他(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.

Boolean_expression是返回TRUE或FALSE的表达式。如果布尔表达式包含SELECT语句，则SELECT语句必须括在括号中。

{ sql_statement| statement_block }

{ 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.

使用语句块定义的任何Transact-SQL语句或语句分组。除非使用语句块，否则IF或ELSE条件会影响仅一个Transact-SQL语句的性能。

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

#2

Take a look here for how to use Begin and End with IF statement

看看这里如何使用开头和结尾。

#1