Solved

If in a select statement - MS SQL

Posted on 2013-05-28
17
339 Views
Last Modified: 2013-06-02
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
0
Comment
Question by:garethtnash
  • 8
  • 6
  • 3
17 Comments
 
LVL 10

Assisted Solution

by:plummet
plummet earned 250 total points
ID: 39202399
Hi,

Try this line as your URL:

'/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 as URL

Open in new window


I think that should help.

Cheers
John
0
 

Author Comment

by:garethtnash
ID: 39202429
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.

:(
0
 
LVL 10

Expert Comment

by:plummet
ID: 39202444
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

0
 

Author Comment

by:garethtnash
ID: 39202463
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.
0
 

Author Comment

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

Expert Comment

by:plummet
ID: 39202467
Yes that should be OK, could you post up the query as it is now?
0
 

Author Comment

by:garethtnash
ID: 39202480
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 39202499
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

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:garethtnash
ID: 39202572
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 39202611
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()
0
 

Author Comment

by:garethtnash
ID: 39202628
Can you think of any other way of achieving this?
Thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 39202656
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39203312
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
0
 

Author Comment

by:garethtnash
ID: 39205501
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
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39206328
those small queries to build the url should not be too much of an overhead as it looks like they are keys to each of those tables, but you are executing each of them twice.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39206492
this seems to be a duplicated question (or at least related)
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28140075.html

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

Author Closing Comment

by:garethtnash
ID: 39214352
Thank you Both,

Sorry for double posting

:)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now