Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic sql - how to return a value Expand / Collapse
Author
Message
Posted Sunday, December 22, 2013 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 22, 2013 11:33 PM
Points: 4, Visits: 12
tblParent

pid(int) name deleted(bit)
1 abc 0
2 def 0

tblChild
cid(int) name  pid(ForeignKey)
1 aaa 1
2 bbb 1

When a record from tblParent is being deleted, it should check for any child records. If yes, rollback & return 0. If no, then update the deleted column to '1' and return 1. Basically, doing a soft delete

The SP works fine. All I need is to know the status as 0 or 1 based upon the action that took place. How should it be done. I would call this store procedure from c#, linq to entities to get the status. something like:

public int somefuntion() //returning a string is also fine..
{
return MYDB.SoftDelete(param1param2,param3);
}

ALTER PROCEDURE SoftDelete
(
@TableName nvarchar(50), @ColName nvarchar(50),
@Id nvarchar(50)
)
AS
BEGIN
DECLARE @qry nvarchar(500)

SELECT @qry = 'begin transaction
delete '+@tablename+' where '+@colname+'='+@id+'
if(@@Error <> 0)
Begin
--select 0
End
else
Begin
rollback transaction
update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
--select = 1
end'

EXECUTE sp_executesql @qry
END

Post #1525325
Posted Sunday, December 22, 2013 5:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
you can check for foreign key constraints using this query-

UPDATE 
SET A.deleted = 1
FROM tblParent A
LEFT JOIN tblChild B ON A.PID = B.PID
WHERE B.PID IS NULL




_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1525348
Posted Sunday, December 22, 2013 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 22, 2013 11:33 PM
Points: 4, Visits: 12
Thank you, but mentioning the childtable explicitly is not an option here. A parent table can have many child tables.
Post #1525351
Posted Sunday, December 22, 2013 2:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 36,777, Visits: 31,232
sharpcnet (12/22/2013)
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),
@Id nvarchar(50),
@Return TINYINT = NULL OUTPUT --Changed here
)
AS
BEGIN
DECLARE @qry nvarchar(500)

SELECT @qry = 'begin transaction
delete '+@tablename+' where '+@colname+'='+@id+'
if(@@Error <> 0)
Begin
SELECT @Return = 0 --Changed here
End
else
Begin
rollback transaction
update '+@tablename+' set deleted = 1 where '+@colname+' = '+@id+'
SELECT @Return = 1 -Changed here
end'

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
END

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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1525382
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse