Ufdjrw

Why is consensus so controversial in Britain?

Doing something right before you need it - expression for this?

How to draw the figure with four pentagons?

Neighboring nodes in the network

Why does Kotter return in Welcome Back Kotter

Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?

A reference to a well-known characterization of scattered compact spaces

Today is the Center

intersection of two sorted vectors in C++

How do conventional missiles fly?

Can one be a co-translator of a book, if he does not know the language that the book is translated into?

In Romance of the Three Kingdoms why do people still use bamboo sticks when papers are already invented?

Western buddy movie with a supernatural twist where a woman turns into an eagle at the end

Is "remove commented out code" correct English?

Why is the 'in' operator throwing an error with a string literal instead of logging false?

Is it legal for company to use my work email to pretend I still work there?

Is the Joker left-handed?

Python: return float 1.0 as int 1 but float 1.5 as float 1.5

AES: Why is it a good practice to use only the first 16bytes of a hash for encryption?

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

Watching something be written to a file live with tail

Does a druid starting with a bow start with no arrows?

Why is Collection not simply treated as Collection<?>

SSH "lag" in LAN on some machines, mixed distros

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

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

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.7k1478122

asked Mar 7 at 23:54

SidonSidon

6511717

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.7k1478122

asked Mar 7 at 23:54

SidonSidon

6511717

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.7k1478122

asked Mar 7 at 23:54

SidonSidon

6511717

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.7k1478122

asked Mar 7 at 23:54

SidonSidon

6511717

share|improve this question

edited Mar 8 at 15:01

Mark Rotteveel

61.7k1478122

asked Mar 7 at 23:54

SidonSidon

6511717

edited Mar 8 at 15:01

Mark Rotteveel

61.7k1478122

edited Mar 8 at 15:01

Mark Rotteveel

61.7k1478122

asked Mar 7 at 23:54

SidonSidon

6511717

asked Mar 7 at 23:54

SidonSidon

6511717

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

21.1k51028

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.7k1478122

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

21.1k51028

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

21.1k51028

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

21.1k51028

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

21.1k51028

answered Mar 8 at 0:47

GMBGMB

21.1k51028

answered Mar 8 at 0:47

GMBGMB

21.1k51028

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.7k1478122

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.7k1478122

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.7k1478122

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.7k1478122

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.7k1478122

answered Mar 8 at 15:07

Mark RotteveelMark Rotteveel

61.7k1478122