Executing function without select

Question

I have two functions- one to set a variable and one to view it. The idea is to use the variable in a View.

I'm really new to functions. Anyhoo, here's what they're supposed to do:

GetSiteId:

BEGIN
#Routine body goes here...
RETURN @siteContext;
END

SetSiteId:

BEGIN
    #Routine body goes here...
set @siteContext := SiteId;
    RETURN @siteContext;
END

This has an int parameter of SiteId.

But when I try to execute them, I get an error.

I execute them like so:

SetSiteId(1);
select GetSiteId();

If I perform a select on SetSiteId it works fine, but I don't want to see it, I only want to see the result of GetSiteId, which should be 1.

I thought maybe there was a specific keyword to execute the function, but I couldn't find any and Google was no help. I suspect I might be going about this all wrong, but I can't see how- it seems like it should be the simplest thing in the world to execute a function.

The oh-so-unhelpful error I get is this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SetSiteId(1)' at line 1

Answer 1

There are three basic kinds of stored code in MySQL:

1. Stored functions -- what you have
2. Stored procedures
3. Events (never mind these for now).

A function returns a value. Therefore it needs to be called from a context that can accept that value. Therefore, you can use

SET @something := YOUR_FUNCTION(param, param);

or

SELECT YOUR_FUNCTION(param, param)

A stored procedure returns a resultset (or, alternatively, no resultset). It's called with

 CALL YOUR_PROCEDURE(param, param)

So, you can call your first stored function like this:

 SET @junk := SetSiteId(1);

Or you can call it with SELECT and just ignore the result set.