How to set and return a variable in a Sybase stored procedure

Question

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!

score 2 · Accepted Answer · answered Sep 03 '15 at 10:40

2

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?

answered Sep 03 '15 at 10:40

theweeknd

277
1
12

That's what I wanted thanks. It's really confusing that you use the keyword 'SELECT' to both SET and - surprise - SELECT it.. – Sep 03 '15 at 11:05
2

You can also use SET for variable assignment. Here is it best explained: http://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables – theweeknd Sep 03 '15 at 11:08

Abe Crabtree · Answer 2 · 2015-09-03T15:36:27.373

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!

How to set and return a variable in a Sybase stored procedure

2 Answers2