asked on

If in a select statement - MS SQL

Hello,

I'm building a stored procedure, that creates a directory tree for a URL, it is quite a complicated SP which should return 4 values per row returned these being -

Adverts,
Sector,
SectorID,
URL

The URL part is the complicated part --

CREATE PROCEDURE [dbo].[JobsearchSectorDescNew2](
@region int,
@location int,
@sector int,
@hours int,
@jobtype int,
@clientid int,
@keywords nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN

IF @keywords IS NOT NULL
BEGIN

DECLARE @MyQuote varchar(10)
DECLARE @MyWildCard varchar(5)

SET @MyQuote = '"'
SET @MyWildCard = '*'
SET @keywords = @MyQuote + @keywords + @MyWildCard + @MyQuote

select COUNT(JBAID) Adverts,
JBACategory Sector,
cat.ID SectorID,
'/jobs/' + dbo.fixchars(C.Name) + '/' + dbo.fixchars(JBACategory) + '/' + dbo.fixchars(S.JBSRegion) + '/' 
+ dbo.fixchars(L.JBLocation) + '/' + dbo.fixchars(H.Hourstype) + '/' + dbo.fixchars(A.JBAEmplymentType) + '/' as URL
FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID
inner join dbo.HoursType H on H.ID = A.[Hours]
inner join dbo.EmploymentType E on E.etype = A.JBAEmplymentType

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(E.ID = @jobtype or @jobtype IS NULL) 
AND			(C.ID= @clientid or @clientid is null) 
And		CONTAINS(JBADescription, @keywords)

Group by JBACategory, cat.ID
order by Adverts desc
END
ELSE
BEGIN
select COUNT(JBAID) Adverts,
JBACategory Sector,
cat.ID SectorID,
'/jobs/' + dbo.fixchars(C.Name) + '/' + dbo.fixchars(JBACategory) + '/' + dbo.fixchars(S.JBSRegion) + '/' 
+ dbo.fixchars(L.JBLocation) + '/' + dbo.fixchars(H.Hourstype) + '/' + dbo.fixchars(A.JBAEmplymentType) + '/' as URL 
FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID
inner join dbo.HoursType H on H.ID = A.[Hours]
inner join dbo.EmploymentType E on E.etype = A.JBAEmplymentType

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(E.ID = @jobtype or @jobtype IS NULL) 
AND			(C.ID= @clientid or @clientid is null) 

Group by JBACategory, cat.ID
order by Adverts desc        
END
END
END

GO

Open in new window

So URL structure should be -'/jobs/clientname/sector/region/location....' but and for me, this is where it gets complicated I only want to add 'clientname/' if @clientid is not null, I only want to add 'region/' if @region is not null,.. and the same for all the other elements of the URL..

Possibly to take this a stage further and make it even more complicated @clientid might contain a value that is not listed in dbo.client and again in this case should be treated as a null value...

Greatly appreciate any help I can get with this, as I'm very confused!

Thank you all

SOLUTION

plummet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Thanks Plummet, but I'm now getting --

Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 34
Column 'dbo.Client.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

:(

plummet

Ah yes, I see...looking further down to your group by, add the line I sent you in there (you don't need the alias at the end) so it reads like this:

Group by
    JBACategory, 
    cat.ID,
    '/jobs/' + case when @clientid is null then '' else dbo.fixchars(C.Name) + '/' end + case when @sector is null then '' else dbo.fixchars(JBACategory) + '/' end + case when @region is null then '' else dbo.fixchars(S.JBSRegion) + '/'  end
+ case when @location is null then '' else dbo.fixchars(L.JBLocation) + '/' end + case when @hours is null then '' else dbo.fixchars(H.Hourstype) + '/' end + case when @jobtype is null then ' else dbo.fixchars(A.JBAEmplymentType) + '/' end

Open in new window

garethtnash

ASKER

Umm, sadly not --

Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 34
Column 'dbo.Client.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 36
Column 'dbo.JBSite.JBSRegion' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 37
Column 'dbo.JBLocation.JBLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 38
Column 'dbo.HoursType.Hourstype' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 39
Column 'dbo.JBAdvert.JBAEmplymentType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 66
Column 'dbo.Client.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 68
Column 'dbo.JBSite.JBSRegion' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 69
Column 'dbo.JBLocation.JBLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 70
Column 'dbo.HoursType.Hourstype' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure JobsearchSectorDescNew2, Line 71
Column 'dbo.JBAdvert.JBAEmplymentType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

garethtnash

ASKER

I need to keep the group by, as I'm trying to return the count of the number of records for each row returned.. :(

plummet

Yes that should be OK, could you post up the query as it is now?

garethtnash

ASKER

Sure thing, here you go -

CREATE PROCEDURE [dbo].[JobsearchSectorDescNew2](
@region int,
@location int,
@sector int,
@hours int,
@jobtype int,
@clientid int,
@keywords nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN

IF @keywords IS NOT NULL
BEGIN

DECLARE @MyQuote varchar(10)
DECLARE @MyWildCard varchar(5)

SET @MyQuote = '"'
SET @MyWildCard = '*'
SET @keywords = @MyQuote + @keywords + @MyWildCard + @MyQuote

select COUNT(JBAID) Adverts,
JBACategory Sector,
cat.ID SectorID,
'/jobs/' + case when @clientid is null then '' else dbo.fixchars(C.Name) + '/' end 
+ dbo.fixchars(JBACategory) + '/'
+ case when @region is null then '' else dbo.fixchars(S.JBSRegion) + '/'  end
+ case when @location is null then '' else dbo.fixchars(L.JBLocation) + '/' end 
+ case when @hours is null then '' else dbo.fixchars(H.Hourstype) + '/' end 
+ case when @jobtype is null then '' else dbo.fixchars(A.JBAEmplymentType) + '/' end as URL

FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID
inner join dbo.HoursType H on H.ID = A.[Hours]
inner join dbo.EmploymentType E on E.etype = A.JBAEmplymentType

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(E.ID = @jobtype or @jobtype IS NULL) 
AND			(C.ID= @clientid or @clientid is null) 
And		CONTAINS(JBADescription, @keywords)

Group by JBACategory, cat.ID, '/jobs/' + case when @clientid is null then '' else dbo.fixchars(C.Name) + '/' end + case when @sector is null then '' else dbo.fixchars(JBACategory) + '/' end + case when @region is null then '' else dbo.fixchars(S.JBSRegion) + '/'  end
+ case when @location is null then '' else dbo.fixchars(L.JBLocation) + '/' end + case when @hours is null then '' else dbo.fixchars(H.Hourstype) + '/' end + case when @jobtype is null then '' else dbo.fixchars(A.JBAEmplymentType) + '/' end
order by Adverts desc
END
ELSE
BEGIN
select COUNT(JBAID) Adverts,
JBACategory Sector,
cat.ID SectorID,
'/jobs/' + case when @clientid is null then '' else dbo.fixchars(C.Name) + '/' end 
+ dbo.fixchars(JBACategory) + '/'
+ case when @region is null then '' else dbo.fixchars(S.JBSRegion) + '/'  end
+ case when @location is null then '' else dbo.fixchars(L.JBLocation) + '/' end 
+ case when @hours is null then '' else dbo.fixchars(H.Hourstype) + '/' end 
+ case when @jobtype is null then '' else dbo.fixchars(A.JBAEmplymentType) + '/' end as URL

FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID
inner join dbo.HoursType H on H.ID = A.[Hours]
inner join dbo.EmploymentType E on E.etype = A.JBAEmplymentType

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(E.ID = @jobtype or @jobtype IS NULL) 
AND			(C.ID= @clientid or @clientid is null) 

Group by JBACategory, cat.ID, '/jobs/' + case when @clientid is null then '' else dbo.fixchars(C.Name) + '/' end + case when @sector is null then '' else dbo.fixchars(JBACategory) + '/' end + case when @region is null then '' else dbo.fixchars(S.JBSRegion) + '/'  end
+ case when @location is null then '' else dbo.fixchars(L.JBLocation) + '/' end + case when @hours is null then '' else dbo.fixchars(H.Hourstype) + '/' end + case when @jobtype is null then '' else dbo.fixchars(A.JBAEmplymentType) + '/' end
order by Adverts desc        
END
END
END

GO

Open in new window

Thanks

plummet

Well, I reckon the group by should probably just contain the columns we're using in the aggregated column, so how about changing both to:

Group by JBACategory, cat.ID, C.Name,S.JBSRegion,L.JBLocation,H.Hourstype,A.JBAEmplymentType

Open in new window

garethtnash

ASKER

Umm, not quite, sorry.

The stored procedure compiled OK, buy I have far too many rows of data

the version of this query without the edition of URL, so -

CREATE PROCEDURE [dbo].[JobsearchSectorDesc](
@region int,
@location int,
@sector int,
@hours int,
@jobtype int,
@clientid int,
@keywords nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN

IF @keywords IS NOT NULL
BEGIN

DECLARE @MyQuote varchar(10)
DECLARE @MyWildCard varchar(5)

SET @MyQuote = '"'
SET @MyWildCard = '*'
SET @keywords = @MyQuote + @keywords + @MyWildCard + @MyQuote

select COUNT(JBAID) Adverts,
JBACategory Sector,
cat.ID SectorID 
FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(A.JBAEmplymentType = CASE When @jobtype = 1 then 'Permanent' when @jobtype = 2 then 'Contract' when @jobtype = 3 then 'Temporary' End or @jobtype is null) 
AND			(C.ID= @clientid or @clientid is null) 
And		CONTAINS(JBADescription, @keywords)

Group by JBACategory, cat.ID
order by Adverts desc
END
ELSE
BEGIN
select COUNT(JBAID) Adverts,
JBACategory Sector,
cat.ID SectorID 
FROM [dbo].[JBAdvert] A
inner join dbo.Client C on C.ID = A.ClientID
inner join dbo.sector cat on cat.sector = A.JBACategory
inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID

WHERE		(S.JBSSiteID = @region or @region IS NULL) 
AND			(L.JBLID = @location or @location is null) 
AND			(cat.ID = @sector or @sector is null) 
AND			(A.[Hours] = @hours or @hours is NULL) 
AND			(A.JBAEmplymentType = CASE When @jobtype = 1 then 'Permanent' when @jobtype = 2 then 'Contract' when @jobtype = 3 then 'Temporary' End or @jobtype is null) 
AND			(C.ID= @clientid or @clientid is null) 

Group by JBACategory, cat.ID
order by Adverts desc        
END
END
END

GO

Open in new window

returns 41 rows, with the edition of URL and the additional GROUP by clauses I get 1061 rows

:( I should be getting the 41 rows, I guess I'm the extra because of the additional group by clauses?

Thanks

plummet

Yes, precisely that. If you're showing the columns then they must be in the group by clause, if you have one, unless you use a function such as min() or max()

garethtnash

ASKER

Can you think of any other way of achieving this?
Thanks

plummet

If the values in the fields C.Name,S.JBSRegion,L.JBLocation,H.Hourstype,A.JBAEmplymentType vary for each JBACategory and cat.ID then it has to be correct this way, otherwise which combination of the URL columns would be correct? It has to show all combinations.

PortletPaul

to see an alternative that (I hope) will not produce extra rows:
please try this, it uses max(...) within the URL construction so it isn't used in the group by - but of course I've no way of knowing if such an approach produces valid values

CREATE PROCEDURE [dbo].[JobsearchSectorDescNew3] (
    @region INT
    , @location INT
    , @sector INT
    , @hours INT
    , @jobtype INT
    , @clientid INT
    , @keywords NVARCHAR(50)
    )
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN
        IF @keywords IS NOT NULL
        BEGIN
            DECLARE @MyQuote VARCHAR(10)
            DECLARE @MyWildCard VARCHAR(5)

            SET @MyQuote = '"'
            SET @MyWildCard = '*'
            SET @keywords = @MyQuote + @keywords + @MyWildCard + @MyQuote

            SELECT
                  COUNT(JBAID) Adverts
                , JBACategory Sector
                , cat.ID SectorID
                , '/jobs/' 
                  + CASE 
                        WHEN @clientid IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(C.NAME))  + '/'
                        END 
                  + max( dbo.fixchars(JBACategory) )
                  + '/' 
                  + CASE 
                        WHEN @region IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(S.JBSRegion)) + '/'
                        END 
                  + CASE 
                        WHEN @location IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(L.JBLocation)) + '/'
                    END 
                  + CASE 
                        WHEN @hours IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(H.Hourstype)) + '/'
                        END 
                  + CASE 
                        WHEN @jobtype IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(A.JBAEmplymentType)) + '/'
                        END 
                    AS URL
            FROM [dbo].[JBAdvert] A
            INNER JOIN dbo.Client C ON C.ID = A.ClientID
            INNER JOIN dbo.sector cat ON cat.sector = A.JBACategory
            INNER JOIN dbo.JBLocation L ON L.JBLSiteID = A.JBASiteID
                AND L.JBLocation = A.JBALocation
            INNER JOIN dbo.JBSite S ON S.JBSSiteID = A.JBASiteID
            INNER JOIN dbo.HoursType H ON H.ID = A.[Hours]
            INNER JOIN dbo.EmploymentType E ON E.etype = A.JBAEmplymentType
            WHERE (
                    S.JBSSiteID = @region
                    OR @region IS NULL
                    )
                AND (
                    L.JBLID = @location
                    OR @location IS NULL
                    )
                AND (
                    cat.ID = @sector
                    OR @sector IS NULL
                    )
                AND (
                    A.[Hours] = @hours
                    OR @hours IS NULL
                    )
                AND (
                    E.ID = @jobtype
                    OR @jobtype IS NULL
                    )
                AND (
                    C.ID = @clientid
                    OR @clientid IS NULL
                    )
                AND CONTAINS (
                    JBADescription
                    , @keywords
                    )
            GROUP
                  BY JBACategory
                , cat.ID
            ORDER BY Adverts DESC
        END
        ELSE
        BEGIN
            SELECT
                  COUNT(JBAID) Adverts
                , JBACategory Sector
                , cat.ID SectorID
                , '/jobs/' 
                  + CASE 
                        WHEN @clientid IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(C.NAME))  + '/'
                        END 
                  + max( dbo.fixchars(JBACategory)  )
                  + '/' 
                  + CASE 
                        WHEN @region IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(S.JBSRegion)) + '/'
                        END 
                  + CASE 
                        WHEN @location IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(L.JBLocation)) + '/'
                    END 
                  + CASE 
                        WHEN @hours IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(H.Hourstype)) + '/'
                        END 
                  + CASE 
                        WHEN @jobtype IS NULL
                            THEN ''
                        ELSE max(dbo.fixchars(A.JBAEmplymentType)) + '/'
                        END 
                    AS URL
            FROM [dbo].[JBAdvert] A
            INNER JOIN dbo.Client C ON C.ID = A.ClientID
            INNER JOIN dbo.sector cat ON cat.sector = A.JBACategory
            INNER JOIN dbo.JBLocation L ON L.JBLSiteID = A.JBASiteID
                AND L.JBLocation = A.JBALocation
            INNER JOIN dbo.JBSite S ON S.JBSSiteID = A.JBASiteID
            INNER JOIN dbo.HoursType H ON H.ID = A.[Hours]
            INNER JOIN dbo.EmploymentType E ON E.etype = A.JBAEmplymentType
            WHERE (
                    S.JBSSiteID = @region
                    OR @region IS NULL
                    )
                AND (
                    L.JBLID = @location
                    OR @location IS NULL
                    )
                AND (
                    cat.ID = @sector
                    OR @sector IS NULL
                    )
                AND (
                    A.[Hours] = @hours
                    OR @hours IS NULL
                    )
                AND (
                    E.ID = @jobtype
                    OR @jobtype IS NULL
                    )
                AND (
                    C.ID = @clientid
                    OR @clientid IS NULL
                    )
            GROUP BY
                  JBACategory
                , cat.ID
            ORDER BY Adverts DESC
        END
    END
