PIVOT Result Set WIP

 declare @sql nvarchar(max) = N'select top 10 * from dbo.TierReport where yy=''2017'' and mm=''01''';


select * from sys.dm_exec_describe_first_result_set(@sql,null,1)

exec sp_executesql @stmt=@sql


set nocount on;

declare 
  @sql nvarchar(max) = N'select top 10 * from dbo.TierReport where yy=''2017'' and mm=''01'''
, @temp_table_name sysname = N'#pivot_resultset';


select 'create table ' + @temp_table_name + '(' 
select '[' + [name] + '] ' + [system_type_name] + ',' from sys.dm_exec_describe_first_result_set(@sql,null,1)
select 'eof int'  
select ');'


select 'insert ' + @temp_table_name
select 'exec sp_executesql @stmt=@sql'

select 'select * from ' + @temp_table_name



Table has 0-N custom attributes.  Want to get 1-N custom attributes into an XML document.

CREATE PROCEDURE [dbo].[CAQH_PivotOfficeCustomAttributeData]
as
begin
set nocount on;

truncate table [dbo].[CAQH_OfficeCustomAttribute];

select
  EntityId,
 '<CustomAttribute Name=' + '''' + AttributeName + '''' 
  +  ' DataType=' + '''' + DataTypeCode + '''' 
  +  ' Value=' + '''' + AttributeValue + '''' + ' />' xmlPayload
into #RawOfficeCustomAttribute
from [Medimart].[ExportOfficeCustomAttributeData]

declare 
  @entityid int
, @xmlpayload varchar(max)
, @preventityid int = -1
, @customattribute varchar(max) = '';

declare xml_cursor cursor static for
select EntityId, xmlPayload from #RawOfficeCustomAttribute
order by EntityId;

open xml_cursor;
fetch next from xml_cursor into @entityid, @xmlpayload;

while @@fetch_status = 0
begin
if @entityid <> @preventityid
begin
if len(@customattribute) > 0
begin
set @customattribute += '</xml>'
insert [dbo].[CAQH_OfficeCustomAttribute] 
(EntityId, XmlPayload)
values
(@preventityid, @customattribute);
end
set @customattribute = '<xml>' + @xmlpayload;
set @preventityid = @entityid;
end
else
begin
set @customattribute += @xmlpayload;
end

fetch next from xml_cursor into @entityid, @xmlpayload;
end

if len(@customattribute) > 0
begin
set @customattribute += '</xml>'
insert [dbo].[CAQH_OfficeCustomAttribute] 
(EntityId, XmlPayload)
values
(@preventityid, @customattribute);
end

close xml_cursor;
deallocate xml_cursor;

end




Comments

Post a Comment

Popular posts from this blog

Using sp_executesql with OPENQUERY

 gemini prompt: give me the in depth details on using openquery in sql server with the sp_executesql stored procedure The OPENQUERY function in SQL Server allows you to execute a pass-through query on a specified linked server . By design, OPENQUERY 's arguments—the linked server name and the query string— do not accept local T-SQL variables . However, you can overcome this limitation, particularly for parameterizing the query string, by using dynamic SQL executed via the system stored procedure sp_executesql . Here is an in-depth breakdown of how to use OPENQUERY with sp_executesql to include variable values in your remote query. 1. The Challenge: Parameterizing OPENQUERY The fundamental restriction of OPENQUERY is its syntax: SQL OPENQUERY ( linked_server , 'query' ) Neither linked_server nor the 'query' string can be a local SQL Server variable (e.g., @MyLinkedServerName or @MyQuery ). To pass a variable (like a specific CustomerID or a date range) into...
Read more

Executing Remote Queries Safely and Efficiently with sp_executesql

 gemini prompt: provide a blog post by a database professional on the best practice for using sp_executesql to query a remote sql server database Executing Remote Queries Safely and Efficiently with sp_executesql As database professionals, we often face the need to query data from a remote SQL Server instance . While options like simple four-part names (e.g., [Server].[Database].[Schema].[Table] ) or OPENQUERY exist, using sp_executesql in conjunction with linked servers provides a powerful, secure, and flexible approach, especially when dealing with dynamic SQL. Here's a breakdown of the best practices for leveraging sp_executesql for remote queries. 1. Why sp_executesql for Remote Queries? When executed locally, sp_executesql is primarily used to execute dynamic SQL safely by allowing you to parameterize the query string, which is the fundamental defense against SQL injection . When querying a linked server, you can use it to execute code directly on the remote server...
Read more