November 12, 2004 at 9:02 am
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?
November 12, 2004 at 9:39 am
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.
November 12, 2004 at 11:50 am
I tried this and it keeps returning 0. The syscolumns isn't updating with the temp table.
November 12, 2004 at 11:50 am
I just tried it with a real table and that worked. Thanks!
November 12, 2004 at 12:49 pm
You probably weren't running from the tempdb database. Just use a full path to the table: tempdb.dbo.syscolumns.
November 12, 2004 at 12:52 pm
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?
November 12, 2004 at 1:29 pm
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)?
November 12, 2004 at 1:36 pm
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.
November 12, 2004 at 2:18 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy