Using variable in WHERE clause on sysobjects

Question

Using variable in WHERE clause on sysobjects

Fubak

Ten Centuries

Points: 1174
More actions
April 5, 2005 at 9:09 am

#76269

This select statement works...
select * from db.dbo.sysobjects where name = 'procedurename'
But this one does not...
@Object = 'procedurename'
select * from db.dbo.sysobjects where name = @Object
Why?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

beets me, I tried the following in the master database and it works perfectly, the only thing I can see from your post is that you didn't declare the variable first and didn't set the variable to what you want to look for.

Use Master

GO

declare @object varchar(50)

set @Object = 'sp_monitor'

select * from dbo.sysobjects where name = @Object

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Look up dynamic SQL on this site. Be sure to do the following:

Reasons to be reluctant or/and mindful of dynamic SQL can be found here. Do read it. http://www.sommarskog.se/dynamic_sql.html 'The Curse and Blessings of Dynamic SQL'

I wasn't born stupid - I had to study.

Fubak Ten Centuries Points: 1174 More actions · Answer 3

Nice article, but it doesn't really help me with my problem. Other than the suggestion to build a SQL Statement in a string and execute it. Is that what you are suggesting?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

There's just no need for dynamic sql here, check this out :

Declare @Object as varchar(25)

set @Object = 'SysObjects'

Select * from master.dbo.SysObjects where name = 'SysObjects'

UNION ALL

Select * from master.dbo.SysObjects where name = @Object

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Remi and Mike are correct. Please disregard my idiocy....

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I wasn't born stupid - I had to study

You might have studied too hard . (no hurt intended)

Fubak Ten Centuries Points: 1174 More actions · Answer 7

Well, I WAS born stupid. It ended up being an ID10T error. The variable wasn't being set correctly.

Sorry for the false alarm

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

NP... some of us have figured out it would be something like this. Glad you did too .