If Else Stored Procedure

garethtnash
garethtnash used Ask the Experts™
on
Hello Experts,

I'm trying to write an IF Else (select) stored procedure, that returns values based on one set of variables (if the values exist) but if not returns over values, I think you will see what I am trying to do below, any ideas how best to do this ?

thank you
CREATE PROCEDURE [dbo].[newdesignjobresults]
(
@JBASiteID integer,
@JBALocation varchar(255),
@JBACategory varchar(255),
@JBADescription varchar(255)
)
 
AS
BEGIN
IF EXISTS
    (
SELECT AD.JBAID,
        Lower(AD.JBATitle) AS JBATitle,
        AD.JBALocation,
        AD.JBACategory,
        AD.JBAPayRate,
        AD.JBADatePosted,
        Lower(left(AD.JBADescription, 300) + '...') as JBADescription,
        CL.JBCLName,
        CL.JBCLLogo,
        AV.ADViews,
        AP.Applications,
        1 AS Resultshown
 
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
      AND (@JBALocation Is Null Or JBALocation = @JBALocation)
      AND (@JBACategory Is Null Or JBACategory = @JBACategory) 
      AND (@JBADescription Is Null OR JBADescription LIKE '%' + @JBADescription + '%')
ORDER BY AD.JBADatePosted DESC
    )
    ELSE
(
SELECT AD.JBAID,
        Lower(AD.JBATitle) AS JBATitle,
        AD.JBALocation,
        AD.JBACategory,
        AD.JBAPayRate,
        AD.JBADatePosted,
        Lower(left(AD.JBADescription, 300) + '...') as JBADescription,
        CL.JBCLName,
        CL.JBCLLogo,
        AV.ADViews,
        AP.Applications,
        2 AS Resultshown
 
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
ORDER BY AD.JBADatePosted DESC
) 
END
 
GO

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try this one out:
CREATE PROCEDURE [dbo].[newdesignjobresults]
(
@JBASiteID integer,
@JBALocation varchar(255),
@JBACategory varchar(255),
@JBADescription varchar(255)
)
 
AS
BEGIN
IF (SELECT count(*)
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
      AND (@JBALocation Is Null Or JBALocation = @JBALocation)
      AND (@JBACategory Is Null Or JBACategory = @JBACategory) 
      AND (@JBADescription Is Null OR JBADescription LIKE '%' + @JBADescription + '%') ) > 0
 
SELECT AD.JBAID,
        Lower(AD.JBATitle) AS JBATitle,
        AD.JBALocation,
        AD.JBACategory,
        AD.JBAPayRate,
        AD.JBADatePosted,
        Lower(left(AD.JBADescription, 300) + '...') as JBADescription,
        CL.JBCLName,
        CL.JBCLLogo,
        AV.ADViews,
        AP.Applications,
        1 AS Resultshown
 
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
      AND (@JBALocation Is Null Or JBALocation = @JBALocation)
      AND (@JBACategory Is Null Or JBACategory = @JBACategory) 
      AND (@JBADescription Is Null OR JBADescription LIKE '%' + @JBADescription + '%')
ORDER BY AD.JBADatePosted DESC
 
    ELSE
 
SELECT AD.JBAID,
        Lower(AD.JBATitle) AS JBATitle,
        AD.JBALocation,
        AD.JBACategory,
        AD.JBAPayRate,
        AD.JBADatePosted,
        Lower(left(AD.JBADescription, 300) + '...') as JBADescription,
        CL.JBCLName,
        CL.JBCLLogo,
        AV.ADViews,
        AP.Applications,
        2 AS Resultshown
 
FROM dbo.JBAdvert AD
        left join (SELECT AP.JBAPAdvertID, COUNT(AP.JBAPID) Applications FROM dbo.JBApplication AP GROUP BY AP.JBAPAdvertID) AP on AV.VacancyID = AD.JBAID
        left join (SELECT AV.VacancyID, COUNT(AV.ID) ADViews FROM dbo.JBVacancyview AV GROUP BY AV.VacancyID) AV on AV.VacancyID = AD.JBAID
        inner join dbo.JBClient CL on CL.JBCLID = AD.JBAClientID
 
WHERE      JBASiteID = @JBASiteID 
      AND JBADatePosted >= DATEADD(d, -JBAPostFor, GETDATE())
ORDER BY AD.JBADatePosted DESC
 
END
 
GO

Open in new window

HainKurtSr. System Analyst

Commented:
what I see is this

CREATE PROCEDURE [dbo].[newdesignjobresults](...)
AS
BEGIN
  IF EXISTS (a query) ELSE (another query)
END

what the condition is checking?

HainKurtSr. System Analyst

Commented:
I think I got what you try to do :)

CREATE PROCEDURE [dbo].[newdesignjobresults](...)
AS
BEGIN
  a query
  if @@rowcount=0 then (other query)
END

what about this?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

HainKurtSr. System Analyst

Commented:
I guess above solution will  return 2 resultset if first query does not return anything...
output will be

Resultset of First query
or
Empty resultset, resultset of second query

if you can handle this situation in your application, this will be faster...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
HainKurt,
  Query in my comment earlier should work fine..

Author

Commented:
Thank you that has been really helpful and taught me something - thank you
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial