Can this be merged into a single query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can this be merged into a single query?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-01 : 15:24:10

[code]
IF @DeptID = 0
BEGIN
INSERT INTO @DepartmentIDs
select DepartmentID from Departments where clientid = @ClientID
INSERT INTO @DepartmentIDs SELECT 0
END
ELSE
BEGIN
INSERT INTO @DepartmentIDs
select ChildDepartmentID from DepartmentChildren where DepartmentID = @DeptID
END
[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-11-01 : 15:55:15

[code]
INSERT INTO @DepartmentIDs
Select DepartmentID
from
Departments
where clientid = @ClientID
and @DeptID = 0
union all
SELECT a.departmentid
from
(select 0 as Departmentid) a
where @DeptID = 0
union all
select ChildDepartmentID
from DepartmentChildren
where DepartmentID = @DeptID
and @Dept > 0
[/code]

Check stats to compare if there is any performance gain, personally I would probably lean towards using the if statement that you already have.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-01 : 16:02:18

Not quite. Yours always returns this...

Select DepartmentID
from Departments where clientid = @ClientID

But that should only be returned if @DeptID = 0

The performance gain I expect is that if I can make it a single query I can make it an inline function.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-01 : 16:16:44

This seems to work...


Select DepartmentID
from Departments where clientid = @ClientID and @DeptID = 0
union all
SELECT a.departmentid
from (select 0 as Departmentid) a
where @DeptID = 0
union all
select ChildDepartmentID from DepartmentChildren where DepartmentID = @DeptID

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-11-02 : 00:09:40

The query I posted is/was correct.

You reposted the exact same query without the indentation.

The only difference is the one posted by me also included a check on DepartmentChildren to not search when @DEPTID was equal to zero (It is spelled wrong though & says @DEPT > 0 but it should say @DeptID > 0) as you had illustrated was the functionality in your post, which I included incase any departmentchildren did have any records where deptid was equal to 0 in your table to ensure accurate results.

I think it is possible you responded before you read the corrected revision, but it is timestamped before you posted.

Second, you can use both methods illustrated in a function, one does not give you ability over the other. I recommend to check which performs better and weight your choice on that.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources