Validating Stored Procedure results
-
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?
-
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.
-
I tried this and it keeps returning 0. The syscolumns isn't updating with the temp table.
-
I just tried it with a real table and that worked. Thanks!
-
You probably weren't running from the tempdb database. Just use a full path to the table: tempdb.dbo.syscolumns.
-
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?
-
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)?
-
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.
-
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.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply