[RESOLVED] Transact sql question!!!

[MrNorth]

I have the following sql query

SELECT A, B, C, (SELECT E FROM TABLE_Y WHERE D=@A AND E=@B and F=@C) AS TABLE_Y_DATA FROM TABLE_X WHERE A=@A AND B=@B and C=@C

result:

A B C TABLE_Y_DATA
----------------------------------
1 4 55 HYDSA
77 3 22 OGYITY

etc

Note that you only get ONE hit with the sub query

Now, the tricky part is that parameters a, b and c can be * from the application (they are numbers so they are by default set to -1 which equals *

How can I write this as a stored procedure where I take into account the parameters a, b and c? I mean, if A=77, B=-1 and C=42 then the B-statements should not be used. Please note that the database is NOT designed with -1 in mind. That is only on application level.

Im really stuck here!! Perhaps there is an easier way to write the sql query than then one I made?

/Henrik

[brucevde]

Try something like

Where
(A=@A Or @A=-1) And (B=@B Or @B=-1) and (C=@C Or @C=-1)

[anguswalker]

What if the default was NULL rather than -1. Then you could use:

SELECT A, B, C, (SELECT E FROM TABLE_Y WHERE D=ISNULL(@A,D) AND E=ISNULL(@B,E) and F=ISNULL(@C,F)) AS TABLE_Y_DATA FROM TABLE_X WHERE A=ISNULL(@A,A) AND B=ISNULL(@B,B) and C=ISNULL(@C,C)

[MrNorth]

Thank you, you saved my day!

Do you know of any good transact sql tutorials online? Im a pl/sql guy but now I have started working a lot with sql server... the syntax is a bit different

/Henrik

[si_the_geek]

I would recommend using Books Online - the help system for SQL Server.

If you have Enterprise Manager or Query Analyser installed, you should also have this installed too.

(if you don't, these can all be installed by inserting the SQL Server CD and selecting 'Client tools').