CODEWITHSUNDEEP
sql-serverfunctionsql-server-2012
Blank EDjok
Blank EDjok

Reputation: 742

Using SQL Functions

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

Upvotes: 1

Views: 74

Answers (2)

M.Ali
M.Ali

Reputation: 69524

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);

Upvotes: 1

Ronan Thibaudau
Ronan Thibaudau

Reputation: 3603

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

Upvotes: 1

Related Questions