Re: IF....THEN....ELSE

[jjlbmcnews]

Hi,

I have a stored procedure that depending on various conditions executes differently. Now I really can't get my head around how this will work, basically I have a standard SELECT query, if one or any of three conditions are true I want the select query to perform a JOIN to another table and add a few WHERE clauses. The stored procedure can be executed by parsing blank parameters so the standard SELECT query results are returned, if any of the parameters contain a value the query results another result set. Whats the best way to approach this? I was thinking of using IF....THEN....ELSE to get the results I want but I'm not sure if this is ideal.

 

[eramgarden]

can u use "case" statment?

 

[jjlbmcnews]

Hi,

Don't think I can as it basically looks like this -

DECLARE @Country INT
DECLARE @Language INT
DECLARE @PubType VARCHAR(1000)
DECLARE @CountryID INT
DECLARE @LanguageID INT

IF @Country <> 0 OR @Language <> 0 OR @PubType <> ''
BEGIN
SELECT Account AS 'BriefCode', Custom1 AS 'Status', COUNT(*)
FROM [dbo].[Distribution] AS D
LEFT JOIN [dbo].[ReadingSource] AS R ON D.PubID = R.iReadingSourceID
WHERE (Custom4 = ' ' OR Custom4 like '@Date%')
END
IF @Country > 0
BEGIN AND r.iCountryID = @CountryID
END
ELSE IF @CountryID < 0
BEGIN AND r.iCountryID <> 1
END
IF @Language > 0
BEGIN AND r.iLanguageID = @LanguageID
END
ELSE IF @Language < 0
BEGIN AND r.iLanguageID <> 1
END
IF @PubType <> ''
BEGIN AND r.iTypeID IN (@pubType)
END
ELSE
IF @Country = 0 OR @Language = 0 OR @PubType = ''
BEGIN
SELECT Account AS 'BriefCode', Custom1 AS 'Status', COUNT(*)
FROM [dbo].[Distribution] AS D
WHERE (Custom4 = ' ' OR Custom4 like '@Date%')
GROUP BY Account, Custom1
END

Can you see what I trying to do from this?