Local variables and select statements?

[MarkCulp]

If I use this in interactive it works:

declare @time1 time
declare @Time2 time
declare @time3 time

select 
    @time1 = t_time1,
    @Time2 = t_time2,
    @time3 = t_time3
from
    tblTimes 
select @time1, @Time2, @time3

But if I try to use it in a procedure I get error on the declares:

declare @time1 time;
declare @Time2 time;
declare @time3 time;

select 
    @time1 = t_time1,
    @Time2 = t_time2,
    @time3 = t_time3
from
    tblTimes;

So my question is how I do this in the best way in a procedure?

[MarkCulp]

Please show us the procedure declaration and the error that you are seeing.

[Former Member]

I get error message that the variables not exist. The declaration is what I showed in my question.

[Former Member]

Without a create procedure block around that?
No Begin End block either?

If so then dbisql will execute each statement separately and your defines will go out of scope on the very next statement.

[Former Member]

I do not know if I unclear or not. But what I mean is that I has a working procedure which I try to add this in the beginning of the procedure:

declare @time1 time;
declare @time2 time;
declare @time3 time;

select 
    @time1 = t_time1,
    @time2 = t_time2,
    @time3 = t_time3
from
    tblTimes;

My question was, why do I get the error message that the variables do not exist? Is it possible to do like this in a procedure?

[Breck_Carter · Accepted solution]

The "select @time1 = t_time1" is written using Transact SQL, so the procedure will have to be written using Transact SQL as well...

CREATE PROCEDURE p AS

declare @time1 time
declare @time2 time
declare @time3 time

select 
    @time1 = t_time1,
    @time2 = t_time2,
    @time3 = t_time3
from
    tblTimes 
select @time1, @time2, @time3
go

SELECT * FROM p()
go

If you want to use Watcom SQL (highly recommended!) then use the INTO clause...

CREATE PROCEDURE q()
BEGIN

declare @time1 time;
declare @time2 time;
declare @time3 time;

select 
    t_time1, t_time2, t_time3
into
    @time1, @time2, @time3
from
    tblTimes;

select 
    @time1, @time2, @time3;

END;
SELECT * FROM q();