Dynamisches SQL Rückgabe als Tabelle

[Arne Drews]

Hallo,

Ich habe mir die Query aus https://entwickler-forum.de/showthread.php/76113-T-SQL-PIVOT-Artikel-Existenz-in-Shops?p=286973#post286973 jetzt dynamisch aufgebaut.
Ich bekomme nun ( @sql ist ein nvarchar, der das dynamische SQL enthält ) das Ergebnis als Tabelle zurückgeliefert:
[highlight=sql]execute sp_executesql @sql[/highlight]

Ich möchte dieses Ergebnis nun in einer ERP-Umgebung zur Ansicht veröffentlichen. Dort sind aber nur SELECT-Queries erlaubt.
Gibt es eine Möglichkeit, wie ich auf das Ergebnis des execute per SELECT zugreife?

Danke

[defo]

Du kannst es vielleicht mit einer UDF versuchen, die Table Values zurückliefert
Hier z.B. wie's geht:
https://docs.microsoft.com/en-us/sql...ql-server-2017

Mit dem Verfahren bekommst Du natürlich gern Theater, wenn die Tabelle irgendwo mehr Spalten zurückliefert als "vorgesehen"

[Arne Drews]

Hi, danke für die Info.

Eine Table bekomme ich ja aber schon. Ich kann im ERP kein SELECT auf eine FUNCTION machen. Dort sind nur TABLES und VIEWS erlaubt, woran es vermutlich scheitern wird.

[defo]

Dann habe ich deine Beiträge wohl falsch verstanden.

Ich möchte dieses Ergebnis nun in einer ERP-Umgebung zur Ansicht veröffentlichen. Dort sind aber nur SELECT-Queries erlaubt.
Gibt es eine Möglichkeit, wie ich auf das Ergebnis des execute per SELECT zugreife?

Du zeigst ein Execute, das ist kein Select.

Die UDF Function würde ein "Select * from <udf>[param]" erlauben. Wenn das auch nicht gewünscht ist, dann würde vielleicht eine View Kapselung helfen. Aber wahrscheinlich geht das auch in die Hose.

[Arne Drews]

Ja, das EXECUTE ruft ja nur das dynamische SELECT hinter @sql auf. Das kann ich ja problemlos auch als CREATE VIEW erstellen.
In einem Server-Agent Job unter Berücksichtigung von DROP VIEW IF EXIST reglemässig ausgeführt, sollte ich immer eine aktuelle VIEW haben.

[Arne Drews]

Ja, das EXECUTE ruft ja nur das dynamische SELECT ( siehe Link zu anderem Thread ) hinter @sql auf. Das kann ich ja problemlos auch als CREATE VIEW erstellen.
Habe das mal eben umgebaut, das komplette Script sieht dann so aus:
[HIGHLIGHT=sql]
use VOSSHelper
go


if object_id( 'vArticlesShopsOverview', 'v' ) is not null drop view vArticlesShopsOverview
go


declare @shops nvarchar(max) = ''
select @shops += quotename(replace([description], 'www.', '')) + ',' from Shopsystem..Shops
set @shops = left(@shops, len(@shops)-1)


declare @sql nvarchar(max) = '
create view vArticlesShopsOverview as
select Calculated.*, isnull(bezeichnung.VOSS_WebshopTitel, artikel.Matchcode) Artikel
from (

select Artikel SKU, ' + @shops + '
from (
select
Artikel = artikel.Artikelnummer + '';'' + cast(variante.AuspraegungID as varchar(5)),
Exist = iif( article.ArticleId is not null, 1, 0 ),
Webshop = lower(replace(shop.[Description], ''www.'', ''''))


from Produkte artikel
join Varianten variante on variante.Artikelnummer = artikel.Artikelnummer
full join Shoparticle article on article.Sku = artikel.Artikelnummer collate SQL_Latin1_General_CP1_CI_AS + '';'' + cast(variante.AuspraegungID as varchar(5))
left join Shops shop on shop.ShopId = article.ShopId

) BaseTable


pivot (
max(Exist)
for Webshop in (' + @shops + ')
) PivotTable

) Calculated


join Produkte artikel
on artikel.Artikelnummer = substring(Calculated.SKU, 0, charindex('';'', Calculated.SKU))


left join Produktbezeichnungen bezeichnung
on bezeichnung.Artikelnummer = substring(Calculated.SKU, 0, charindex('';'', Calculated.SKU))
and bezeichnung.AuspraegungID = substring(Calculated.SKU, charindex('';'', Calculated.SKU)+1, 5)
and bezeichnung.Sprache = ''D'''


execute sp_executesql @sql;
[/HIGHLIGHT]
Das lasse ich als Job einmal nachts laufen und habe immer tagesaktuell eine aktualisierte View, auf die ich ein SELECT ausführen kann.
Funktioniert für mich.

[defo]

ok, was mir da noch auffällt:
Das Drop View im Code dürfte auch die Rechte implizit löschen.

Funktionieren kann das also nur, wenn Du einen User verwendest (application owner oder sogar dba), der sowieso alles immer selektieren kann. Das wäre jetzt nicht meine erste Wahl.

Wenn man es filigraner haben will, müsste entweder ein "Create Or Replace View .." (kann msssql bestimmt, könnte aber zu Meckereien führen, je nach Strukturänderung ..) verwendet werden, wo die Selectrechte erhalten blieben
oder
es müsste nach dem Create noch eine separate Grant Operation aufgerufen werden.

[Arne Drews]

ok, was mir da noch auffällt:
Das Drop View im Code dürfte auch die Rechte implizit löschen.

Funktionieren kann das also nur, wenn Du einen User verwendest (application owner oder sogar dba), der sowieso alles immer selektieren kann. Das wäre jetzt nicht meine erste Wahl.

Das stimmt. Die Schnittstelle aus dem ERP nutzt aber an der Stelle für die Datenverbindung einen User, der die entsprechenden Berechtigungen hat.

Wenn man es filigraner haben will, müsste entweder ein "Create Or Replace View .." (kann msssql bestimmt, könnte aber zu Meckereien führen, je nach Strukturänderung ..) verwendet werden, wo die Selectrechte erhalten blieben
oder
es müsste nach dem Create noch eine separate Grant Operation aufgerufen werden.

CREATE OR ALTER VIEW soll wohl gehen, aber erst ab 2016 CU1, wenn ich das richtig gelesen habe. Dort sind wir aber noch nicht. Ende des Jahres bekommen wir ein Upgrade auf die 2016, aber die Ausbaustufe ist mir aktuell nicht bekannt.
Insoweit muss ich das erstmal so lassen.

Danke für Deine Tipps und Hinweise!