Validating Stored Procedure results

Question

Post reply

Validating Stored Procedure results

Cheddar

SSC Eights!

Points: 886
More actions
November 12, 2004 at 9:02 am

#60255

Does anyone know a way to validate the results of a stored procedure? I have a routine that checks for updates to procedures and validates the format of the data returned but the only way I can do that is to dump the results to a temp table. I can't insert into a temp table if the number of columns doesn't fit the format I am expecting. Is there any way I can get the number of columns being returned from a procedure without inserting it to another table?

Viewing 9 posts - 1 through 8 (of 8 total)

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

dongadoy SSC Eights! Points: 870 More actions · Answer 1

Use openrowset and syscolumns.

select *

into #t

from openrowset('SQLOLEDB','server';'login';'password','exec tempdb.dbo.test')

select number_of_columns = count(*) from syscolumns where id = object_id('#t')

Assuming procedures always return only one resultset.

Cheddar SSC Eights! Points: 886 More actions · Answer 2

I tried this and it keeps returning 0. The syscolumns isn't updating with the temp table.

Cheddar SSC Eights! Points: 886 More actions · Answer 3

I just tried it with a real table and that worked. Thanks!

dongadoy SSC Eights! Points: 870 More actions · Answer 4

You probably weren't running from the tempdb database. Just use a full path to the table: tempdb.dbo.syscolumns.

Cheddar SSC Eights! Points: 886 More actions · Answer 5

That works, thanks. Now I am running into the problem with the SELECT INTO and no column names being specified. I want the process of making these procedures real easy for people making these. Do you have any suggestions for that?

dongadoy SSC Eights! Points: 870 More actions · Answer 6

No way to handle that in sql.

use xp_cmdshell to exec procedure using osql or isqlw. capture results of xp_cmdshell. parse the text.

create #t(t varchar(255), ln int identity)

insert #t exec master.dbo.xp_cmdshell 'osql ....

select * from #t order by ln

Don't know what you're doing; but it seems like a lot of trouble.

Does the procedure results have to fit an expected structure (i.e. same columns and data types)?

Cheddar SSC Eights! Points: 886 More actions · Answer 7

All of the procedures (over 60 of them) need to be in the same format. I created a process that executes each of these and moves the data into a global table. It's just a collection of data from all of our databases that has been pared down in a general way. I don't want to have the developers to create a procedure and then debug the big overnight feed so I am developing a compiler-type procedure that goes through the changed procedures and validates (their data-types and the order of the columns etc.) The xp_cmdshell is out of the question, I won't get sign-off from the dba to use this. Thanks for all your help.

It is a lot of extra work, but its all in the name of making myself redundant.

dongadoy SSC Eights! Points: 870 More actions · Answer 8

Do the work outside of sql server (jscript, vbscript, vb, etc.). Those languages are completely dynamic when looking at query results.

You could also use the sp_OAxxx procedures with ADO to execute the query and verify the results. But if your dba won't let you use xp_cmdshell, he's not going to allow you to use sp_OAxxx.