END
GO

Open in new window

+edit: missed a max() hope I got them all now

garethtnash

ASKER

Hi Both,

Thanks for your help.

I think I may have found a solution, would you mind just having a quick look at at it letting me know if it looks sound to you please, it appears to work, i suppose the question is whether it has been compiled well and is efficient --

Here it is --

CREATE PROCEDURE [dbo].[JobsearchSectorDescNEW](
@region int,
@location int,
@sector int,
@hours int,
@jobtype int,
@clientid int,
@keywords nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
	BEGIN
	DECLARE @URL nvarchar(500)
	DECLARE @URL2 nvarchar(500)
	Declare @tempurl nvarchar(250)
		/******/
		BEGIN
		set @URL = '/jobs/'
		set @URL2 = ''
		END
		/******/
		/******/
		if exists (select Name from dbo.Client where ID = @clientid)
			BEGIN
				Select @tempurl = dbo.fixchars(lower(Name)) from dbo.client where id = @clientid
				Set @URL = @URL + @tempurl + '/'
			END
		/*****/
		/******/
		if exists (select JBSRegion from dbo.JBSite where JBSSiteID = @region)
			BEGIN
			set @tempurl = ''
			select @tempurl = dbo.fixchars(lower(JBSRegion))from dbo.JBSite where JBSSiteID = @region
			Set @URL2 = @tempurl + '/' 
			END
		/******/
		/******/
		if exists (select JBLocation from dbo.JBLocation where JBLID = @location)
			BEGIN
			select @tempurl = dbo.fixchars(lower(JBLocation))from dbo.JBLocation where JBLID = @location
			Set @URL2 = @URL2 + @tempurl + '/' 
			END
		/******/
		/******/
		if exists (select etype from dbo.EmploymentType where ID = @jobtype)
			BEGIN
			select @tempurl = dbo.fixchars(lower(etype))from dbo.EmploymentType where ID = @jobtype
			Set @URL2 = @URL2 + @tempurl + '/' 
			END
		/******/
		/******/
		if exists (select HoursType from dbo.HoursType where ID = @hours)
			BEGIN
			select @tempurl = dbo.fixchars(lower(HoursType))from dbo.HoursType where ID = @hours
			Set @URL2 = @URL2 + @tempurl + '/' 
			END
		/******/
		/******/
		if @keywords is not null
		begin 
		SET @URL2 = @URL2 + '?keywords=' + lower(RTRIM(LTRIM(@keywords)))
		END
		/******/
	IF @keywords IS NOT NULL
		/******/
		/******/
		BEGIN

		DECLARE @MyQuote varchar(10)
		DECLARE @MyWildCard varchar(5)

		SET @MyQuote = '"'
		SET @MyWildCard = '*'
		SET @keywords = @MyQuote + @keywords + @MyWildCard + @MyQuote

		select COUNT(JBAID) Adverts,
		JBACategory Sector,
		cat.ID SectorID,
		@URL + dbo.fixchars(lower(JBACategory)) + '/' + @URL2  as HTTPLink
		FROM [dbo].[JBAdvert] A
		inner join dbo.Client C on C.ID = A.ClientID
		inner join dbo.sector cat on cat.sector = A.JBACategory
		inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
		inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID

		WHERE		(S.JBSSiteID = @region or @region IS NULL) 
		AND			(L.JBLID = @location or @location is null) 
		AND			(cat.ID = @sector or @sector is null) 
		AND			(A.[Hours] = @hours or @hours is NULL) 
		AND			(A.JBAEmplymentType = CASE When @jobtype = 1 then 'Permanent' when @jobtype = 2 then 'Contract' when @jobtype = 3 then 'Temporary' End or @jobtype is null) 
		AND			(C.ID= @clientid or @clientid is null) 
		And		CONTAINS(JBADescription, @keywords)

		Group by JBACategory, cat.ID
		order by COUNT(JBAID) desc
		END
		/******/
		/******/
	ELSE
		/******/
		/******/
		BEGIN
		select COUNT(JBAID) Adverts,
		JBACategory Sector,
		cat.ID SectorID,
		@URL + dbo.fixchars(lower(JBACategory)) + '/' + @URL2  as HTTPLink
		FROM [dbo].[JBAdvert] A
		inner join dbo.Client C on C.ID = A.ClientID
		inner join dbo.sector cat on cat.sector = A.JBACategory
		inner join dbo.JBLocation L on L.JBLSiteID = A.JBASiteID AND L.JBLocation = A.JBALocation
		inner join dbo.JBSite S on S.JBSSiteID = A.JBASiteID

		WHERE		(S.JBSSiteID = @region or @region IS NULL) 
		AND			(L.JBLID = @location or @location is null) 
		AND			(cat.ID = @sector or @sector is null) 
		AND			(A.[Hours] = @hours or @hours is NULL) 
		AND			(A.JBAEmplymentType = CASE When @jobtype = 1 then 'Permanent' when @jobtype = 2 then 'Contract' when @jobtype = 3 then 'Temporary' End or @jobtype is null) 
		AND			(C.ID= @clientid or @clientid is null) 

		Group by JBACategory, cat.ID
		order by COUNT(JBAID) desc        
		END
		/******/
	END
END

GO

Open in new window

I appreciate your input and feedback.

Thank you

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

this seems to be a duplicated question (or at least related)
https://www.experts-exchange.com/questions/28140075/What-is-wrong-with-my-Stored-Procedure.html

did you try the use of max() at id 39203312

garethtnash

ASKER

Thank you Both,

Sorry for double posting

:)