Way of checking to see if all SP's work

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

General SQL Server Forums

New to SQL Server Programming

Way of checking to see if all SP's work

Author

Topic

rternier
Starting Member

44 Posts

Posted - 2007-07-25 : 17:36:27

Our System currently has on the upwards of 300 stored procedures in it.

We have SP's that call SP's that call SP's and sometimes a Stored Procedure will return 10 ResultSets which are used by .NET.

Some of our developers do not check other stored procedures when they modify the SP parameters, and in doing so break the system until the testers find it.

is there a way to run a command to check ALL the stored procedures to see if they work, they match the SP they're calling, etc?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 17:48:25

No. Developers should create unit tests though to ensure what they are developing passes their tests at least.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rternier
Starting Member

44 Posts

Posted - 2007-07-25 : 18:00:11

Unit tests are one thing we're doing, and we Use TFS to check code for us, but we are a semi-young team, and right now we're so busy its' hard to get someone looking at how to properly use unit testings...

Our system is so large, to just put unit tests in now for the whole thing would be insane (And i'm not even that up to date on how to do it). 23 code projects, 15 web projects, 900+ code files, 100+ web pages... a lot of files to work on to implement.

I guess I could just create a script of all StoredProcedures and then execute them to see if there are any errors... but too bad there's nothing automated to check them all.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 18:04:34

Testing is the most important part of development, so if developers are not creating test scripts, then they really aren't completing the job.

If they are creating them, then it is just a matter of saving them, and maybe developing a master script to run all of them.

CODO ERGO SUM

rternier
Starting Member

44 Posts

Posted - 2007-07-25 : 18:20:47

We do test driven development, however as far as automated test scripts go (I assume you mean creating scripts to go through the website as you make it), we haven't gotten there yet.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 18:22:56

quote:
Originally posted by rternier

I guess I could just create a script of all StoredProcedures and then execute them to see if there are any errors... but too bad there's nothing automated to check them all.

How would you know what to pass to the parameters?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rternier
Starting Member

44 Posts

Posted - 2007-07-25 : 18:50:28

I was more of the line thinking:

1)this SP wants to call SP#1, and it's passing in 5 parameters of this type
2)Try passing in 5 random values just to see if there are any errors calling that SP (don't execute the SP, just check the call).

if it passes - great, if not, raise an error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 18:57:13

How would you pass in values without executing the stored procedure?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rternier
Starting Member

44 Posts

Posted - 2007-07-25 : 19:06:34

I'm thinking moreover like a "Compile" option. in .NET, if you have a function call that is passing in 5 parameters where the function only wants 4, you get an error on the compile.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 19:11:15

Good luck on this! Let us know how it turns out.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rternier
Starting Member

44 Posts

Posted - 2007-07-25 : 19:12:14

hahahahah - I was looking to see if there was a way in SQL :P I believe VS for DataBase professionals has an option for this. Wanted to know if SQL 2005 had one

Kristen
Test

22859 Posts

Posted - 2007-07-26 : 01:32:31

I think a "Lint" for SQL Server would be very helpful. That would do things like warning when a Varchar is IMPLICITLY cast to an Int, for example.

Haven't ever found one though.

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-26 : 12:01:32

I've been using this to check for broken sprocs. Note that it will find sprocs that are broken because of changes to a table's schema, but NOT if a table is dropped (SQL Server will happily compile a sproc with a missing table).

set nocount on
--RecompileSQLCode
--This script recompiles all procedures, triggers, and views in a database, and shows
--objects that fail to compile.  This script will detect code that is broken by
--schematic changes to tables, but not because of dropped tables.
--blindman, 7/21/2007

create table #SQLStrings (StringID bigint identity(1, 1), SQLString varchar(max))
create table #CompileErrors (ErrorMessage varchar(4000))
declare	@SQLString varchar(max)
declare	@ObjectName varchar(500)
declare	@ObjectType varchar(10)

declare CodeObjectList cursor for
    select	[name], [type]
    from	sys.objects
    where	type in ('P', 'TR', 'V')
    order by parent_object_id

open CodeObjectList
fetch next from CodeObjectList into @ObjectName, @ObjectType
while @@fetch_status = 0
    begin
    truncate table #SQLStrings
    insert into #SQLStrings exec sp_helptext @ObjectName
    set @SQLString = ''
    select  @SQLString = @SQLString + ltrim(SQLString) from #SQLStrings order by StringID
    set	@SQLString = left(@SQLString, charindex('CREATE ', @SQLString)-1) + 'ALTER ' + right(@SQLString, len(@SQLString)-charindex('CREATE ', @SQLString) -6)
    begin try
	exec (@SQLString)
    end try
    begin catch
	insert into #CompileErrors (ErrorMessage) values (@ObjectName + ' (' + rtrim(@ObjectType) + '): ' + error_message())
    end catch
    fetch next from CodeObjectList into @ObjectName, @ObjectType
    end

close CodeObjectList
deallocate CodeObjectList

select * from #CompileErrors

drop table #SQLStrings
drop table #CompileErrors

e4 d5 xd5 Nf6

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 01:41:33

Looks very useful blindman.

Note that

exec sp_helptext @ObjectName

will "split" lines that are overly long.

Perhaps easier to to re-create all Sprocs from Version Control? (Must admit I've none done that, we only re-create where the filedate is new, but recreating all would show up illegal references)

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-27 : 10:43:39

quote:
Originally posted by Kristen
Note that

exec sp_helptext @ObjectName

will "split" lines that are overly long.

Shouldn't matter, since I concatenate all the lines into varchar(max) anyway. But I admit that I haven't run this on any huge sprocs yet. I've been using it for a week or two now. I'd still like to find a nifty way to identify code broken from dropped tables, so any suggestions would be appreciated/incorporated.
I've also considered modifying the code to create temporary objects using a dummy name rather than running ALTER on the actual objects. This way, the code objects themselves are never modified, but I'm not sure what that would gain me or what the harm of running ALTER is.

e4 d5 xd5 Nf6

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 10:53:17

"Shouldn't matter, since I concatenate all the lines into varchar(max) anyway"

Unfortunately it doesn't split on word boundaries, so you may get a variable / column name split over two lines, or the remainder of a comment put on a line by itself!

I suppose if you used ALTER rather than DROP / CREATE and failure-to-create will be benign - you'll get an error, which you can investigate, but the original Sproc will be in-situ still.

"I'd still like to find a nifty way to identify code broken from dropped tables"

Does sysdepends help? I wonder if it records the fact that SProc has a dependency on a now-non-existent table?

Or perhaps a trigger on a system table that "logs" the sysdepends stuff when a table is dropped?

Kristen

pootle_flump

1064 Posts

Posted - 2007-07-27 : 12:00:28

quote:
Originally posted by Kristen

"Shouldn't matter, since I concatenate all the lines into varchar(max) anyway"

Unfortunately it doesn't split on word boundaries, so you may get a variable / column name split over two lines, or the remainder of a comment put on a line by itself!

This should cover it:select

...select @SQLString = @SQLString + ltrim(SQLString) from #SQLStrings order by StringID..

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-27 : 12:39:44

sysdepends won't help. SQL Server simply does not add sysdepends records if an object does not exist, and if an object is deleted the sysdepends records are deleted too.

e4 d5 xd5 Nf6

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-27 : 12:58:42

acctually you can do something like what you need

1. remove default values from your stored procedures
2. set fmtonly on
3. run
4. set fmtonly off


create proc test1
	@var1 varchar(10),
	@var2 int
as
select @var1, @var2
union all
select @var1, @var2
go

-- return just schema format and not the data
set fmtonly on

-- this is ok
exec test1 'asf', 5

-- this fails
exec test1 

set fmtonly off

go
drop proc test1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 13:16:41

"if an object is deleted the sysdepends records are deleted too"

Can you put a Trigger on that in SQL 2005? (Only half a solution, I know ...)

"This should cover it ..."

Interesting. If you just output the results from sp_HelpText there are line breaks at Column 259. However, if you output to a table, and concatenate, then presumably he CR LF were also output to the table, and are preserved in the concatenation.

CREATE PROCEDURE dbo.LongLineTest
AS
-- ..!....1....!....2....!....3....!....4....!....5....!....6....!....7....!....8....!....9....!..100....!....1....!....2....!....3....!....4....!....5....!....6....!....7....!....8....!....9....!..200....!....1....!....2....!....3....!....4....!....5....!....6
GO

sp_helptext 'LongLineTest'
GO

declare @SQLString varchar(8000)

create table #SQLStrings
(
StringID bigint identity(1, 1),
SQLString varchar(8000)
)

INSERT INTO #SQLStrings
EXEC sp_helptext 'LongLineTest'

SET @SQLString = ''
SELECT @SQLString = @SQLString + ltrim(SQLString)
FROM #SQLStrings
ORDER BY StringID

SELECT [@SQLString] = @SQLString
GO
DROP TABLE #SQLStrings
GO
DROP PROCEDURE dbo.LongLineTest
GO

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-27 : 14:54:32

quote:
Originally posted by spirit1

acctually you can do something like what you need

1. remove default values from your stored procedures
2. set fmtonly on
3. run
4. set fmtonly off

Well, step 1 would be a problem for 300 procedures...

e4 d5 xd5 Nf6

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources