script that will check my script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

script that will check my script

Author

Topic

nice_guy
Starting Member

3 Posts

Posted - 2008-11-17 : 15:33:37

I would like to know if it is possible to write some kind of script that will test my script, for example:
1. I would like to know if my script will run without problems (like the verify button in the mgnt studio).
2. I would like to know what kind of action my script is going to perform - let's say the i'm receiving my script from an outer source and I would like to know if it is an insert/drop/update script etc. and not according to the first word in the script but in a way that I can really trust.
3. I would like to know what kind of object my script works on - view/sp/table etc.

for example, in the following script I would like to know that it is an update script on a stored procedure and that it will run without any problems:

alter procedure x
@x int
as
update students set name='john' where studentId=@x
go

is it possible to do what i'm asking here just by script without any manual process?

Jason100
Starting Member

34 Posts

Posted - 2008-11-18 : 00:16:24

alter procedure x
@x int
as
BEGIN TRY
update students set name='john' where studentId=@x
RETURN 1
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
RETURN -1
END CATCH;
GO

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-21 : 10:54:41

You can test procedure using transactions and rollbacks:

DECLARE @ix as integer;
DECLARE @iy as integer;

BEGIN TRAN
--check before change
set @ix= select ...................
PRINT @ix
BEGIN
--Update query here
.........
........
END
--check changes
Set @iy = select ............
PRINT @iy
BEGIN
ROLLBACK TRAN
END
-- if you want to commit un-comment lines below:
--ELSE
--BEGIN
--Commit
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-21 : 11:21:45

The answer is:
I don't know about SOME KIND OF SCRIPT that is possible to do it altogether.
But it is not possible to write a T-SQL Script to do it.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources