August 18, 2009 at 4:45 pm
I am trying to determine a way in MS SQL 2000 to get access to the list of columns returned from a stored procedure that returns a single set of results. I need to be able to take this column data and match it with a table containing "friendly names" in a user defined table and present it in my UI. I know how to get access to this information using ADO.Net, but I would like to have this all done on the server so I only have one hit to the server rather than several from the client to accomplish the same thing.
I have been unable to find any function or stored procedure that provides access to this data within MS SQL even though it is obviously available through ADO, ADO.Net, OLEDB, ODBC, etc... Any help or direction would be appreciated.
Scott Travis
August 18, 2009 at 4:52 pm
August 18, 2009 at 4:58 pm
Thanks, I tried this, but it only gets me the same result set without any data. If I run this through a query tool then I see the column names, but I need to know how I can access those column names directly within a SQL Script so they can be joined with another table.
What I need is some function or stored procedure that provides dynamic reflection of query results. All I know of and can find in MS SQL is metadata functions that query stored metadata.
-Scott Travis
August 18, 2009 at 5:26 pm
It is kind of convoluted, but what do you all think of returning the results as XML then querying that XML for the column data? I have been trying to do this but have been unable to cache the "FOR XML" data to a variable or temporary table using standard SQL. Any ideas on if this could work or not?
Thanks,
Scott Travis
August 19, 2009 at 5:24 am
There is another convoluted way of getting this - http://www.sqlservercentral.com/Forums/Topic550843-145-2.aspx#bm605174
August 19, 2009 at 9:06 am
stravis (8/18/2009)
Thanks, I tried this, but it only gets me the same result set without any data. If I run this through a query tool then I see the column names, but I need to know how I can access those column names directly within a SQL Script so they can be joined with another table.What I need is some function or stored procedure that provides dynamic reflection of query results. All I know of and can find in MS SQL is metadata functions that query stored metadata.
-Scott Travis
Yes, when running this through SSMS, all you see is empty column names, but it returns more than that. It returns the metadata for the result set (column name, datatype). I'm not sure how to capture that metadata, but SSIS uses calls to the database engine with SET FMTONLY ON to get metadata during design time and run time validation of stored procedures and SQL queries. This is where my knowledge on the subject ends. There's got to be a way to programatically read in those results because SSIS is able to. How? I'm not sure. Can you create an ADO recordset object from the empty result set and get the metadata from that?
August 19, 2009 at 9:09 am
stravis (8/18/2009)
It is kind of convoluted, but what do you all think of returning the results as XML then querying that XML for the column data? I have been trying to do this but have been unable to cache the "FOR XML" data to a variable or temporary table using standard SQL. Any ideas on if this could work or not?Thanks,
Scott Travis
You'd have to re-code all of your SPs to return XML. That could be a ton of work.
August 19, 2009 at 10:20 am
Thanks John,
SSIS and many other client applications read the information into a recordset and then process it in a programming language other than SQL to get the column names. I've profiled SSIS, Crystal, and a couple of other tools to see how they did it and there is simply no additional SQL.
I was able to code an XML based solution, but it only works on SQL 2005 and above due to the inability to assign the XML data in SQL 2000. The code for getting the XML without rewriting the stored procedure is below. I didn't bother writing the second part to query the XML data to get all the column names because it wasn't going to work for with SQL 2000.
USE tempdb
GO
IF OBJECT_ID('dbo.TestSP') IS NOT NULL
DROP PROCEDURE dbo.TestSP;
GO
IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = 'Loopback')
BEGIN
EXEC sp_addlinkedserver @server = 'Loopback', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = @@servername
END
GO
CREATE PROC dbo.TestSP
AS
SELECT 1 as [ID], 'Name' as [Name], 'Boston' as [City]
GO
DECLARE @MyXML XML
SELECT @MyXML = (SELECT * FROM
(SELECT *
FROM OPENQUERY(Loopback,'SET FMTONLY ON;
EXEC tempdb.dbo.TestSP;
SET FMTONLY OFF')) vw FOR XML AUTO, XMLSCHEMA)
SELECT @MyXML
I did code a solution that relies on .Net coding and only three SQL calls. The method I used is below.
1. Built a method in .Net that takes a stored procedure name and connection
2. Derive parameters for stored procedure in .Net (SQL call 1)
3. Assign default values to parameters based on datatype in .Net
4. Execute stored procedure and return results to dataset (SQL call 2)
5. Loop through columns returned and build table of columns manually in new data table in dataset and build column separated list of column names
6. Send column separated list to stored procedure that returns matched columns (see below; SQL call 3)
CREATE PROC [dbo].[usp_get_field_friendly_names]
@CommaDelimitedColumnList as NVARCHAR(1000)
AS
select
i.Field, i.Friendly_Name
from [dbAPS].[dbo].[_api] i
where
patindex('%' + rtrim(CAST(i.[Field] as VARCHAR(100))) + ',%', @CommaDelimitedColumnList) > 0 OR
patindex('%' + rtrim(CAST(i.Field as VARCHAR(100))), @CommaDelimitedColumnList) > 0
GO
7. Merge data from data table built in step 5 with results from step 6 using data view against dataset in .Net
This solution is a little long winded and didn't meet my original specification of having SQL handle the heavy lifting, but it did work and I managed to make only one more round trip than I would have had to do otherwise. I am going to see about using the other suggestion on this topic and see if I can translate it to SQL 2000.
August 19, 2009 at 1:32 pm
Thanks winash,
I've adapted your solution to SQL Server 2000 and made it so that it could use any stored procedure and it seems to work great. This is definitely a hack, but it does work. The solution below would need to be expanded for additional data types and I know that it will not work if you have two columns with the same name in the result set. My final solution will be to take the results returned from this stored procedure and combine them with the friendly names table.
I'm sure that there are ways to make this more effecient, but at least this works.
Thanks to everyone for your help.
CREATE PROC [dbo].[usp_get_sproc_column_names]
@SPROCNAME AS NVARCHAR(200)
AS
SET NOCOUNT ON
DECLARE @parameter_name VARCHAR(100), @data_type VARCHAR(100), @sqlexec NVARCHAR(1000), @first_parameter BIT
DECLARE @temp_table_name NVARCHAR(100)
-- generate temp table name
SELECT @temp_table_name = '##RESULTS_' + LTRIM(RTRIM(CAST(CAST((RAND() * 100000) as INT) as VARCHAR(100))))
DECLARE parameter_cursor CURSOR FOR
SELECT
PARAMETER_NAME,
DATA_TYPE
from INFORMATION_SCHEMA.PARAMETERS
where specific_name = @SPROCNAME AND PARAMETER_MODE = 'IN'
order by ordinal_position
OPEN parameter_cursor
FETCH NEXT FROM parameter_cursor INTO @parameter_name, @data_type
SET @sqlexec = 'SET FMTONLY ON;EXEC ' + DB_NAME() + '.dbo.' + @SPROCNAME + ' '
SET @first_parameter = 1
-- create parameter list with default values
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@first_parameter = 1) BEGIN
SET @sqlexec = @sqlexec + @parameter_name + ' = '
SET @first_parameter = 0
END ELSE BEGIN
SET @sqlexec = @sqlexec + ', ' + @parameter_name + ' = '
END
SELECT @sqlexec = CASE
WHEN @data_type IN ('varchar', 'nvarchar', 'char', 'nchar', 'text') THEN
@sqlexec + dbo.ufn_quotestring('')
WHEN @data_type IN ('bit', 'tinyint', 'smallint', 'int', 'bigint', 'decimal', 'float') THEN
@sqlexec + '0'
ELSE
@sqlexec + 'NULL'
END
FETCH NEXT FROM parameter_cursor INTO @parameter_name, @data_type
END
SET @sqlexec = @sqlexec + ';SET FMTONLY OFF'
CLOSE parameter_cursor
DEALLOCATE parameter_cursor
-- cleanup old data
IF (OBJECT_ID('tempdb..' + @temp_table_name) IS NOT NULL)
BEGIN
DROP TABLE ##RESULTS
END
-- create query string and execute
DECLARE @sqlOpenQueryExec NVARCHAR(1200)
select @sqlOpenQueryExec = 'SELECT * INTO ' + @temp_table_name + ' FROM OPENQUERY(Loopback,' + dbo.ufn_quotestring(@sqlexec) + ')'
EXEC sp_executesql @sqlOpenQueryExec
-- return results
SELECT
C.column_name
FROM tempdb.INFORMATION_SCHEMA.COLUMNS C
WHERE OBJECT_ID(C.TABLE_CATALOG+'.'+C.TABLE_SCHEMA+'.'+C.TABLE_NAME) = OBJECT_ID('tempdb..' + @temp_table_name)
-- cleanup
DECLARE @cleanupScript NVARCHAR(100)
SELECT @cleanupScript = 'DROP TABLE ' + @temp_table_name
EXEC sp_executesql @cleanupScript
GO
Scott Travis
August 19, 2009 at 1:35 pm
I forgot to get add the code for the custom function ufn_quotestring() found at http://www.sommarskog.se/dynamic_sql.html
CREATE FUNCTION [dbo].[ufn_quotestring](@str nvarchar(1998)) RETURNS nvarchar(4000) AS
BEGIN
DECLARE @ret nvarchar(4000),
@sq char(1)
SELECT @sq = ''''
SELECT @ret = replace(@str, @sq, @sq + @sq)
RETURN(@sq + @ret + @sq)
END
GO
August 20, 2009 at 2:15 am
Thanks for posting the modified code Travis. This is a neater implementation and something that I'd also be re-using 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply