CODEWITHSUNDEEP
sqlsql-server-2012
petersondw10
petersondw10

Reputation: 3

Conditional Select Statement with if else

I'm trying to select data from a row and then using that value input another predefined data value into a variable. I've got this, not sure if it's the correct syntax for SQL though.

DECLARE @ID INT
DECLARE @server VARCHAR(15)

DECLARE response_cursor CURSOR
    FOR SELECT col1, col3, col4 FROM [dbo].[tbl] WHERE [status] <> 'Decommission'

IF col3 ='ABCD'
    THEN @server = 'Server1';
ELSIF col3 ='DEFS'
    THEN @server = 'Server2';
ELSIF col3 ='THSE'
    THEN @server = 'Server3';
...
ELSE col3 = NULL
    THEN @server = Null
END IF;

I get this when I try and run it:

Msg 156, Level 15, State 1, Line 9 Incorrect syntax near the keyword 'then'.

This is running on a MS SQL 2012 server

Here's the entire sql statement I'm working on; might help to better explain what I'm trying to do.

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_anotherprocedure]

as
begin


-- begin loop processing to get a response for each record in the validation     table

DECLARE @ID INT
DECLARE @server varchar(15)
DECLARE @port nvarchar(5)

declare response_cursor cursor
    for select ID, COL3, Assigned_Port  from [dbo].[tbl] where [Status] <>     'Decommission' -- we dont want to process decommissioned ports

    if COL3='123'
        begin @server = 'ABC'; end
    IF COL3='456'
        begin @server = 'DEF'; end
    IF COL3='789'
        begin @server = 'HIJ'; end
    ELSE COL3 = NULL
        begin @server = '' END


open response_cursor
fetch next from response_cursor  -- get the first record
INTO @ID, @SERVER, @PORT

WHILE @@FETCH_STATUS = 0
BEGIN
    -- make the  call
    DECLARE @RC int
    DECLARE @request nvarchar(100)
    DECLARE @out nvarchar(max)

   --set @server = 'xxx.xxx.xxx.xxx'   --ip address of the server
   --set @port = '101010'               --port of the server
   set @request = '"some_parmamet'  

    EXECUTE @RC = [dbo].[sp_stored] 
        @server
        ,@port
        ,@request
        ,@out output

    -- UPDATE THE RESPONSE FIELD FOR THE CURRENT RECORD WITH THE RESPONSE     FROM THE REQUEST
/*   UPDATE [dbo].[tbl]
        set response = @out
        where ID = @ID

    fetch next from response_cursor  -- get the next record and repeat  request
    into @ID, @SERVER, @PORT
*/
    Print @out
    END 

close response_cursor
deallocate response_cursor


end

Upvotes: 0

Views: 122

Answers (3)

logixologist
logixologist

Reputation: 3834

Theres a few issues here:

DECLARE @ID INT
DECLARE @server VARCHAR(15)

DECLARE response_cursor CURSOR
    FOR SELECT col1, col3, col4 FROM [dbo].[tbl] WHERE [status] <> 'Decommission'

For your first if

IF col3 ='ABCD'
    BEGIN SET @server = 'Server1'; END

There is no ELSE/IF IN SQL that I am aware of. 

IF col3 ='DEFS'
    BEGIN SET @server = 'Server2'; END

IF col3 ='THSE'
    BEGIN SET @server = 'Server3'; END

Yuu dont need an END IF. You do a BEGIN/END for each IF. Also if you need to do you can use ELSE

the structure is

IF something BEGIN dosomething END
ELSE
BEGIN doSomethingElse END

If you really need to use the IF/THEN/ELSE model, you can always do it using CASE

That structure is

SELECT CASE WHEN someCritera then dosomething
WHEN someCriteria then doSomething
...
--after your final when/then end with an END

For reference purposes

Upvotes: 1

Eric
Eric

Reputation: 3257

You can use CASE statement for a cleaner code.

SELECT @server = (
  CASE col3
    WHEN '123' THEN 'ABC'
    WHEN '456' THEN 'DEF'
    WHEN '789' THEN 'GHI'
    ELSE ''
  END)

Upvotes: 1

J Greene
J Greene

Reputation: 361

CASE
    WHEN col3 = 'ABCD' THEN 'Server1'
    WHEN col3 = 'DEFS' THEN 'Server2'
    WHEN col3 = 'THSE' THEN 'Server3'
ELSE NULL END as @server

Upvotes: 0

Related Questions