Hôm nay mình chia sẽ với mọi người một Procedure trả về câu lệnh tạo cấu trúc của một "bảng trả về" khi thực thi một Procedure.

Generate table structure from result of "exec procedure" in sql server

Khi bạn muốn thực thi một procedure sao đó trả về một table, và trong table đó, bạn muốn update delete hoặc sumary gì đó, ... trước khi trả table lại cho client, bạn sẽ phải sử dụng cấu trúc lệnh SQL:

Xample:

Giả sử bạn có một procedure USP_Get_sinhvien trả về một table gồm 2 trường là MaSV, HoTen. Bạn muốn thực thi nó và đổ dữ liệu vào một bảng tạm:

CREATE TABLE #sv_tmp (MaSV varchar(50), HoTen NVARCHAR(50))
DECLARE @q nvarchar(4000)
SET @q = 'USP_Get_sinhvien'

INSERT INTO  #tmp (MaSV , HoTen)
EXEC sp_executesql (@q)

SELECT * from #sv_tmp ;

Như trên, bạn phải tạo ra một cấu trúc table temp đúng như table bạn sẽ trả về từ câu truy vấn (hoặc là một store procedure). Điều này gây mất thời gian vì thực tế bạn sẽ cần create nhiều field hơn là chỉ 2 như ví dụ trên. Và sẽ khó khăn hơn khi bạn update cái store procedure đó thay đổi số lượng và cấu trúc table trả về, đồng nghĩa bạn cũng phải sửa lại cấu trúc table temp trên. 

Solution:

CREATE FUNCTION UFN_Get_table_structure_from_Proc( @proc_name VARCHAR(100), @temp_table_name VARCHAR(20))
RETURNS NVARCHAR(MAX)
BEGIN
	DECLARE @SPNAME sysname;

	SET @SPNAME =@proc_name; 

	DECLARE @TMPTABLE sysname;

	SET @TMPTABLE = N'#' + @temp_table_name;

	DECLARE @DELIM NVARCHAR(4) =   N', ' + NCHAR(10);

	DECLARE @SQL NVARCHAR(MAX);

	SET @SQL = N'CREATE TABLE ' + @TMPTABLE + N' ( ';

	WITH rowDefs AS (

		SELECT TOP(100) PERCENT name + ' ' + system_type_name  AS RowDef, column_ordinal

		FROM sys.dm_exec_describe_first_result_set(N'exec ' + @SPNAME,NULL,1)

		WHERE is_hidden = 0

		ORDER BY column_ordinal

	 )

	SELECT DISTINCT

		@SQL = @SQL + STUFF ( ( SELECT @DELIM + RowDef

			  FROM rowDefs t1

		FOR XML PATH ( '' ) ) , 1 , 1 , '' )

	FROM rowDefs t2

	SELECT @SQL = @SQL + N' );'

	RETURN @SQL;
END

Với function này sẽ tạo ra một câu lệnh 

CREATE TABLE #tmp_name(field1 TYPE, field2 TYPE,...)

một cách tự động và tùy vào cấu trúc trả về của procedure name truyền vào.

Usage:

DECLARE @sql_create_table nvarchar(max)
set @sql_create_table = dbo.UFN_get_table_structure_from_Proc ('USP_Get_sinhvien', 'temp_sv')

exec(@sql_create_table );
--Test
--select * from #temp_sv;

INSERT INTO  #temp_sv
EXEC EXEC USP_Get_sinhvien;

SELECT * from #temp_sv;

Trên đây là cách các bạn có thể trả về một cấu trúc table khi exec một store procedure. Việc create table temp phụ thuộc vào store procedure bây giờ sẽ đơn giản hơn nhiều rồi :))

Chúc các bạn thành công !

HAPPY CODING <3<3<3