Join in Stored Procedure - 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

Transact-SQL (2000)

Join in Stored Procedure

Author

Topic

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-14 : 11:54:00

Hi again

I am now working with Stored Procedures and currently working on a simple messaging system. The Stored Procedure looks like this:

CREATE PROCEDURE display_archive_messages
@user_id int
AS
Select message_id, message_sender, message_sent, message_title From messages
Where message_read = 1 AND message_reciever = @user_id

'message_sender' has the senders user_id.

But if I now want to Join this query with the table which has all the users so that I can get the username of the sender in this specific query, how would I do that?

'users' table, 'user_id' and 'user_username' are fields from the table I want to get the data from...

=====================================
Why not try and do the impossible?

Edited by - Swede on 01/14/2002 11:56:03

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-14 : 12:22:51

CREATE PROCEDURE display_archive_messages
@user_id int
AS
Select u.user_id,message_id, message_sender, message_sent, message_title From messages
inner join userstable u on u.userid=message.user_id
Where message_read = 1 AND message_reciever = @user_id

Mike
"A program is a device used to convert data into error messages."

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-14 : 12:37:46

Can't get it to work and I have no idea why...

What does this do exactly? Does it just collect the same user_id from the users table and not the user_username?

How do I actually get the user_username from the users table from that?

=====================================
Why not try and do the impossible?

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-14 : 13:04:17

Ah well, got it to work a different way... Feedback on this way, is it good or bad?

CREATE PROCEDURE display_new_messages
@user_id int
AS
SELECT messages.message_id, messages.message_sender, messages.message_sent, messages.message_title, users.user_username
FROM messages, users
WHERE messages.message_sender = users.user_id AND messages.message_read = 0 AND messages.message_reciever = @user_id

=====================================
Why not try and do the impossible?

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-14 : 13:18:26

This will work but generally joins are faster than using a where clause.

Mike
"A program is a device used to convert data into error messages."

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-14 : 13:21:26

Hmmm, what I wrote is an INNERJOIN, isn't it? *confused*

=====================================
Why not try and do the impossible?

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-14 : 13:29:07

Technically yes, but I'll explain further. With a where clause, it basically pulls all the records together and then searches for matches on the where clause. With the where clause, it trims your records based on the join, then filters for the where clause. Does that make more sense?

Mike
"A program is a device used to convert data into error messages."

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-14 : 13:36:18

Yep!

OK, so to get it to work optimally, I would use something like the thing you wrote (Although I couldn't get that to work)?

=====================================
Why not try and do the impossible?

mfemenel
Professor Frink

1421 Posts

Posted - 2002-01-14 : 14:41:52

Post what you wrote and I'll see what I can do.

Mike
"A program is a device used to convert data into error messages."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-14 : 17:17:24

Swede,

A little tip...

ALWAYS put SET NOCOUNT ON as the first statement in a Stored Proc....

DavidM

Tomorrow is the same day as Today was the day before.

Nazim
A custom title

1408 Posts

Posted - 2002-01-15 : 00:09:30

On your query

SELECT messages.message_id, messages.message_sender, messages.message_sent, messages.message_title, users.user_username
FROM messages inner join users
on messages.message_sender = users.user_id AND
messages.message_read = 0 AND messages.message_reciever = @user_id

or

SELECT messages.message_id, messages.message_sender, messages.message_sent, messages.message_title, users.user_username
FROM messages inner join users
on messages.message_sender = users.user_id
where messages.message_read = 0 AND messages.message_reciever = @user_id

You can also Use Alias to reduce the length of your query

Eg:

SELECT m.message_id, m.message_sender, m.message_sent, m.message_title, u.user_username
FROM messages m inner join users u
on m.message_sender = u.user_id AND
m.message_read = 0 AND m.message_reciever = @user_id

to put more light on why David suggested to put Set Nocount on as your first statement in your stored procedure. Set Nocount On basically suppresses the display of Intermdiary or Summary Messages like No. of records returned or affected. this will increase the Performance of Stored Procedures as unnecessary Network traffic is reduced.

HTH

----------------------------------
"True love stories don't have endings."

Edited by - Nazim on 01/15/2002 00:14:19

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-15 : 07:13:12

OK, here is my new SP. Have I entered NOCOUNT in the right place?

CREATE PROCEDURE count_new_messages
@user_id int
AS
SET NOCOUNT ON
SELECT COUNT(*) AS message_count FROM messages
WHERE message_read = 0 AND message_reciever = @user_id

GO

No 100% sure about the use of NOCOUNT though, can you give me a short short example of one use of NOCOUNT and one without?

=====================================
Why not try and do the impossible?

Nazim
A custom title

1408 Posts

Posted - 2002-01-15 : 07:36:26

Typically its the first statement in your Stored procedure.

for eg:

CREATE PROCEDURE count_new_messages(@mcond varchar(10))
@user_id int
AS
Update table set columname=columnname+344 where condition=@mcond

Update Anothertabe set columname=columname were condition=@mcond

select ....
GO

If you execute this stored procedure in Query Analyser the messages you will see is the no. of rows updated in the first update statement and then on the second and then the result of the select. but , v are only concerened about the select result. The Result will be something like this

(4 row(s) affected)
(5 row(s) affected)
sno sname .............
----- --result of the select
....
(40 row(s) affected) -- the no. of rows the select returns

if we append Set NOCOUNT ON in the code

CREATE PROCEDURE count_new_messages(@mcond varchar(10))
@user_id int
AS
SET NOCOUNT ON -- Typically the first statement can be anywhere too, but recommended at the top
Update table set columname=columnname+344 where condition=@mcond

Update Anothertabe set columname=columname were condition=@mcond

select ....
GO

the Result will be

sno sname .............
-----
.................. (ONLY the query result suppersing the display of messages like (4 row(s) affected) )

HTH

----------------------------------
"True love stories don't have endings."

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-15 : 08:51:58

Aha, now I get it.

Well explained! Thanks!

=====================================
Why not try and do the impossible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources