Create sample table and insert sample data :
CREATE
Table
Test (Id
INT
NOT
NULL
Primary
Key
,
Name
VARCHAR
(100))
INSERT
Test
SELECT
1 ,
'Sathya'
GO
Create sample Stored procedure :
PROC TestProc
AS
BEGIN
DECLARE
@SQL NVARCHAR(
MAX
) =
'SELECT *, FROM Test'
IF EXISTS (
1
FROM
sys.dm_exec_describe_first_result_set(@SQL,
, 0)
WHERE
error_message
IS
AND
error_number
error_severity
error_state
error_type
error_type_desc
)
column_ordinal = 0
END
ELSE
EXEC
(@SQL)
To execute Stored procedure :
TestProc
ALTER
'SELECT * FROM Test'
IF EXISTS(
Richard Mueller edited Revision 3. Comment: Fixed the tag "SQL Server 2012"
Naomi N edited Original. Comment: Nice article!
Dear SathyanarrayananS,
First of all, thanks for your efforts and thanks to Naomi that is everywhere and correct everything. I love this function because I needed it since 2008 in our ERP application. But the purpose of this function is to identify the headers of output result set of the procedures. It's so helpful in MVVM architecture particularly in dynamic applications such ERPs that we need to know what are the output columns names and data types from one procedure that created out of the c# source code.
I expected by the title, that this is such PARSE function. But its not.
By the way, what is the role of TRY/CATCH block? In TRY/CATCH block we execute once and get syntax error. But in this approach we execute it twice, if there is no errors. This add extra performance problems in large dynamic procedures particularly in OLTP databases with many transactions and users.
Regards,
Saeid