Ggtcf

Does an advisor owe his/her student anything? Will an advisor keep a PhD student only out of pity?

Do we have to expect a queue for the shuttle from Watford Junction to Harry Potter Studio?

Why can't the Brexit deadlock in the UK parliament be solved with a plurality vote?

What is the highest possible scrabble score for placing a single tile

Delete multiple columns using awk or sed

The IT department bottlenecks progress, how should I handle this?

A Trivial Diagnosis

awk assign to multiple variables at once

Is this toilet slogan correct usage of the English language?

Microchip documentation does not label CAN buss pins on micro controller pinout diagram

Why should universal income be universal?

What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?

Biological Blimps: Propulsion

Shouldn’t conservatives embrace universal basic income?

Will number of steps recorded on FitBit/any fitness tracker add up distance in PokemonGo?

How to convince somebody that he is fit for something else, but not this job?

PTIJ: Why is Haman obsessed with Bose?

How do I fix the group tension caused by my character stealing and possibly killing without provocation?

It grows, but water kills it

Strong empirical falsification of quantum mechanics based on vacuum energy density?

Doesn't the system of the Supreme Court oppose justice?

Short story about a deaf man, who cuts people tongues

What is the difference between lands and mana?

Non-trope happy ending?

Dynamic SQL (where) in Firebird stored procedure

How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerInsert results of a stored procedure into a temporary tableFunction vs. Stored Procedure in SQL ServerSQL Server - SELECT FROM stored procedureHow to check if a stored procedure exists before creating itHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableSearch text in stored procedure in SQL ServerConvert INT to VARCHAR SQL

1

I Have an SP that receive 2 parameters, P1 and P2, like this:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
 ...
 ...

And I need to generate the where clause based on the P2 parameter, like this:

if (P2=1) then
 where (state='SP' and situation='stopped') 
elseif (P2=2)
 where (state='MG' and situation='moving')

How to use this type of if statement in where clause?

sql stored-procedures firebird firebird2.5

share|improve this question

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

asked Mar 7 at 23:54

SidonSidon

6491716

add a comment | 

1

I Have an SP that receive 2 parameters, P1 and P2, like this:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
 ...
 ...

And I need to generate the where clause based on the P2 parameter, like this:

if (P2=1) then
 where (state='SP' and situation='stopped') 
elseif (P2=2)
 where (state='MG' and situation='moving')

How to use this type of if statement in where clause?

sql stored-procedures firebird firebird2.5

share|improve this question

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

asked Mar 7 at 23:54

SidonSidon

6491716

add a comment | 

I Have an SP that receive 2 parameters, P1 and P2, like this:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
 ...
 ...

And I need to generate the where clause based on the P2 parameter, like this:

if (P2=1) then
 where (state='SP' and situation='stopped') 
elseif (P2=2)
 where (state='MG' and situation='moving')

How to use this type of if statement in where clause?

sql stored-procedures firebird firebird2.5

share|improve this question

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

asked Mar 7 at 23:54

SidonSidon

6491716

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
 ...
 ...

if (P2=1) then
 where (state='SP' and situation='stopped') 
elseif (P2=2)
 where (state='MG' and situation='moving')

share|improve this question

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

asked Mar 7 at 23:54

SidonSidon

6491716

share|improve this question

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

asked Mar 7 at 23:54

SidonSidon

6491716

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

edited Mar 8 at 15:01

Mark Rotteveel

61.4k1478121

asked Mar 7 at 23:54

SidonSidon

6491716

asked Mar 7 at 23:54

SidonSidon

6491716

2 Answers
2

active

oldest

votes

2

To me your question translates as a simple OR condition in the WHERE clause of a SQL query:

WHERE
 (:P2 = 1 AND state='SP' and situation='stopped')
 OR (:P2 = 2 AND state='MG' and situation='moving')

share|improve this answer

answered Mar 8 at 0:47

GMBGMB

18.6k31028

add a comment | 

1

The answer of GMB will work fine for most situations, but in more complex cases it may have less desirable performance. An alternative solution would be to build a query string dynamically and execute it with execute statement:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
declare query varchar(2048);
begin
 query = 'select ......';
 if (p2 = 1) then
 query = query || ' where (state=''SP'' and situation=''stopped'')';
 else if (p2 = 2) then
 query = query || ' where (state=''MG'' and situation=''moving'')';

 -- if you expect a single result
 execute statement query into code, name, state, situation;

 -- OR

 -- for multiple results
 for execute statement query into code, name, state, situation do
 suspend;
end

share|improve this answer

edited Mar 8 at 16:00

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55054682%2fdynamic-sql-where-in-firebird-stored-procedure%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

To me your question translates as a simple OR condition in the WHERE clause of a SQL query:

WHERE
 (:P2 = 1 AND state='SP' and situation='stopped')
 OR (:P2 = 2 AND state='MG' and situation='moving')

share|improve this answer

answered Mar 8 at 0:47

GMBGMB

18.6k31028

add a comment | 

2

To me your question translates as a simple OR condition in the WHERE clause of a SQL query:

WHERE
 (:P2 = 1 AND state='SP' and situation='stopped')
 OR (:P2 = 2 AND state='MG' and situation='moving')

share|improve this answer

answered Mar 8 at 0:47

GMBGMB

18.6k31028

add a comment | 

To me your question translates as a simple OR condition in the WHERE clause of a SQL query:

WHERE
 (:P2 = 1 AND state='SP' and situation='stopped')
 OR (:P2 = 2 AND state='MG' and situation='moving')

share|improve this answer

answered Mar 8 at 0:47

GMBGMB

18.6k31028

WHERE
 (:P2 = 1 AND state='SP' and situation='stopped')
 OR (:P2 = 2 AND state='MG' and situation='moving')

share|improve this answer

answered Mar 8 at 0:47

GMBGMB

18.6k31028

answered Mar 8 at 0:47

GMBGMB

18.6k31028

answered Mar 8 at 0:47

GMBGMB

18.6k31028

1

The answer of GMB will work fine for most situations, but in more complex cases it may have less desirable performance. An alternative solution would be to build a query string dynamically and execute it with execute statement:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
declare query varchar(2048);
begin
 query = 'select ......';
 if (p2 = 1) then
 query = query || ' where (state=''SP'' and situation=''stopped'')';
 else if (p2 = 2) then
 query = query || ' where (state=''MG'' and situation=''moving'')';

 -- if you expect a single result
 execute statement query into code, name, state, situation;

 -- OR

 -- for multiple results
 for execute statement query into code, name, state, situation do
 suspend;
end

share|improve this answer

edited Mar 8 at 16:00

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121

add a comment | 

1

The answer of GMB will work fine for most situations, but in more complex cases it may have less desirable performance. An alternative solution would be to build a query string dynamically and execute it with execute statement:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
declare query varchar(2048);
begin
 query = 'select ......';
 if (p2 = 1) then
 query = query || ' where (state=''SP'' and situation=''stopped'')';
 else if (p2 = 2) then
 query = query || ' where (state=''MG'' and situation=''moving'')';

 -- if you expect a single result
 execute statement query into code, name, state, situation;

 -- OR

 -- for multiple results
 for execute statement query into code, name, state, situation do
 suspend;
end

share|improve this answer

edited Mar 8 at 16:00

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121

add a comment | 

The answer of GMB will work fine for most situations, but in more complex cases it may have less desirable performance. An alternative solution would be to build a query string dynamically and execute it with execute statement:

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
declare query varchar(2048);
begin
 query = 'select ......';
 if (p2 = 1) then
 query = query || ' where (state=''SP'' and situation=''stopped'')';
 else if (p2 = 2) then
 query = query || ' where (state=''MG'' and situation=''moving'')';

 -- if you expect a single result
 execute statement query into code, name, state, situation;

 -- OR

 -- for multiple results
 for execute statement query into code, name, state, situation do
 suspend;
end

share|improve this answer

edited Mar 8 at 16:00

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121

CREATE OR ALTER PROCEDURE MY_PROC (P1 varchar(10), P2 smallint = 1)
RETURNS (
 code VARCHAR(10),
 name VARCHAR(70),
 state VARCHAR(2),
 situation VARCHAR(20)
AS 
declare query varchar(2048);
begin
 query = 'select ......';
 if (p2 = 1) then
 query = query || ' where (state=''SP'' and situation=''stopped'')';
 else if (p2 = 2) then
 query = query || ' where (state=''MG'' and situation=''moving'')';

 -- if you expect a single result
 execute statement query into code, name, state, situation;

 -- OR

 -- for multiple results
 for execute statement query into code, name, state, situation do
 suspend;
end

share|improve this answer

edited Mar 8 at 16:00

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.4k1478121