使用 IF/ELSE IF 语句的奇怪错误 [英] Odd error using IF/ELSE IF statements

问题描述

我正在尝试根据场景参数的值创建一个临时表并使用以下 IF 语句，但出现以下错误:

 IF @indexName = 'A'begin select top 400 * into #temp from #pretemp order by EMRev desc end否则如果@indexName = 'B'begin select top 75 * into #temp from #pretemp order by EMRev desc end否则如果@indexName = 'C'begin select top 300 * into #temp from #pretemp order by EMRev desc end别的begin select top 100 * into #temp from #pretemp order by EMRev desc end

<块引用>

消息 2714，级别 16，状态 1，第 179 行数据库中已经有一个名为#temp"的对象.Msg 2714, Level 16, State 1, Line 181数据库中已经有一个名为#temp"的对象.Msg 2714, Level 16, State 1, Line 183数据库中已经有一个名为#temp"的对象.

我确定 IF 语句基于 @indexName 变量工作(用简单的东西替换块语句(例如，'select @indexName')，程序运行良好).

对导致此错误的原因有任何想法吗?

解决方案

因为只有前几条记录不同.你可以试试这个

声明@num intSET @num = CASE @indexName当 'A' 那么 400当 'B' 那么 75当 'C' 那么 300其他 100结尾select top (@num) * into #temp from #pretemp order by EMRev desc

I'm trying to create a temp table dependent on the value of a scenario parameter and using the following IF statement but getting the error below:

    IF @indexName = 'A'
        begin select top 400 * into #temp from #pretemp order by EMRev desc end
    ELSE IF @indexName = 'B'
        begin select top 75 * into #temp from #pretemp order by EMRev desc end
    ELSE IF @indexName = 'C'
        begin select top 300 * into #temp from #pretemp order by EMRev desc end
    ELSE 
        begin select top 100 * into #temp from #pretemp order by EMRev desc end

Msg 2714, Level 16, State 1, Line 179 There is already an object named '#temp' in the database. Msg 2714, Level 16, State 1, Line 181 There is already an object named '#temp' in the database. Msg 2714, Level 16, State 1, Line 183 There is already an object named '#temp' in the database.

I'm certain the IF statement works based on the @indexName variable (replacing the block statement with something simple (eg, 'select @indexName'), the program runs fine).

Any ideas about what's causing this error?

解决方案

Since only the top number of records are difference. You can try this

declare @num int 

SET @num = CASE @indexName 
                    WHEN 'A' THEN 400
                    WHEN 'B' THEN 75
                    WHEN 'C' THEN 300
                    ELSE  100
            END

select top (@num) * into #temp from #pretemp order by EMRev desc

这篇关于使用 IF/ELSE IF 语句的奇怪错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！