No idea if this is the right place to post... Need stored proc help

Hello,
I am trying to make a stored proc that will tell me if there are any rows in a table. The table name is a variable that I get at the start of the proc it is not sent in. What I am looking for in the table is sent into the stored proc.

here is where the problem is, i can not save it. It does not seem to allow me to use the if exists with variables. I have tried it also without the + and with the + ' to create a string I cant seem to get it anyway I try.
if exists (SELECT * FROM + @.TableName + WHERE + @.ColumnName + = + @.SearchStr2)

print 'got here ' +@.TableName + ' '+ @.ColumnName

END

Thanks for any help

You need to build the entire query string in a variable, and then use the dbo.sp_executesql stored procedure to execute that query string. As you've discovered, you can't use 'if exists' with variables.

For example:

declare @.sql nvarchar(max)

set @.sql = N'

if exists (select * from ' + @.TableName + N' where ' + @.ColumnName + ' = ''' + @.SearchStr2 + ''')

print ''Got here ' + @.TableName + ', ' + @.ColumnName + ''''

exec dbo.sp_executesql @.sql

|||

All of this of course is true; however, you also need to know that this is exactly the style of code that a hacker is looking to exploit for an "SQL Injection" attack. Please do some additional research before you implement this. This kind of code CREATES a security hole.

Dave

|||

Thanks Iain I will give it a shot and let you know. Also thanks Mugambo but this is a one time thing i need to run to do the following if anyone has a better idea how to do it I would love ideas.

I need to update our SQL database for an individual user. The problem is somehow this user got two ID's with different numbers and it has been about 4 months now that it has gone unnoticed and. So I now need to change the two id's into one. So what I was planning to do was to do was search ever column of every table and it would let me know if there is an exact match to the ID I put in. If so it will display the column and table so I can decide if it is data I need to change or not. We have about 200 tables so to do it one table at a time wouldnt be impossible to do but this way I learn a little more about SQL and stored procs which is what I really want anyway.

Thanks again

|||And thanks again Iain works great.|||Hi,

Can you mark a correct answer for us please.

Thanks!