Stored procedure should only update supplied values.
-
Hello guys! In my database I need to be able to update my data. Funny huh? ;) Well I thought if it was possible to create a stored procedure, that only updates the data supplied by the program. So any data not supplied, would simply stay untouched. An example. We have a table "Persons". The columns are: "PersonId", "FirstName", "LastName", "Age". Now little Lisa has a birthday, and we need to change her age. So instead of supplying the SP with all the data of Lisa. I would like to be able to only supply the new value of "Age", and of course the "PersonId" for identification. All other values should remain the same. But some day little Lisa gets married, and we need to change her last name. At that time, I would like to supply the same SP with only the new value "LastName", and all the other data should remain the same. Is this possible? Or is it just not the way to do things? Best regards Soeren
-
Hello guys! In my database I need to be able to update my data. Funny huh? ;) Well I thought if it was possible to create a stored procedure, that only updates the data supplied by the program. So any data not supplied, would simply stay untouched. An example. We have a table "Persons". The columns are: "PersonId", "FirstName", "LastName", "Age". Now little Lisa has a birthday, and we need to change her age. So instead of supplying the SP with all the data of Lisa. I would like to be able to only supply the new value of "Age", and of course the "PersonId" for identification. All other values should remain the same. But some day little Lisa gets married, and we need to change her last name. At that time, I would like to supply the same SP with only the new value "LastName", and all the other data should remain the same. Is this possible? Or is it just not the way to do things? Best regards Soeren
Why not just create two stored procedures? say ChangeAge and ChangeLastName You could create some dynamic SQL in a stored procedure and execute a varchar, but you are getting into the territory of opening yourself up to security risks (like SQL Injection Attacks). It is better, if you can, to use a solution that does not involve dynamic SQL. If you do have to use it then you need to implement extra safe guards to prevent an attack. ColinMackay.net Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
-
Hello guys! In my database I need to be able to update my data. Funny huh? ;) Well I thought if it was possible to create a stored procedure, that only updates the data supplied by the program. So any data not supplied, would simply stay untouched. An example. We have a table "Persons". The columns are: "PersonId", "FirstName", "LastName", "Age". Now little Lisa has a birthday, and we need to change her age. So instead of supplying the SP with all the data of Lisa. I would like to be able to only supply the new value of "Age", and of course the "PersonId" for identification. All other values should remain the same. But some day little Lisa gets married, and we need to change her last name. At that time, I would like to supply the same SP with only the new value "LastName", and all the other data should remain the same. Is this possible? Or is it just not the way to do things? Best regards Soeren
Hi I also think Colin's idea is good. well-done Colin. regards, chandana