Problem with "if" statement in Stored Proc

Hi All,

I'm haveing problems with a simple if statement within a stored proc. Here is a snippet of the stored proc

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id
IF @.industry = 31
BEGIN
AND industry = @.industry
END

The error message i get from enterprise manager is:

Error 156: Incorrect syntax near the keyword 'AND'

If i remove the if statement and select and select an 'industry' value other than 31 the it works fine.

Thanks

What exactly are you trying to do?|||

Ok, i have a job search function, that allows the users to filter the jobs by industry. This is done with an industry dropdown list. If the value selected is "All Industries" the value passed is '31', therefore i need to ignore the industry filter and output all the results (job) regardless of what industry they are under.

I originally had the following asp.net (c#) code:

if(Request.QueryString["industry"] != "31"){
SQL += " AND industry = @.industry";
prm = new SqlParameter("@.industry",SqlDbType.VarChar,50);
prm.Direction=ParameterDirection.Input;
prm.Value = Request.QueryString["industry"];
command.Parameters.Add(prm);
}

The above worked fine, but now i'm using a stored proc (becaues i have now implemented a paging function which utilises a stored proc). I could use a "if statement" in asp.net but if i don't pass any value for @.industry, the stored proc will throw an error.

Please let me know if you require further information.

Thanks

|||

Try something like this in your stored procedure:

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id AND (industry = @.industry OR @.industry <> 31)

|||

Thanks for your response Terri,

Unfortunately this seems to have the opposite result to what i was trying to achieve. It basically returns all results no matter what industry value is passed (used to filter results), unless the value "31" has been passed. When "31" is passed it outputs nothing (this is because there aren't any records have have an industry equal to "31").

|||

Give this a shot:

SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry = (CASEWHEN @.industry = 31then industryELSE @.industryEND)

|||

This will be more efficient for you (if you only have one or two of these "ALL" type queries):

IF @.industry=31BEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idENDELSEBEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry=@.industryENDAlthough you can also do:SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND (@.industry=31ORindustry=@.industry)

Which is also more efficient, because the comparison to the field is a fixed number, SQL Server can then use indexes more efficiently -- index range vs complete index scan.