[MSSQL 2005] CREATE VIEW uit PROCEDURE

[Jarcol]

Ik heb een procedure waar een SELECT statement in uitgevoerd wordt.
Ik wil graag een view maken over die procedure.

Voorbeeld:

code:

CREATE PROCEDURE proc_ci_view (@product_label NVARCHAR(50)) AS
BEGIN
  @query = 'SELECT * FROM tabel WHERE colomn1 LIKE ' + @product_label

   EXEC @query
END

CREATE VIEW view_pl AS
(
  EXEC proc_ci_view @product_label = 'Router'
)

Als ik bovenstaande uitvoer krijg ik de volgende foutmelding:
Msg 156, Level 15, State 1, Procedure ci_view_router, Line 2
Incorrect syntax near the keyword 'EXEC'.

Weet iemand hoe ik dit kan oplossen? Ik kan deze situatie nergens terugvinden..

[DamadmOO]

Waarom zet je de select query niet direkt in je view en doe je in je SP een select op de view?

[Jarcol]

DamadmOO schreef op dinsdag 20 april 2010 @ 09:12:
Waarom zet je de select query niet direkt in je view en doe je in je SP een select op de view?

Omdat het nogal een dynamisch opgebouwde query is. In de procedure bouw ik de query op, afhankelijk van de variabele. Het voorbeeld is te summier, maar gebruikt om het idee duidelijk te maken.

Als ik in de procedure de query return krijg ik een conversion nvarchar to int failed. Als ik in de procedure de query uitvoer en vervolgens de procedure uitvoer in een create view krijg ik de melding "Incorrect syntax near the keyword 'EXEC'."

[pedorus]

Dit is ook gewoon niet mogelijk denk ik, en ik snap ook niet waarvoor het nuttig zou zijn. Op zo'n dynamische view kun je niet vertrouwen en indexen enzo zijn ook enigszins uitgesloten. Je kan geen SP gebruiken in een view, omgekeerd wel. Waarom niet geen weer een SP?

[Gé Brander]

EXEC in een CREATE VIEW commando is niet mogelijk. Is ook niet logisch. Kan je, als je het echt zo krom wilt doen, niet gewoon een nvarchar variabele maken met de tekst van de logica (SELECT) die de view moet zijn maken en die variabele gebruiken om de opdracht van CREATE VIEW sament te stellen en dan dat commando uitvoeren?

code:

DECLARE @opdracht AS nvarchar(4000)
SET @opdracht = 'CREATE VIEW AS ' + -- hier komt dan de logica die van het select statement
EXEC (@opdracht)

Heel summier natuurlijk, maar je begrijpt het principe.

[Jarcol]

Ik heb het inmiddels opgelost door een function.

De volledige code is:

code:

CREATE FUNCTION proc_ci_view (@product_label NVARCHAR(50)) RETURNS VARCHAR(8000) AS
BEGIN
    DECLARE @column_name varchar(100)
    DECLARE @CI_ID int
    DECLARE @productID int
    DECLARE @query varchar(8000)
    DECLARE @label_type varchar(40)

    SET @label_type = @product_label -- Geef hier het product op, bv. Server, Router of Wan Verbinding. 
    --SET @label_type = 'Router'
    SELECT @productID=it_cmdb_product.cmdb_product_id FROM it_cmdb_product WHERE description LIKE @label_type

    DECLARE tsmain_query CURSOR FOR
        SELECT DISTINCT it_product_label.product_label, it_cmdb_product_attr.cmdb_product_attr_id
        FROM it_cmdb_ci_attr, it_product_label, it_cmdb_product_attr
        WHERE it_cmdb_ci_attr.product_id = @productID
        AND it_cmdb_ci_attr.cmdb_product_attr_id = it_cmdb_product_attr.cmdb_product_attr_id
        AND it_cmdb_product_attr.product_label_id = it_product_label.product_label_id
        ORDER BY it_cmdb_product_attr.cmdb_product_attr_id

    OPEN tsmain_query

    SET @query = 'SELECT it_cmdb_ci.cmdb_ci_id AS "CI ID", it_cmdb_ci.description AS "Label", '
    FETCH NEXT FROM tsmain_query into @column_name, @CI_ID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @query = @query + '(SELECT content FROM it_cmdb_ci_attr WHERE cmdb_ci_id = it_cmdb_ci.cmdb_ci_id AND cmdb_product_attr_id = ' + CONVERT(varchar(20), @CI_ID) + ') AS "' + @column_name + '", '
        FETCH NEXT FROM tsmain_query into @column_name, @CI_ID
    END

    SET @query = 'CREATE VIEW ci_view_' + @product_label + ' AS (' + @query + 'Status FROM it_cmdb_ci WHERE product_id = ' + CONVERT(varchar(20), @productID) + ')'

    DEALLOCATE tsmain_query

    --EXEC(@query)
    return @query
END*/

DECLARE @function_query NVARCHAR(MAX)
SELECT @function_query=dbo.proc_ci_view('Router')
EXECUTE sp_executesql @function_query

Ik kan dus de tekst die uit de function gereturnd wordt execute-en.

[Gé Brander]

Kan je nog iets over de achtergrond vertellen waarom je uiteindelijk voor zo een vage constructie kiest? Misschien zijn er andere logischer oplossingen voor het grotere vraagstuk te vinden.

[jorisdekloris]

Ja inderdaad. De motivatie om voor een dergelijke vage constructie te kiezen is van belang. Misschien weet iemand een betere oplossing.

[Jarcol]

jorisdekloris schreef op dinsdag 20 april 2010 @ 15:57:
Ja inderdaad. De motivatie om voor een dergelijke vage constructie te kiezen is van belang. Misschien weet iemand een betere oplossing.

dus...

[Jarcol]

c70070540 schreef op dinsdag 20 april 2010 @ 15:52:
Kan je nog iets over de achtergrond vertellen waarom je uiteindelijk voor zo een vage constructie kiest? Misschien zijn er andere logischer oplossingen voor het grotere vraagstuk te vinden.

Ik heb een CMDB. Ik wil uit die CMDB per type CI alle CI's halen met hun attributen. Die zitten door het pakket in 20000 verschillende tabellen van een database opgeslagen.

Ik wil dus een generiek script hebben dat voldoende heeft aan het type CI en op basis daarvan een view creeert die alle CI's van een bepaald type met hun attributen weergeeft.

[Ciqniz]

Als het niet realtime hoeft had je ook een tussentabel kunnen maken die je 1x per dag vult met de data uit verschillende bronnen bijvoorbeeld. Die tabel raadpleeg je dan elke keer. Soort van ETL idee eigenlijk...?

[Jarcol]

Ciqniz schreef op woensdag 21 april 2010 @ 00:11:
Als het niet realtime hoeft had je ook een tussentabel kunnen maken die je 1x per dag vult met de data uit verschillende bronnen bijvoorbeeld. Die tabel raadpleeg je dan elke keer. Soort van ETL idee eigenlijk...?

Ik snap wat je bedoelt, maar het moet wel real-time zijn.

[Verwijderd]

je kunt natuurlijk gebruik maken van een trigger om het real-time te maken.

[Gé Brander]

En waarom is dan toch die view nodig? Je kan toch ook gewoon de benodigde query aftrappen? Als het realtime moet, zie ik de toegevoegde waarde van de view niet.

[Jarcol]

c70070540 schreef op woensdag 21 april 2010 @ 12:02:
En waarom is dan toch die view nodig? Je kan toch ook gewoon de benodigde query aftrappen? Als het realtime moet, zie ik de toegevoegde waarde van de view niet.

Omdat ik de gegevens niet wil opvragen vanuit MSSQL, maar vanuit Excel. Ik maak daar dus een gegevensverbinding en importeer zo de hele zooi. En dan werkt dat het mooist vanuit een View.

[Gé Brander]

Jarcol schreef op woensdag 21 april 2010 @ 14:48:
[...]


Omdat ik de gegevens niet wil opvragen vanuit MSSQL, maar vanuit Excel. Ik maak daar dus een gegevensverbinding en importeer zo de hele zooi. En dan werkt dat het mooist vanuit een View.

Ja voor de gebruiker kan ik me dat voorstellen, maar ik zal eens mijn gedachten laten gaan hoe dat ook anders kan...