Tricky! obtain Stored Procedure Parameters as columns in result
-
Hi
can I ask for some SQL query help ..
How would I return all parameter names for stored procedure in first row, parameter datatype in the second row of a result set.
eg. Result set:-
[font="System"]
[column names]Col1, Col2, Col3
[first row - parameter names]@Param1, @Param2, @Param3
[second row - datatype]varchar, int, varchar
[/font]
I can see the information is in this table:
SELECT PARAMETER_NAME ,
DATA_TYPE
FROM information_schema.parameters
WHERE specific_name = '<stored procedure name>'
Any ideas?
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
I suppose you could use the PIVOT functionality. Are you familiar with this? http://msdn.microsoft.com/en-us/library/ms177410.aspx
Jared
CE - Microsoft -
C# Screw (4/11/2012)
Hican I ask for some SQL query help ..
How would I return all parameter names for stored procedure in first row, parameter datatype in the second row of a result set.
eg. Result set:-
[font="System"]
[column names]Col1, Col2, Col3
[first row - parameter names]@Param1, @Param2, @Param3
[second row - datatype]varchar, int, varchar
[/font]
I can see the information is in this table:
SELECT PARAMETER_NAME ,
DATA_TYPE
FROM information_schema.parameters
WHERE specific_name = '<stored procedure name>'
Any ideas?
Off the top of my head: -
DECLARE @p1SQL NVARCHAR(MAX), @p2SQL NVARCHAR(MAX), @SQL NVARCHAR(MAX);SELECT @p1SQL = COALESCE(@p1SQL,'') + ',' + CHAR(13) + CHAR(10) +
CHAR(39) + PARAMETER_NAME + CHAR(39) + ' AS Col' +
CAST(ROW_NUMBER() OVER(ORDER BY PARAMETER_NAME) AS VARCHAR(3))
FROM information_schema.parameters
WHERE SPECIFIC_NAME = <sproc name>;
SELECT @p2SQL = COALESCE(@p2SQL,'') + ',' + CHAR(13) + CHAR(10) +
CHAR(39) + DATA_TYPE + CHAR(39) + ' AS Col' +
CAST(ROW_NUMBER() OVER(ORDER BY DATA_TYPE) AS VARCHAR(3))
FROM information_schema.parameters
WHERE SPECIFIC_NAME = <sproc name>;
SET @p1SQL = STUFF(@p1SQL,1,3,'');
SET @p2SQL = STUFF(@p2SQL,1,3,'');
SET @SQL = 'SELECT ' + CHAR(13) + CHAR(10) + @p1SQL + CHAR(13) + CHAR(10) +
'UNION ALL SELECT ' + CHAR(13) + CHAR(10) + @p2SQL;
PRINT @SQL;
EXECUTE sp_executesql @SQL;
--edit--
Bug in the order by clause. Because I change what I was ordering by in the row_number function I was introducing incorrect results. Correct in my next post and below: -
DECLARE @p1SQL NVARCHAR(MAX), @p2SQL NVARCHAR(MAX), @SQL NVARCHAR(MAX);SELECT @p1SQL = COALESCE(@p1SQL,'') + ',' + CHAR(13) + CHAR(10) +
CHAR(39) + PARAMETER_NAME + CHAR(39) + ' AS Col' +
CAST(ROW_NUMBER() OVER(ORDER BY PARAMETER_NAME) AS VARCHAR(3))
FROM information_schema.parameters
WHERE SPECIFIC_NAME = <sproc name>;
SELECT @p2SQL = COALESCE(@p2SQL,'') + ',' + CHAR(13) + CHAR(10) +
CHAR(39) + DATA_TYPE + CHAR(39) + ' AS Col' +
CAST(ROW_NUMBER() OVER(ORDER BY PARAMETER_NAME) AS VARCHAR(3))
FROM information_schema.parameters
WHERE SPECIFIC_NAME = <sproc name>;
SET @p1SQL = STUFF(@p1SQL,1,3,'');
SET @p2SQL = STUFF(@p2SQL,1,3,'');
SET @SQL = 'SELECT ' + CHAR(13) + CHAR(10) + @p1SQL + CHAR(13) + CHAR(10) +
'UNION ALL SELECT ' + CHAR(13) + CHAR(10) + @p2SQL;
PRINT @SQL;
EXECUTE sp_executesql @SQL;
A lesson to everyone, always proof read your code - even when you're only posting on a forum!! 😉
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Good lord! :Wow:
its at times like this that I am in awe!!!
Such a fast response - and that wasn't easy (in my book anyway!).
Homage and special thanks to you!!
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Spotted a bug in what I wrote.
Should be like this: -
DECLARE @p1SQL NVARCHAR(MAX), @p2SQL NVARCHAR(MAX), @SQL NVARCHAR(MAX);SELECT @p1SQL = COALESCE(@p1SQL,'') + ',' + CHAR(13) + CHAR(10) +
CHAR(39) + PARAMETER_NAME + CHAR(39) + ' AS Col' +
CAST(ROW_NUMBER() OVER(ORDER BY PARAMETER_NAME) AS VARCHAR(3))
FROM information_schema.parameters
WHERE SPECIFIC_NAME = <sproc name>;
SELECT @p2SQL = COALESCE(@p2SQL,'') + ',' + CHAR(13) + CHAR(10) +
CHAR(39) + DATA_TYPE + CHAR(39) + ' AS Col' +
CAST(ROW_NUMBER() OVER(ORDER BY PARAMETER_NAME) AS VARCHAR(3))
FROM information_schema.parameters
WHERE SPECIFIC_NAME = <sproc name>;
SET @p1SQL = STUFF(@p1SQL,1,3,'');
SET @p2SQL = STUFF(@p2SQL,1,3,'');
SET @SQL = 'SELECT ' + CHAR(13) + CHAR(10) + @p1SQL + CHAR(13) + CHAR(10) +
'UNION ALL SELECT ' + CHAR(13) + CHAR(10) + @p2SQL;
PRINT @SQL;
EXECUTE sp_executesql @SQL;
The row_number order by was different for each part which would generate incorrect results.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply