March 18, 2009 at 8:30 am
I'm working on a system where changes to the database (INSERTS, UPDATES, DELETES) aren't excuted right away so they can be audited by an admin.
Is there a way I can verify that the statements will work with a stored procedure? For example, if a user trys to perform an INSERT and specifies the value of an identity column I would like to know that before the statement is audited and approved so the user can correct themselves and not the admin. Does that make sense? Coding all the possible scenarios is unrealistic.
Basically I'm looking for something like this:
DECLARE @retrun_cde int
exec sp_checksql 'INSERT INTO table ('SomeIntegerColumn') VALUES ('SomeText'), @return_cde OUTPUT
if(@return_cde != 0)
BEGIN
raiserror something something
END
---
Dlongnecker
March 18, 2009 at 11:29 am
SET PARSEONLY ON
SET NOEXEC ON
-- Do your thing
SET PARSEONLY OFF
SET NOEXEC OFF
* Noel
March 18, 2009 at 11:38 am
unfortunately you can't call SET PARSENAME inside a procedure...
so you can't wrap noeld's code and put it inside a proc; maight work as dynamic SQL, but I'd have to test if that would work.
noeld (3/18/2009)
SET PARSEONLY ON
SET NOEXEC ON
-- Do your thing
SET PARSEONLY OFF
SET NOEXEC OFF
Lowell
March 18, 2009 at 2:02 pm
If your statements don't too much changes you can work with transaction rollbacks:
IF (@@TRANCOUNT != 0)
ROLLBACK TRANSACTION
BEGIN TRANSACTION
INSERT INTO anywhere VALUES (1, 'Hello World')
ROLLBACK TRANSACTION
So everything becomes done and undone. Be careful to avoid blocking other sessions!
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 18, 2009 at 3:54 pm
Florian Reischl (3/18/2009)
If your statements don't too much changes you can work with transaction rollbacks:
IF (@@TRANCOUNT != 0)
ROLLBACK TRANSACTION
BEGIN TRANSACTION
INSERT INTO anywhere VALUES (1, 'Hello World')
ROLLBACK TRANSACTION
So everything becomes done and undone. Be careful to avoid blocking other sessions!
Greets
Flo
Just remember that if you do this, and the table has a column defined as an identity, the identity value of the column will still increment and the rollback will not set it back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply