Alternative for Exists ()

Question

Alternative for Exists ()

Navigation

#1 by (4 votes)
#2 by (1 votes)

4

I'm looking for an alternative to using the command Exists in procedures that I need to perform a large query and check if it returns some data, if it does not return to look for it should return a select with all its columns with the value '...' .

How am I doing at the moment:

IF EXISTS(SELECT ..... FROM ..... WHERE .....) --Consulta muito grande, por isso não escrive ela.
BEGIN
    SELECT ..... FROM ..... WHERE .....
END
ELSE
BEGIN
    SELECT '...' as 'Col1', ....
END

The problem with this approach is that I need to run the query twice, causing a certain slowness in the procedure.

sql-server

asked by anonymous 08.04.2015 / 22:13

2 answers

1

An alternative is to save the value of the time-consuming query and reuse it later. You can use this for Table Variable or Temp Table . This way, you query once and reuse the returned data saved in the variable or in the temporary table.

As you did not specify your query in detail, it is more difficult to pass the resolution of the problem with code, but see this example as it will help you write SQL: link

Of course, you'll have a higher disk space usage for this kind of solution, but it might be worth it for your slowness problem.

09.04.2015 / 00:09

How to remove a remote branch in Git? Passage of variables to the view in CodeIgniter

score 4 · Accepted Answer

I do not understand exactly how your process works, as there may be a better way, but some things that might help are:

When using EXISTS , you do not need to select any fields, you can SELECT 1 FROM ... WHERE ... and save some disk readings. For example:

SELECT 1 ... FROM ... WHERE ... .

Do not force the database to read all records, you can use TOP to limit the result, because for EXISTS a record is sufficient. For example:

SELECT TOP 1 1 FROM ... WHERE ...

The code above will return 1 if there is a record, or nothing if there is none.

If the idea is just to return something, you can try to return the original query and use the @@ROWCOUNT variable to check whether it worked or not. Example:

-- tenta retornar
SELECT ..... FROM ..... WHERE .....

-- verifica se algo foi retornado
IF @@ROWCOUNT = 0
BEGIN
     -- retorna default
     SELECT '...' as 'Col1', ....
END

Note: As mentioned by AP itself in comments, @@ROWCOUNT only works if the NOCOUNT parameter is disabled . In the case of a procedure, for example, there can not be the command SET NOCOUNT ON .