Using SQL Functions

Question

Hello i am trying to create a function in ms sql server that accepts an integer parameter and returns a varchar. I am however having some trouble implementing this and would really love some help with this:

CREATE FUNCTION GetCategory (@CategoryID int)
RETURNS int 
AS
BEGIN
    DECLARE @Category varchar(64)

    @Category = (SELECT Category
    FROM Categories
    WHERE CategoryID = @CategoryID)

    RETURN @Category
END

The code above is what i tried doing already. I get the following error upon execution:=:

Msg 102, Level 15, State 1, Procedure GetCategory, Line 7
Incorrect syntax near '@Category'.
Msg 137, Level 15, State 2, Procedure GetCategory, Line 11
Must declare the scalar variable "@Category".

M.Ali · Accepted Answer · 2014-04-13 00:31:10Z

1

If Column CategoryID is a Primary key in your table and you are 100% sure there will be only ONE Category with a particualr CategoryID then your code is safe, but if it isnt a primary key and you can possibly have multiple Categories with same CategoryID then I would suggest using SELECT TOP 1 in your select statement.

CREATE FUNCTION GetCategory (@CategoryID int)
RETURNS varchar(64)        --<-- This needs to be the datatype you want to return
AS
BEGIN
    DECLARE @Category varchar(64);

    SELECT @Category =  Category
    FROM  Categories
    WHERE CategoryID = @CategoryID

    RETURN @Category;
END

Calling Function

SELECT dbo.GetCategory (1);

edited Apr 13, 2014 at 0:31

answered Apr 13, 2014 at 0:16

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Blank EDjok Over a year ago

Okay thank you that work but i am havin trouble calling the function now, i tried exec dbo.GetCategory(1) and it didn't work, i got "exec dbo.GetCategory(1)"

M.Ali Over a year ago

see my update how to call this function, EXEC or EXECUTE is used to call a stored Procedure , functions are simply used with SELECT statement.

Blank EDjok Over a year ago

Okay thank you that actually makes a lot of sense to me now, i understand it completely :)

Ronan Thibaudau · Accepted Answer · 2014-04-13 00:16:36Z

1

You say RETURNS int when actually you return a varchar 64. Also when asking a question and posting sample code of what doesn't work it helps if you say "what" doesn't work. (what error message you get etc).

answered Apr 13, 2014 at 0:16

Ronan Thibaudau

3,6013 gold badges34 silver badges79 bronze badges

Collectives™ on Stack Overflow

Using SQL Functions

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related