Solved

If in a select statement - MS SQL

Posted on 2013-05-28
17
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 49

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 49

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 49

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how the fundamental information of how to create a table.

635 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