David Browne
3/8/2007 9:36:00 PM
"Jamie Risk" <risk.#.@intectus.com> wrote in message
news:OElqTTcYHHA.2316@TK2MSFTNGP04.phx.gbl...
>I have two tables, and a query that is used many times. My
> attempts at creating a function that returns the result of the
> query isn't working.
>
> How can I implment a function "fProtocol()" that will operate on
> the two tables below with the query I've shown?
>
> - Jamie
>
> CREATE TABLE Protocol (
> protocol_id INT PRIMARY KEY,
> notes VARCHAR(80) null
> );
>
> CREATE TABLE Array (
> array_id INT PRIMARY KEY,
> protocol_id INT FOREIGN KEY
> REFERENCES
> Protocol (protocol_id)
> ON DELETE CASCADE,
> data INT not null
> );
>
> -- Trying to do this:
> -- SELECT *
> -- FROM Protocol LEFT OUTER JOIN Array
> -- ON (Protocol.protocol_id = Array.protocol_id)
> CREATE FUNCTION fProtocol()
> RETURNS TABLE
> AS
> RETURN SELECT * FROM Protocol LEFT OUTER JOIN Array
> ON (Protocol.protocol_id = Array.protocol_id);
CREATE FUNCTION fProtocol()
RETURNS TABLE
AS
RETURN SELECT p.protocol_id, p.notes, a.array_id
FROM Protocol p
LEFT OUTER JOIN Array a
ON p.protocol_id = a.protocol_id;
David