By Pramod P


2019-02-11 09:06:37 8 Comments

I am trying find fetch employee grade from employee table and set the value based on grade and return an integer value,but I get error in second line as incorrect syntax near returns I am trying to learn sql server

 create procedure checkgrade(@empid int)
    returns int
    as 
    begin
        declare @cgrade char(1)
        declare @rint int
            select @cgrade=grade from employee where @empid=empid
            if @cgrade='M' set @rint =1
            else if  @cgrade='L' set @rint=2
            else if @cgrade='T' set @rint=3
            else if @cgrade='X' set @rint= 4
            else set @rint=0
        return @rint
    end;

4 comments

@Alexander Volok 2019-02-11 09:14:30

CREATE PROCEDURE has slightly different syntax:

 create procedure checkgrade(@empid int)
    as
    begin
        declare @cgrade char(1)
        declare @rint int
            select @cgrade=grade from employee where @empid=empid
            if @cgrade='M' set @rint =1
            else if  @cgrade='L' set @rint=2
            else if @cgrade='T' set @rint=3
            else if @cgrade='X' set @rint= 4
            else set @rint=0
        return @rint
    end;

In fact, it can be that you need function instead of procedure as other members suggested

@S.Jose 2019-02-11 09:14:18

You can use output parameter to Output the result

create procedure checkgrade @empid int, @rint int OUTPUT   
    as 
    begin
        declare @cgrade char(1)

            select @cgrade=grade from employee where @empid=empid
            if @cgrade='M' set @rint =1
            else if  @cgrade='L' set @rint=2
            else if @cgrade='T' set @rint=3
            else if @cgrade='X' set @rint= 4
            else set @rint=0       
    end

@Pramod P 2019-02-11 09:19:10

i wanted to use function instead of procedure

@Mano 2019-02-11 09:10:53

You could use the Function instead of Procedure.

create Function checkgrade(@empid int)
    returns int
    as 
    begin
        declare @cgrade char(1)
        declare @rint int
            select @cgrade=grade from employee where @empid=empid
            if @cgrade='M' set @rint =1
            else if  @cgrade='L' set @rint=2
            else if @cgrade='T' set @rint=3
            else if @cgrade='X' set @rint= 4
            else set @rint=0
        return @rint
    end;

@JERRY 2019-02-11 09:10:37

You have used syntax of function in procedure.

create Function checkgrade(@empid int)
returns int
as 
begin
    declare @cgrade char(1)
    declare @rint int
        select @cgrade=grade from employee where @empid=empid
        if @cgrade='M' set @rint =1
        else if  @cgrade='L' set @rint=2
        else if @cgrade='T' set @rint=3
        else if @cgrade='X' set @rint= 4
        else set @rint=0

    return @rint
end;

Related Questions

Sponsored Content

8 Answered Questions

[SOLVED] How does database indexing work?

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

25 Answered Questions

3 Answered Questions

[SOLVED] SQL "Incorrect Syntax near 'IF' - cannot figure out issue

2 Answered Questions

[SOLVED] SQL - Incorrect syntax near ";"

Sponsored Content