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

Report · Aug 23, 2007

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

Report · Aug 24, 2007

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
>

Report · Sep 05, 2007

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
>>
>
>