Stored Proc from 2000 to 2005 - 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 2005 Forums

Transact-SQL (2005)

Stored Proc from 2000 to 2005

Author

Topic

JimmyFo
Starting Member

11 Posts

Posted - 2008-04-03 : 11:26:07

Hi, I have the following stored procedure working in SQL 2000:


(
	@p_ServiceID INT = NULL
)

AS

	SELECT
		S.ServiceID,
.............
.............

	FROM
		tblServices AS S
	WHERE
		(@p_ServiceID = NULL OR @p_ServiceID = S.ServiceID)

This returns all services if a null or nothing is passed, and returns a specific service if a proper service ID is passed.

However, in 2005 this doesn't work. If I pass a service ID, it returns it, but if I pass nothing or a null, it returns no rows.

Any ideas?

Thanks,
James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 11:40:37

Try this:-

(
	@p_ServiceID INT = NULL
)

AS

	SELECT
		S.ServiceID,
.............
.............

	FROM
		tblServices AS S
	WHERE
		(@p_ServiceID IS NULL OR @p_ServiceID = S.ServiceID)

JimmyFo
Starting Member

11 Posts

Posted - 2008-04-03 : 11:59:27

Ah ha, that works... interesting transition.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 12:02:01

I dunno how it worked in 2000. But it should be IS NULL not = NULL as NULL is not a value but a bit set internally. You cant have equality condition check (=) with NULL

munna_sqlteam
Starting Member

1 Post

Posted - 2008-04-04 : 03:14:32

@@TRANCOUNT mean what?

MuNna

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 03:19:40

quote:
Originally posted by munna_sqlteam

@@TRANCOUNT mean what?

MuNna

if you have a question not related to the original post you should start a new thread.

And Books Online tells you exactly what @@trancount is

quote:
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources