selective sql request

[Sam]

Hi,
I've got the following Stored Procedure

CREATE PROCEDURE pr_Admin_GetCtrlFields
--Get the control fields, detail or flow according to the ModeID
parameter
@QueryID int,
@ModeID int
AS

SELECT FieldID, FieldName, EntryInfo, ControlTypeID,ViewFieldTypeID,
ViewTable, ViewField,
F.SortOrder, InsertField, UpdateField, FieldTabId, ModeId
FROM Fields F, Queries

WHERE F.QueryID = @QueryID
AND ControlTypeID <> NULL
AND ModeID = @ModeID
ORDER BY F.SortOrder ASC
GO
[Fields] and [Queries] are linked by a field called {QueryID}.
[Queries] contain a field called {RootTable}, and what I want to do is:

If ViewTable is NULL then I select {RootTable} as ViewTable, otherwise
I select ViewTable as I'm currentely doing it.

So how can I introduce this condition in my Select statement?

Thx

[David Portas]

COALESCE(viewtable, roottable) AS viewtable

--
David Portas
SQL Server MVP
--

[Sam]

Thx !

[Erland Sommarskog]

Sam (sa**************@voila.fr) writes:

Hi,
I've got the following Stored Procedure

CREATE PROCEDURE pr_Admin_GetCtrlFields
--Get the control fields, detail or flow according to the ModeID
parameter
@QueryID int,
@ModeID int
AS

SELECT FieldID, FieldName, EntryInfo, ControlTypeID,ViewFieldTypeID,
ViewTable, ViewField,
F.SortOrder, InsertField, UpdateField, FieldTabId, ModeId
FROM Fields F, Queries

WHERE F.QueryID = @QueryID
AND ControlTypeID <> NULL

Beware! This is most certainly not what you want. This condition
will never evaulate to TRUE. Use IS NOT NULL instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp