Thank you, but mentioning the childtable explicitly is not an option here. A parent table can have many child tables.
That's a major design problem that probably should have been avoided.
The answer to your question is to take advantage of what sp_ExecuteSQL is actually capable of... passing parameters.
I can't test this because I don't have your insitu data or tables, but something like the following should fix you right up. Look for "changed here" to see the changes I made to your code.
ALTER PROCEDURE SoftDelete
@TableName nvarchar(50), @ColName nvarchar(50),
@Return TINYINT = NULL OUTPUT --Changed here
DECLARE @qry nvarchar(500)
SELECT @qry = 'begin transaction
delete '+@tablename+' where '+@colname+'='+@id+'
if(@@Error <> 0)
SELECT @Return = 0 --Changed here
update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
SELECT @Return = 1 -Changed here
EXECUTE sp_executesql @qry,
,N'@Return TINYINT OUTPUT' --Defines the passed parameter -- Changed here
,@Return = @Return OUTPUT --Returns the value of the parameter -- Changed here
Shifting gears a bit, this code is extremely prone to SQL Injection and a security accident just waiting to happen. You really
need to santize your inputs on this code to make sure that there is no SQL Injection.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs