My sp problem - 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 2000 Forums

SQL Server Development (2000)

My sp problem

Author

Topic

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 14:55:36

Good day,
I have this select statement which works fine:

Select StuID, Count(ContractNo)
FROM Contract JOIN Student
ON Student.STContractNo = Contract.ContractNo
GROUP BY StuID, ContractNo
Having ContractNo > 1

With Results of:
StuID ContractNo
stu0001 1
stu00010 1
stu00011 1
stu00014 1
stu00015 1
stu0004 1
stu0005 1
stu0007 1
stu0008 1

But I'm trying to turn this into a sp with a parameter To get the same results but it's returning nothing:
CREATE PROCEDURE ContractList
@Contract int
AS
Select StuID, Count(ContractNo)
FROM Contract JOIN Student
ON Student.STContractNo = Contract.ContractNo
WHERE ContractNo = @Contract
GROUP BY StuID, ContractNo
Having ContractNo > 1

EXEC ContractList 1

But It's not returning any results at all.
What mistake am I making?

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-14 : 15:34:34

Your having clause says ContractNo must be greater than one, and the where clause says ContactNo must be equal to the parameter.
Then you're calling the sp with a parameter value of 1, so the where selects rows where ContractNo is equal to 1 and the having clause then excludes all those rows.
Perhaps you meant to have this in your having?
HAVING count(ContractNo) > 1

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 15:41:52

SnSql
I tried HAVING count(ContractNo) > 1 and got the same results I even tried HAVING count(ContractNo)= 1

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

Benholio
Starting Member

4 Posts

Posted - 2006-12-14 : 15:49:41

You have no WHERE clause in your original code, why is there now one in the SP?

WHERE ContractNo = @Contract

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 15:52:29

Since you are filtering your rows on ContractNo = 1, I think you can just remove the HAVING clause altogether.

Tara Kizer

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 15:55:03

You have no WHERE clause in your original code, why is there now one in the SP?

I was trying to give it some sort of definition and the where clause seemed like a good way to do this.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 15:56:52

Tara,
Did that, same results. I'm quite befuddled

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-14 : 15:58:55

quote:
Originally posted by nomadsoul

You have no WHERE clause in your original code, why is there now one in the SP?

I was trying to give it some sort of definition and the where clause seemed like a good way to do this.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

SQL is a very precise language, I'd recommend against taking a "some sort of definition" or "seemed like a good way" approach

What do you want the query to do? Until we are sure of that, we'll be guessing at ways that might sort of seem like a kind of possible way to get it fairly correct, perhaps.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 15:59:49

Then are you sure there is actually a row in both tables with ContractNo = 1?

SELECT * FROM Contract WHERE ContractNo = 1
SELECT * FROM Student WHERE ContractNo = 1

Do either of them or both return 0 rows?

Tara Kizer

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 16:07:41

What do you want the query to do?
Sorry for the confusion
I'm counting students that have more than one contract, then finding which student has only one contract, not the contract number itself.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 16:08:18

Tara , yes they returned 0

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 16:11:15

Then that's why you aren't getting any results back!!!

Why are you expecting rows when there aren't any for that parameter?

Tara Kizer

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 16:12:41

We are having our DB final soon. Based upon the assignments given this semester, I think this may be one of his questions in one form or another, using one of several db's So I'm preparing for it

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 16:29:10

Ohhhh, this is a schoolwork question!

Tara Kizer

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 16:41:04

Yes m'am, I hope that's ok, I promise it's not an assignment/homework question. For those, I go to others in class and they to me.
But I do have a pt.job where I use it and sometimes I come here to figure those problems because the class cant help me there niether can others at the office because I'm the allaround tech guy (web-dev, office Lan, DB.--on and on) here: www.primehomesrealestate.com
but I've noticed alot of homework type questions in here.
So, back to my problem:
I change it a bit:
CREATE PROCEDURE ContractList
@Contract varchar
AS
Select StuID, sum(ContractNo) 'Total Contracts Per Student' FROM Contract JOIN Student
ON Student.SAContractNo = Contract.ContractNo
where ContractNo>1
Group by StuID

exec ContractList 101

Now it's returning but only when I put in 101 or 102 etc.. as the needed parameter.
Returns:

stu00014 1
stu0007 1

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 16:43:30

Yes that is correct. It will return the rows when it finds them matching with the value you passed to the parameter. If it's not what you want, please explain in detail what you want. Show us sample rows of what the data looks like and what you want returned.

Tara Kizer

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-12-14 : 16:51:44

Tara,
I checked the table(s) and that is what is given stu00014 has only one contract. What you said earlier got me thinking about the sum and the datatype and other ways to do this. I'm going to leave this as is
Thank you and sn

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources