Using variable in WHERE clause on sysobjects

Question

Post reply

Using variable in WHERE clause on sysobjects

Fubak

Ten Centuries

Points: 1114
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

Farrell Keough

SSCoach

Points: 17414

April 5, 2005 at 9:15 am

#550569

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: 1114 More actions · Answer 3

Fubak

Ten Centuries

Points: 1114

April 5, 2005 at 9:22 am

#550574

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?