Is this possible? not so sure!

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 2000 Forums

Transact-SQL (2000)

Is this possible? not so sure!

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-01-03 : 09:05:12

Is it possible to execute a section of T-SQL on a condition? For example is there any way to do the following:

select agentid as [Agent ID],
				count(a.uniquekey) as [Total Calls]
		from CallLog a
		join Scripts b on a.databaseid = b.uniquekey 
		where b.UniqueKey = @Campaign
		and startcalltime between @DateFrom and @DateTo
IF @SM <> 0
		AND Agentid IN (
				SELECT ID FROM dbo.Users U
				INNER JOIN dbo.ALCATEL_MONITOR_AgentCCID_Ref MR
				ON MR.DiallerID = U.AMCATID
				AND MR.SMID = @SM
				)
END
		group by agentid

The section I refer to is:

IF @SM <> 0 
		AND Agentid IN (
				SELECT ID FROM dbo.Users U
				INNER JOIN dbo.ALCATEL_MONITOR_AgentCCID_Ref MR
				ON MR.DiallerID = U.AMCATID
				AND MR.SMID = @SM
				)

END

Although this obviously will fail is there any way to do this, so that the section of code will only execute if the @SM condition is met. I cannot see a way how T-SQL could perform this but hopefully someone can tell me different.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 09:19:50

This can be done using CASE but efficient sucks!

Why not go for IF block in direct way:

IF @SM <> 0
begin
	select 
		agentid as [Agent ID],
		count(a.uniquekey) as [Total Calls]
	from CallLog a
	join Scripts b on a.databaseid = b.uniquekey 
	where 
		b.UniqueKey = @Campaign
		and startcalltime between @DateFrom and @DateTo
		AND Agentid IN (
				SELECT ID FROM dbo.Users U
				INNER JOIN dbo.ALCATEL_MONITOR_AgentCCID_Ref MR
				ON MR.DiallerID = U.AMCATID
				AND MR.SMID = @SM
				)
	group by agentid
end
else
begin
	select 
		agentid as [Agent ID],
		count(a.uniquekey) as [Total Calls]
	from CallLog a
	join Scripts b on a.databaseid = b.uniquekey 
	where 
		b.UniqueKey = @Campaign
		and startcalltime between @DateFrom and @DateTo
	group by agentid
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-01-03 : 09:25:49

Thanks Harsh,

Yes, if this is not feasible then the full IF block is the only way. I wanted to avoid this though as the full Stored Proc is already 1500 lines long and this will effectively take it to about 3000 lines. There are already a few IF blocks in there and I am a bit concerned about execution time. I would have liked to do a comparison between the 2 ways.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 09:43:02

You can try this:

select 
	agentid as [Agent ID],
	count(a.uniquekey) as [Total Calls]
from CallLog a
join Scripts b on a.databaseid = b.uniquekey 
where 
	b.UniqueKey = @Campaign
	and startcalltime between @DateFrom and @DateTo
	AND Agentid IN (
			SELECT ID FROM dbo.Users U
			INNER JOIN dbo.ALCATEL_MONITOR_AgentCCID_Ref MR
			ON MR.DiallerID = U.AMCATID
			AND MR.SMID = NullIf(@SM, 0)
			)
group by agentid

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-01-03 : 10:20:49

Thanks Harsh, I have decided to go with the full IF blocks as the NULLIF wouldn't produce the desired results. I need the whole block to execute/not execute depending on the @SM parameter.

Thank you for all your help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 11:24:38

quote:
Originally posted by p.shaw3@ukonline.co.uk

Thanks Harsh, I have decided to go with the full IF blocks as the NULLIF wouldn't produce the desired results. I need the whole block to execute/not execute depending on the @SM parameter.

Thank you for all your help.

A simple way to do this:


select 
	agentid as [Agent ID],
	count(a.uniquekey) as [Total Calls]
from CallLog a
join Scripts b on a.databaseid = b.uniquekey 
left outer join
       ( 
	SELECT distinct ID FROM dbo.Users U
	INNER JOIN dbo.ALCATEL_MONITOR_AgentCCID_Ref MR
	ON MR.DiallerID = U.AMCATID
	AND MR.SMID = @SM
	) x
   on AgentID = x.ID

where 
	b.UniqueKey = @Campaign
	and startcalltime between @DateFrom and @DateTo and
        (@SM is null OR x.ID is not null)
group by agentid

Any "conditional join" in T-SQL should be expressed as a LEFT OUTER JOIN. Then, in your WHERE clause, you check that condition and decide whether or not to use or require the results of your outer join in your final result.

Also -- what table does agentID come from? Always prefix all of your column names, even if they are unique for the tables in your SELECT, with a table name. Without knowing your schema, we have no idea which table agentID comes from.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources