Assign value in integer returned from stored procedure + Sybase

Question

I am calling a stored procedure from another stored procedure. That stored procedure is returning an integer value always so I am accepting that value in one integer variable.

EXEC @IsBusinessDay  =  LiteIsWorkingDay @ExecutionStart

But even if stored procedure returning 1 value of the @IsBusinessDay is 0.

Code block

SELECT @ExecutionStart = CONVERT(VARCHAR, GETDATE(), 107)

EXEC @IsBusinessDay  =  LiteIsWorkingDay @ExecutionStart

IF(@IsBusinessDay = 0)
BEGIN
    IF(CONVERT(VARCHAR,@InterMediateStartDate,108) >  CONVERT(VARCHAR,GETDATE(),108))
    BEGIN   
        INSERT INTO TbJobQueue (JobId, ScheduleId, DueDate, Status, ExpiryDate, 
                                ProcessingDate, InputUser, InputTime, 
                                LastModifiedBy, LastModifiedTime)
        VALUES (@JobId, @ScheduleId,  @InterMediateStartDate,  'NQUE', NULL,
                NULL, 'Scheduler', GETDATE(), NULL, NULL)
    END
END

Please Help.

Thanks

Can you give some more information. What's in your stored procedures, ... Without more information, we can't help you — Sven, Sep 23 '11 at 08:51
I am inserting records with one store procedure. Now to check whether today's day is business day or not i am assigning value of today's date to @ExecutionStart and call the procedure LiteIsWorkingDay. — Denish, Sep 23 '11 at 09:01
This procedure will check in HolidayMaster that whether that day is working day or not. If working day then it returns 0 else it returns 1. so i am expecting value of 0 and 1 from that store procedure. That is giving proper result. even statement given above is giving proper result if called isolated. but when in Store proc value of IsBusinessDay remains 0 irrispactive of the return value. — Denish, Sep 23 '11 at 09:04

score 1 · Accepted Answer · answered Nov 11 '11 at 22:36

If you want the value of the stored procedure in a variable you have to make something like this:

Declare output parameter in the LiteIsWorkingDay stored procedure

create procedure LiteIsWorkingDay @ExecutionStart varchar(20), @IsBusinessDay int output
In LiteIsWorkingDay stored procedure you have to select a value for the @IsBusinessDay output parameter.
In the stored procedure that calls LiteIsWorkingDay you need to do something like this to get its value:

declare @ExecutionStart varchar(20) select @ExecutionStart = convert(varchar, getdate(), 107)

declare @IsBusinessDay int exec LiteIsWorkingDay, @IsBusinessDay output

And that's all. Now the variable @IsBusinessDay will have the value that you want :)

Assign value in integer returned from stored procedure + Sybase

1 Answers1