question

Amruta avatar image
Amruta asked

sys.dm_exec_describe_first_result_set inside a SQL Function doesnt return all rows

I created a function to check the sql syntax:

CREATE FUNCTION [dbo].[fn_Check_SQL_syntax]
(
  @SQL  varchar(max)
 
)
RETURNS VARCHAR (max)
AS
BEGIN
DECLARE
@ErrorMessage VARCHAR(MAX),
select @ErrorMessage = isnull(string_agg(error_message, ' '),'SQL sucessfully parsed')
from sys.dm_exec_describe_first_result_set (@sql, null, 0)
RETURN 
@ErrorMessage;

END

and

when I call the function

select [dbo].[fn_Check_SQL_syntax]('select abc, xyz , 1 from [mytable]')

I only get the first 2 rows saying

Invalid column name 'abc'. Invalid column name 'xyz'.

but if I execute it like this

select isnull(string_agg(error_message, ' '),'SQL sucessfully parsed')
from sys.dm_exec_describe_first_result_set ('select abc, xyz , 1 from dbo.Meta_Model', null, 0)

I get

Invalid column name 'abc'. Invalid column name 'xyz'. The batch could not be analyzed because of compile errors.

Could you please explain why am I not getting all the rows within a function.

Thanks

sql-server-2016
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Does the table have columns named abc & xyz?

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.