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

Answer this question

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

WendyB
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
dakota367
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
Robi-Robo
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
Yuhang
And thanks again Iain works great.
DragonC&#35;
Hi,

Can you mark a correct answer for us please.

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

Answer this question