how to return a cell into a variable in sql functions

Question

I want to define a scaler function which in that I'm going to return the result into a variable but I do not know how to do this.

CREATE FUNCTION dbo.Funname ( @param int )
RETURNS INT
AS
declare @returnvar int
select  @returnvar = select colname from tablename where someconditions = something
return(@returnvar)

I want to make a function something like the top. I mean the result of the select statement which is:

select colname from tablename where someconditions = something

Is only a single cell and we are sure about it. I want to store it into a variable and return it from the function. How can I implement this thing?

Martin Smith · Accepted Answer · 2010-09-11 12:59:33Z

3

I should probably mention that scalar UDFs do come with a considerable health warning and can cause performance issues depending upon how you use them.

Here's an example though.

CREATE FUNCTION dbo.Funname ( @param INT )
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN (SELECT number FROM master.dbo.spt_values WHERE number < @param)
END

In the above example I didn't use a variable as it is redundant. The version with variable is

BEGIN
DECLARE @Result int
SET @Result = (SELECT number FROM master.dbo.spt_values WHERE number < @param)
RETURN @Result
END

For both of the above you would need to be sure the Query returned at most one row to avoid an error at runtime. For example

select dbo.Funname(-1) Returns -32768

select dbo.Funname(0) Returns error "Subquery returned more than 1 value."

An alternative syntax would be

BEGIN
DECLARE @Result int
SELECT @Result = number FROM master.dbo.spt_values WHERE number < @param
RETURN @Result
END

This would no longer raise the error if the subquery returned more than one value but you would just end up with an arbitrary result with no warning - which is worse.

Following Comments I think this is what you need

CREATE FUNCTION dbo.getcustgrade(@custid CHAR(200)) 
RETURNS INT 
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
        RETURN
        ( SELECT [cust grade]
        FROM    ( SELECT  customerid,
                         DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS [cust grade]
                FROM     Orders
                GROUP BY CustomerID
                )
                d
        WHERE   customerid = @custid
        )
END

edited Sep 11, 2010 at 12:59

answered Sep 11, 2010 at 12:31

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

3 Comments

user435245 Over a year ago

i wrote this query in conclusion but it returns error in creating the funtion: CREATE FUNCTION dbo.getcustgrade ( @custid char(200) ) RETURNS INT WITH RETURNS NULL ON NULL INPUT AS begin return(select Orders.CustomerID, DENSE_RANK() OVER (ORDER BY count(*) desc) AS [cust grade] from Orders where customerid = @custid group by CustomerID ) end

user435245 Over a year ago

the error is: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Martin Smith Over a year ago

You can't return both Orders.CustomerID and the rank. You would just need to select the Rank. That query doesn't look right anyway.

Collectives™ on Stack Overflow

how to return a cell into a variable in sql functions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related