mardi 16 juin 2015

Replace the following query in sqlite

I have a sql server query as follows in a store procedure with parameters @firstId and @secondId

IF @firstId = ''
begin

SELECT DISTINCT x, y =
FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.id AND t2.id = @secondId
ORDER BY t1.somecolumn

END
ELSE
BEGIN

SELECT DISTINCT x, y =
FROM t1
LEFT OUTER JOIN t2 ON t1.id = t2.id AND t2.id = @secondId
AND t1.id = @firstId
ORDER BY t1.somecolumn
END

I am very new to sqlite and trying to write the above query in sqlite with case statements as suggested in the sqlite documentation but so far not able to achieve the same. I want achieve something like below or a better query.

CASE WHEN @firstId = '' THEN (CHOOSE FIRST SELECT STATEMENT) ELSE (CHOOSE SECOND SELECT STATEMENT) END

I thought of splitting the original sql query into two select statements in sqlite and call respective query based on the condition handled in c# code base file as shown below but not sure if this is the good practice.

if(firstId == string.empty)
{
//call the first select statement
}
else
{
//call the second select statement
}

I don't want to split the store procedure into two separate queries. Any help is appreciated.

Aucun commentaire:

Enregistrer un commentaire