How to improve on this T-SQL SPROC

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)

How to improve on this T-SQL SPROC

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-09-09 : 16:27:46

I wrote this very bad SPROC which returns 0 if not found and the id when its found. How can I improve on this.

CREATE PROCEDURE [usp_GetForumID]

@CourseID int,
@ForumID int OUTPUT

AS

IF NOT EXISTS

(

SELECT ForumID FROM tblMyTable WHERE CourseID = @CourseID

)

BEGIN

SET @ForumID = 0

END

ELSE

SET @ForumID = ( SELECT ForumID FROm tblMyTable WHERE CourseID = @CourseID )

GO

Mohammad Azam
www.azamsharp.net

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-09 : 16:34:37

Just set @ForumID = 0 prior to the SELECT.

SET @ForumID = 0
SELECT @ForumID = ForumID FROm tblMyTable WHERE CourseID = @CourseID

When no rows satisfy the query, then @ForumID will be 0.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources