Getting SQL Data Types of Stored Procedure Results

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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting SQL Data Types of Stored Procedure Results

Author

Topic

elmalloc
Starting Member

13 Posts

Posted - 2004-07-24 : 19:42:56

Hello everyone,

I am in dire need to get the SQL Data types of a given stored procedure. I am doing some code generation work, and I'm working on generating off of a stored procedure. Now, via C# code, I'm making a call to the DB to execute the stored procedure - and I need to get the structure of the returned results.

This is getting very difficult. Select * into [TABLE] FROM does not work with an executed stored procedure. It will work with "OPENQUERY", but the problem with openquery is that after making a linked server to myself, the resulting table does NOT have UDTs in it.

For example, in both the master DB and in the DB I'm creating the table in, I have a UDT of EmailAddress (varchar). When I create the table based off of the executed stored procedure, I get the email address columns as "VARCHAR" datatypes instead of "EMAILADDRESS". This is a major hindrance, as a straight select * into TEMPTABLE from the table that has the emailaddress UDT in it, creates the TEMPTABLE table with the UDT in it (when running SP_HELP against it).

What can I do to get a stored procedure's return columns SQL data types (including correct UDTs)?

I have tried so many differnt methods and I'm pulling my hair out over this one. UDF can't have temp tables or call sql procedure within them (so that's scratched as a select * into TEMPTABLE from dbo.MyFunction()). The ADO.NET dataset has the .NET data types, not the Sql data types (i.e. string for my emailaddress). The ADO.NET SqlDataReader just has "varchar" for the underlying SQL data type, and not the email address.

Is this an inherent problem with sql stored procedures in returning the underlying type and not the UDT? Remember, the same stored procedure text executed as a select statement is giving me the correct UDT.

Any help at all?

Thanks,
ELmO

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 03:43:59

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36303
might help

Kristen

elmalloc
Starting Member

13 Posts

Posted - 2004-07-25 : 09:15:21

Thanks Kristen, it doesn't help though because he is going on a table basis and I'm trying to execute a stored procedure and get the returned SQL data types. It's incredible that doing a straight select statement on different tables works fine, but executing a stored procedure for tables is not so easy to get the returned SQL data types.

-ELmO

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 14:34:35

Hmmm ... looks like all the original type information is gone before it gets to the stage you are looking at, which doesn;t seem all that unreasonable to me - VB is not going to be interested to know that a particular column in a recordset is of type EMAILADDRESS - which is non-standard ADO!!.

Also, what about if the SProc returns multiple recordsets? The SELECT * INTO approach (whether via OPENQUERY or not) is going to cough at that point.

Can you explain what you are trying to achieve such that someone here might be able to come up with a different suggestion?

Kristen

elmalloc
Starting Member

13 Posts

Posted - 2004-07-25 : 15:30:13

I'm not looking at ADO.NET, I've already went down thate route. I'm flexible in what I can and can't do (IE I won't "support" having multiple result sets coming back.) I'm just trying to get the SPROC results data types (including the correct UDT).

I'm doing code generation. Based off a stored procedure's result data types, I generate appropriate GUI widgets in a datagrid (Email address is an email icon, when they click on it - it opens a mailto link, for example). I have a couple of UDTs I use to work with. This is only a very small slice of the generator project I'm working on. Right now it supports reporting datagrids off any stored procedure, it creates javascript panels for the procedure parameters, and querystring to set public propertise for the SPROC params. It shows the results in a datagrid of auto bound columns.

I need to get the data types of the columns returned so I can generate code for button/template columns correctly. IE if it's a datetime field, I only show the date portion (I'm not using the time side), so I need to stick a dataformatter on it (as another example).

We've done some great work with just selecting a table and creating DB access code/gui widgets for viewing/editing the data in it (and getting statistics/graphs/etc), but it's getting difficult to support stored procedure generation with datagrid column support.

When the user selects a stored procedure from a given DB, it gets the parameters of the stored procedure via an SQL SP call and then passes null for all of them to run the procedure.

What I don't understand is this. With the same select * INTO statement (using a select statement), it brings back the UDTs. An OPENQUERY statement for SELECT * INTO does not bring back the UDTs. I have tried numerous other methods, such as tryign ot create a view from the stored procedure and run SP_HELP against that, creating a UDF and calling that for the select * into, amongst other things. I have a feeling it's just not possible, but someone must have a work around for me.

It can be as slow a method as it wants, this is only run once during generation time anyway.

I hope someone can help me out and I explained the situation better,
ELmO

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 01:39:34

I reckon the deal with OPENQUERY is that the inner task doesn't know that the outer task is SQLServer, so it cuts back to basic datatypes. But that's kinda irrelevant 'coz it doesn't do what you want.

If you are in control of the SProcs too then the only thing I can suggest is to get them to provide some "Hints".

For example, in your SProcs you could do
[CODE]
SELECT MyName,
MyAddress,
MyTown,
'STATE' AS HINT,
MyState,
'EMAILADDRESS' AS HINT,
MyEMailAddress
FROM MyTable ...
[/CODE]
or even have an additional parameter, defaulting to "False" which, when True, output an additional "helper" recordset describing any special characteristics of the recordset(s) within the SProc. Your GUI-generator conuld then interogate all SProcs (or perhaps, just those that had the special "HelperFlag" as a declared parameter) to get what it wants; the developers could put the relevent info into those SProcs that need it; and it would be capable of allowing support for multiple recordsets.

But its a bit out-of-the-box so I don't know whether it gets you anywhere ...

Kristen

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-26 : 01:58:40

Not sure if I've got the wrong end of the stick, but:-

If you're only looking for the data type of the SP's INPUT and OUTPUT columns, have a look at the syscolumns table. This will tell you all you need to know.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-26 : 03:52:36

I think I found a way to do what you want. Some supporting documentation:

Details on server-side cursors
[url]http://jtds.sourceforge.net/apiCursors.html[/url]
VERY interesting article on SQL Server Query Processor Internals and Architecture.
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp[/url]

This should do it:

-- Setup


EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'

Create table t_testTypes (Fname varchar(20), Phone telephone)
INSERT t_testtypes Values ('Joe','555-1212')
INSERT t_testtypes Values ('Sam','888-1212')

CREATE PROCEDURE p_testTypes As
BEGIN
	SELECT Fname, Phone from t_Testtypes
END

--Get UDT for sp results
DECLARE @cursor integer

--Create server side cursor to open procedure
EXEC sp_cursoropen @cursor output, N'exec p_TestTypes', 4, 2 

--Name cursor
EXEC sp_cursoroption @cursor, 2, 'myCursor'

--Cursor columns are stored in master..syscursorcolumns for server  cursors. type_name() returns UDT name
SELECT column_name, type_name(data_type_sql) TypeName
FROM master.dbo.syscursorcolumns 
WHERE cursor_handle = @cursor

--Close cursor. Retrieve no rows.
exec sp_cursorclose @cursor 


column_name     TypeName
--------------- ---------
Fname           varchar
Phone           telephone

Do I get to be listed in the Credits of your new app

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 04:29:21

V. Crafty. Krafty even ...

Kristen

elmalloc
Starting Member

13 Posts

Posted - 2004-07-26 : 04:50:53

I will be paying you via paypal if this works! If not, I will still pay you a smaller amount for trying. I will check it out later today!

Thanks,
ELmO

elmalloc
Starting Member

13 Posts

Posted - 2004-07-26 : 05:04:15

I couldn't resist, I had to try it. It worked, son. How much would you like for this? I'm not rich, though how much time did you spend on it?

I had to remove "SET NOCOUNT ON" from the procedures I've written, but they work fine otherwise. At generation time, I will let the user select the procedure, and strip out the "SET NOCOUNT ON" if they have it - then run that procedure to get the data types. A lot of work, but well worth it.

Thanks,
ELmO

elmalloc
Starting Member

13 Posts

Posted - 2004-07-26 : 05:11:06

I will also post an image (and website of an example web application that the code generator does now) after I implement this. I will show you the datagrid (and it's capabilities) for reporting that you have now bridged the gap for stored procedure report generation, after I am done in a couple of days.

Thanks to both Kristen and KSelvia,
ELmO

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 05:13:24

I'll take all the credit for making such a hash of it that Ken decided he had to wade in and sort the mess out!

Kristen

elmalloc
Starting Member

13 Posts

Posted - 2004-07-26 : 05:16:29

lol

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-07-26 : 05:51:28

Umm... Don't you just need to specify SET FMTONLY ON?
This executes any sql statement and returns just the column heading of all result sets.

DavidM

"Always pre-heat the oven"

elmalloc
Starting Member

13 Posts

Posted - 2004-07-26 : 07:38:35

I needed the SQL data types of the results.

Thanks,
ELmO

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-26 : 11:28:34

Glad it worked! No payment necessary but the gesture is nice. Actually it didn't take long once I hit uppon the idea, but a google for the undocumented table syscursorcolumns will show you just how rare an animal it is :) I acutally tracked it down by profiling exec sp_describe_cursor_columns on the open cursor.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-26 : 14:34:47

BTW: I don't know if anyone noticed, but this technique is also a solution for the often asked question, 'How can I select results of a stored procedure into a table without first knowing the table structure and without using OPENQUQRY?' With a little more code and dynamic SQL this would allow you to build the (temp) table with appropriate column names/types.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

elmalloc
Starting Member

13 Posts

Posted - 2004-07-26 : 17:47:23

Thanks Ken.

For your efforts, I will at least post numerous pictures of the Fujitsu P7010 lifebook laptop I just bought (10.6" widescreen laptop).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources