sql 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)

sql problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-05 : 07:50:31

chris writes "i'm creating a sql stored proc that is supposed to insert someone into a "friends list" for my website pahardcore.com. i'm passing it a string (a persons username) because a user requested that they be able to enter a name in a text box and have it add the friend (if the person exists in the members table). here's what i have, but it's not working. how can i use the results of my first query in my second + third query?

CREATE PROCEDURE dbo.spInsertOneFriend
@username varchar(100),
@userid bigint
AS

select member_id from members where m_name = @username

if @@rowcount != 0

select intmemberid, intuserid from tblbuddy where intmemberid = @userid and intuserid = [member_id (from first query)]

if @@rowcount = 0
insert into tblBuddy (intMemberID, intUserID) values (@userid, [member_id (from first query)])

GO"

dsdeming

479 Posts

Posted - 2003-05-05 : 08:15:34

Wouldn't it be simpler to do something like this:

DECLARE @FriendID
SELECT @FriendID = member_id FROM members WHERE m_name = @username

INSERT INTO tblBuddy (intMemberID, intUserID)
SELECT @userid, @FriendID
WHERE @FriendID NOT IN( SELECT intUserID FROM tblBuddy
WHERE intMemberID = @userid )

HTH

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-05 : 10:57:28

Or in one query (might be useful if multi-user and saves error handling)

insert tblBuddy (intMemberID, intUserID)
select @userid, members.member_id
from members
where members.m_name = @username
and not exists (select * from tblbuddy where tblbuddy.intuserid = members.member_id and tblbuddy.intmemberid = @userid)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-05 : 11:03:17

You may have a SQL problem, but I hope I don't have a problem...my company blocked your Mature Site....

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-05 : 11:16:35

Shame On you Brett

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources