Help creating stored procedure

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

General SQL Server Forums

New to SQL Server Programming

Help creating stored procedure

Author

Topic

donkyho
Starting Member

12 Posts

Posted - 2009-11-18 : 14:31:15

I have 3 tables, tblUser, tblTest and a join table..tblUserTest (just TestID and UserID in this table)

I'm not sure the best way to get the data I'm looking for, but what I was thinking is I want to pass in UserID and what I want returned is TestID, TestName and UserID...I want a list of all tests and the UserID or NULL depending if that UserID/TestID have a match in the join table....does this make sense?

Please help!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-19 : 00:13:43

[code]
CREATE PROC sampleproc
(
@userid INT = NULL
)
AS
SET NOCOUNT ON
BEGIN

SELECT t.testid,t.testname,u.userid,u.username
FROM tblusertest ut
INNER JOIN tbluser u ON u.userid = ut.userid
INNER JOIN tbltest t ON t.testid = ut.testid
WHERE (@userid IS NULL OR ut.userid = @userid)

END
SET NOCOUNT OFF
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources