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 8 (of 8 total)

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