cannot exec sp - SQL Server Forums

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

Transact-SQL (2005)

cannot exec sp

Author

Topic

mixalis
Starting Member

8 Posts

Posted - 2008-09-12 : 08:27:50

[code]
@tc_fk INT,
@card_type INT,
@card_status INT
AS
BEGIN

SET NOCOUNT ON;

select distinct

TestCentre.tc_pk,
TestCentre.brand_name,
SkillsCard.name AS sc_name, (select count(*) from sc_tc where sc_tc.tc_fk=tc_pk and sc_tc.sc_fk=skillscard.pk),
assigned = CASE certificate_sc.candidate_fk
WHEN isnull(certificate_sc.candidate_fk, 0) THEN 1
ELSE 2
END

from testcentre,skillscard

inner join sc_tc with (nolock)
on sc_tc.sc_fk = skillscard.pk
(@tc_fk is not null and TestCentre.tc_pk = @tc_fk))
inner join certificate_sc with (nolock)
on certificate_sc.sc_tc_fk = sc_tc.pk

where (((@card_type = -1) or (@card_type is not null and skillscard.type_fk = @card_type))
and
((@tc_fk is null) or (@tc_fk is not null and testcentre.tc_pk = @tc_fk))
and
(@card_status is null) or (@card_status = 1 and certificate_sc.candidate_fk is not null) or (@card_status = 2 and certificate_sc.candidate_fk is null))

END
[/code]

the afore mentioned sp does not execute...sql server says executing query but I never get the results...I left it for like a minute or two and still it said that the query is being executed...any ideas???

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-14 : 09:11:18

How many rows are in those tables?
The way that where clause is written, you're probably going to get a table scan, not index seeks. It ain't gonna be fast.

While it's running, check the sys.dm_exec_requests view, see if that query's blocked, if so by what. See it it's waiting and if so what the wait type is and what the wait resource is.

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-14 : 09:24:54

I took the liberty to rewrite your query so it is easier to read.
You need to join the skillscard table, otherwise you will get a cross join (cartesian product).
Also there is no need to join sc_tc table when you have it in a correlated subquery.

(
	@tc_fk INT,
	@card_type INT,
	@card_status INT
)
AS

SET NOCOUNT ON

select distinct	TestCentre.tc_pk, 
		TestCentre.brand_name,
		SkillsCard.name AS sc_name,
		(select count(*) from  sc_tc where sc_tc.tc_fk = tc_pk and sc_tc.sc_fk = skillscard.pk),
		CASE certificate_sc.candidate_fk
			WHEN isnull(certificate_sc.candidate_fk, 0)  THEN 1
			ELSE 2
		END AS assigned
from		testcentre,
		skillscard
inner join	certificate_sc on certificate_sc.sc_tc_fk = sc_tc.pk
where		(@card_type = -1 or skillscard.type_fk = @card_type) 
		and (@tc_fk is null or testcentre.tc_pk = @tc_fk)
		and (@card_status is null
			or (@card_status = 1 and certificate_sc.candidate_fk is not null)
			or (@card_status = 2 and certificate_sc.candidate_fk is null)
		)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources