Verify SQL in stored procedure

Question

Post reply

Verify SQL in stored procedure

dlongnecker-802303

Ten Centuries

Points: 1280
More actions
March 18, 2009 at 8:30 am

#132152

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

March 18, 2009 at 11:29 am

#962130



SET PARSEONLY ON

SET NOEXEC ON

-- Do your thing

SET PARSEONLY OFF

SET NOEXEC OFF

* Noel

Lowell SSC Guru Points: 323482 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 3

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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

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.