Getting Issue in conditional statement in procedure

[JayakumarEMIS]

Hi @bill-ramos-rmoswi, there is an issue in bebalfish, I have one procedure in that procedure and one condition check database exists and if exists i am getting data from the database, otherwise it execute else block, in my scenario i don't have databse and goes into else block, even though if condition not executed i am getting database not exists, but this works in sql kindly check and help me i added the query and results below

`CREATE PROCEDURE [dbo].[Test] AS

BEGIN if exists ( select 1 from sys.databases where name = 'payloaddb' ) begin SELECT * FROM payloaddb.dbo.Payload end; else begin select 1 end; END

EXEC [dbo].[Test] `

Sql Result:

Babefish Result:

[staticlibs]

@JayakumarEMIS

Yes, Babelfish checks for DB existence before considering if the branch is active. I've filed #71 to track this in WiltonDB.

Currently workaround is to use dynamic SQL for queries that refer to the DB that may not exist.

[JayakumarEMIS]

Thanks @staticlibs , can you please inform once the bug is fix, and also can you please help me to write sample dynamic query how the below query is workable

`CREATE PROCEDURE [dbo].[Test] AS

BEGIN if exists ( select 1 from sys.databases where name = 'payloaddb' ) begin SELECT * FROM payloaddb.dbo.Payload end; else begin select 1 end; END`

[staticlibs]

@JayakumarEMIS

Fix in WiltonDB 3.3 is not planned, there is limited support for cross-DB queries in that version, for example such queries are not supported at all in views and function.

Something like this should work:

CREATE PROCEDURE [dbo].[Test]
AS
BEGIN
if exists ( select 1 from sys.databases where name = 'payloaddb' )
begin
    exec('SELECT * FROM payloaddb.dbo.Payload')
end;
else
begin
    select 1
end;
END

[JayakumarEMIS]

Sure thanks @staticlibs, seems dynamic sql is working, this issue i found during one module testing, if the same issue arise other modules in my application, if occur i will apply this fix there also