IF.. THEN Expressions in Design Grid "Column" Field

[Altemir]

I have a stored procedure where I want to evaluate values in a table
column using an expression in a second column.

For example, if the source table data contains:

MyOBJECT
--------
A
B
C
D


I want to write a stored procedure (or view ... I don't really
understand the difference) that will evaluate MyOBJECT and populate a
MyEXPRESSION local variable to produce the following recordset:

MyOBJECT MyEXPRESSION
---------- ----------------
A
B
C This is a C
D

In Access, I would use "=Iif(MyOBJECT='C','This is a C',NULL)" as an
expression in the second column of a query. How do you do it in ADP?

 

[Sylvain Lafontaine]

Use the CASE ... Statement:

Select Case When MyObject = 'C' Then 'This is C' Else Null End as
MyExpression, ...

Views are simple Select statements while stored procedures can be full
fledged procedures, with lot of code and other things. However, SP returns
DataRowSet, which are specialized objects ready to be sent over the wire to
the client; so you cannot write something like:

Select * from MyStoredProcedure ...

If you want to open a stored procedure a as local table or view in a Select
statement, then you must use a statement like OpenRowSet, OpenQuery or
OpenDataSource to convert the returned datarowset by the SP to a view.