SQL Stored Procedure ? filtered with "if" Not sure how to do this.

Thanks for the help. I'll try that out.

Pat Shaw wrote:
> If I have got the correct angle on this (and I'm not sure I have), you could
> try using a CASE statement in your SELECT.
>
> SELECT col1, col2, CASE when D.paragraph is null then description else
> D.paragraph end as 'Description', col4 etc.
>
> I'm not sure I understand your requirement exactly, but I think this may
> help.
>
> Pat.
>
>
> "Lee" <lee_nospam_@artjunky.com> wrote in message
> news:fak7ab$7fa$1@forums.macromedia.com...
>> This query works but I want to expand on it. This one pulls in a
>> description for a product but I want it to check if there is a description
>> for that product and look in something else if there isn't. In other
>> words,
>>
>> If the D.paragraph that is pulled from G.pid is empty, I want it to get
>> the description ID from G.gid which is the product group it sits in.
>>
>> My problem stems from the fact that not all of my products have
>> descriptions; some of them do but many don't. Many rely on the group
>> description for information. As a patch, until I can add a description for
>> each, I want to be able to have the sql filter that on the server and give
>> me what I want. Below is the SQL that I use
>>
>> CREATE PROCEDURE nameproceedure(@blahblah int)
>> AS
>> SELECT P.ID AS ProductID, P.Name, P.ProdID, PC.gid AS PCgid, G.gid,
>> PC.cid, G.pid AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
>> FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN PCat
>> PC ON G.gid = PC.gid LEFT JOIN Description D ON G.pid = D.PID
>> WHERE P.ProdID = @itemdetail
>> ORDER BY G.gid, G.ord, P.ID, P.Name
>> GO
>>
>
>

If I have got the correct angle on this (and I'm not sure I have), you could
try using a CASE statement in your SELECT.

SELECT col1, col2, CASE when D.paragraph is null then description else
D.paragraph end as 'Description', col4 etc.

I'm not sure I understand your requirement exactly, but I think this may
help.

Pat.


"Lee" <lee_nospam_@artjunky.com> wrote in message
news:fak7ab$7fa$1@forums.macromedia.com...
> This query works but I want to expand on it. This one pulls in a
> description for a product but I want it to check if there is a description
> for that product and look in something else if there isn't. In other
> words,
>
> If the D.paragraph that is pulled from G.pid is empty, I want it to get
> the description ID from G.gid which is the product group it sits in.
>
> My problem stems from the fact that not all of my products have
> descriptions; some of them do but many don't. Many rely on the group
> description for information. As a patch, until I can add a description for
> each, I want to be able to have the sql filter that on the server and give
> me what I want. Below is the SQL that I use
>
> CREATE PROCEDURE nameproceedure(@blahblah int)
> AS
> SELECT P.ID AS ProductID, P.Name, P.ProdID, PC.gid AS PCgid, G.gid,
> PC.cid, G.pid AS Gpid, G.ord, D.PID AS DPID, D.Paragraph
> FROM atblProducts P INNER JOIN Groups G ON P.ID = G.pid INNER JOIN PCat
> PC ON G.gid = PC.gid LEFT JOIN Description D ON G.pid = D.PID
> WHERE P.ProdID = @itemdetail
> ORDER BY G.gid, G.ord, P.ID, P.Name
> GO
>