Q: How to set and return a variable in a Sybase stored procedure I've tried to read up on Sybase but the information that I'm getting is too unclear for me. The issue that I'm having is that I have to edit this 6000 line stored procedure in Sybase. Needless to say it's a complete nightmare, especially for someone who's strongest point isn't exactly SQL.
At the top of the procedure there's a variable declared (just using shorter variable names for the sake of this question):
DECLARE @MY_VARIABLE INT

The little SQL I'm familiar with is SQL Server, and Sybase is feeling quite a bit different. How exactly do you set a value to that variable? In SQL Server I'd image it would be something like this:
SET @MY_VARIABLE = 1

However I can't find any place in the procedure where something like this happens. I did find this though:
SELECT @MY_VARIABLE = convert(integer, Member_Number)
FROM ELECTRONIC_FORM NOHOLDLOCK

Is this how you set a variable in Sybase? With
SELECT @MY_VARIABLE = 2

for example?
Besides that, how do I return the value of the variable at the end of the procedure? I would've imagined it be something like this:
SELECT @MY_VARIABLE

And just make sure it's the last thing in the procedure but that doesn't seem to work. And it's looking quite similar to the way I have a feeling a variable actually gets set and not selected. I'm just really confused and lost here, thanks in advance for any help out there!

A: CREATE PROCEDURE RETURN_SELECT
AS BEGIN

    DECLARE @MY_VARIABLE int 
    SELECT @MY_VARIABLE = 2
    SELECT @MY_VARIABLE
END


EXEC RETURN_SELECT

The output would look like this:
@MY_VARIABLE
2

As simple as it gets, dont know if it helps, or you wanted something more?

A: Make sure you're clear whether you want to SELECT the variable as a result set returned to the client, or whether you'd do better with an OUTPUT parameter variable, which in theweeknd's answer would be:
CREATE PROCEDURE RETURN_SELECT
    @MY_VARIABLE int OUTPUT
AS BEGIN

    SELECT @MY_VARIABLE = 2
END

That might add to the confusion, I hope not.  I don't know how you're getting results but it might be important - one approach might be easier and better than the other.
This is a separate issue from the SET/SELECT discussion above.  Also when you SELECT a result set back to the client, you can do that anywhere in the SP, and you can even select more than one result set back!
