Input parameter makes bad local variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Input parameter makes bad local variable?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-06 : 23:13:02

CREATE PROCEDURE dbo.FE_ContestCenter_2
@ClientID INT = NULL ,
@CourseID INT = NULL
AS
SET NOCOUNT ON

SELECT Top 1 @ClientID = IsNULL(@ClientID, ClientID) FROM Clients

SELECT Top 1 @CourseID = IsNull(@CourseID, CourseID) FROM Courses

DECLARE @TEST INT
SET @TEST = 1032

IF @Test = @CourseID begin
print 'equal'
set @test = @CourseID -- Let's even copy the value ..
end else begin
print 'not equal'
end

SELECT CenterName ,
Sum(CASE WHEN CRecordFinish IS NOT NULL THEN 1 ELSE 0 END) as UCount -- Total Users Completed

FROM Users U
LEFT OUTER JOIN CourseRecords CR ON CR.UserID = U.UserID and CR.CourseID = @test
inner join Centers C on U.CenterID=C.Centerid
WHERE U.Inactive=0
GROUP BY C.CenterName

SET NOCOUNT OFF
GO

In the above procedure, I set @CourseID (an input variable) if it isn't passed as a parameter.

Oddly, the procedure will not work if I use @CourseID in the query. Instead, I allocate a local variable @test, set it to the same value, and the query completes in under a second.

This seems like SQL is having a compile/execution time problem with the use of @CourseID being redefined within the procedure. Is this permitted or not?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-07 : 04:47:01

Nothing to stop you using an input variable.

SELECT Top 1 @CourseID = IsNull(@CourseID, CourseID) FROM Courses

should be

if @CourseID is null
select @CourseID = (select top 1 CourseID FROM Courses)

==========================================
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.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-07 : 08:59:44

Good Morning Nigel (morning here anyway. You've finished lunch by now?)

The syntax you provided appears functionally identical to me. Except

SELECT Top 1 @CourseID = IsNull(@CourseID, CourseID) FROM Courses

will always run, which is unnecessary, so your statement is better.

Is there any other reason to recode that line?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 10:08:29

Nigel's version is more logical, and there's not much point in running a SELECT if the variable is not null. If the Courses tables has a lot of rows you'll probably see a big performance improvement with Nigel's code.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-07 : 11:17:33

The syntax for setting a variable is
SELECT { @local_variable = expression } [ ,...n ]

Which is why I prefer
select @CourseID = (select top 1 CourseID FROM Courses)
to
SELECT Top 1 @ClientID = ClientID FROM Clients

but both seem to work.
In fact you might
set @CourseID = (select top 1 CourseID FROM Courses)
which i smeant to be the preferred method.
I don't like this as it only allows setting of one variable.

Your problem is probably due to the way the variable is passed to the SP. Try logging it to a table to see the value.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources