CODEWITHSUNDEEP
sqlsql-server
Jean Jacques1998
Jean Jacques1998

Reputation: 55

Can we create a view after a script from a variable?

I would like to create a view at the end of the following request. I know that 'create view' must be the first statement in a query batch. My problem is that for this query i must use a variable (@listOfIDRUB). This variable is only fill correctly at the end of my little script.

I also have tried to create the view before my first declaration but it created a problem with "DECLARE".

So is it possible to create a view easily from the result of my script or i have to do something else ?

DECLARE @CounterResId int;
DECLARE @lePath varchar(255);
DECLARE @listOfIDRUB TABLE (EXTERNALREFERENCE uniqueidentifier, ID varchar(255), DOCID varchar(255) );
DECLARE @Max int;

SET @lePath = '';
SET @CounterResId = 1;
SET @Max = (SELECT COUNT(*) FROM SYNTHETIC..EXTRANET_PURGE WHERE TYPE_SUPPR = 'ResId')

WHILE (@CounterResId <= @Max )
BEGIN;
    set @lePath =
    (select tmp.lePath from
    (
        select row_number() over(order by path)as NumLigne, CONCAT(path, '%' ) as lePath from RUBRIQUE
        WHERE MODELE = 'CAEEE64D-2B00-44EF-AA11-6B72ABD9FE38'
        and CODE in (SELECT ID FROM SYNTHETIC..EXTRANET_PURGE where TYPE_SUPPR='ResId')
    ) tmp
    WHERE tmp.NumLigne = @CounterResId)
    INSERT INTO  @listOfIDRUB(EXTERNALREFERENCE, ID, DOCID)
            SELECT  SEC.EXTERNALREFERENCE , SEC.ID, SEC.DOCUMENTID
            FROM WEBACCESS_FRONT..SECTIONS sec
            inner join rubrique rub ON rub.ID_RUBRIQUE = sec.EXTERNALREFERENCE
            inner join template_tree_item tti   ON tti.id_template_tree_item = rub.modele
            inner join template t               ON t.id_template = tti.template
            WHERE t.CODE IN (SELECT TEMPLATE_CODE from SYNTHETIC..EasyFlowEngineListTemplateCode)
            and rub.path like @lePath
    print @CounterResId;
    print @lePath;
    set @CounterResId = @CounterResId + 1;
END;
    select * from @listOfIDRUB;

Instead of select * from @listOfIDRUB

i wanted create view test as select * from listOfIDRUB I have also tried create view test as (all my request)

Upvotes: 0

Views: 55

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Whenever you ask something about SQL please state your RDBMS (product and version). The answers are highly depending on this...

From your code I assume this is SQL Server.

So to your question: No, a VIEW must be "inlineable" (single-statement or "ad-hoc") statement.

You might think about a multi-statement UDF, but this is in almost all cases a bad thing (bad performance). Only go this way, if your result table will consist of rather few rows!

Without knowing your tables this is rather blind walking, but you might try this (add parameters, if you can transfer external operations (e.g. filtering) into the function):

CREATE FUNCTION dbo.MyFunction()
RETURNS @listOfIDRUB TABLE (EXTERNALREFERENCE uniqueidentifier, ID varchar(255), DOCID varchar(255) )
AS
BEGIN
    DECLARE @CounterResId int;
    DECLARE @lePath varchar(255);
    DECLARE @Max int;

    SET @lePath = '';
    SET @CounterResId = 1;
    SET @Max = (SELECT COUNT(*) FROM SYNTHETIC..EXTRANET_PURGE WHERE TYPE_SUPPR = 'ResId')

    WHILE (@CounterResId <= @Max )
    BEGIN;
        set @lePath =
        (select tmp.lePath from
        (
            select row_number() over(order by path)as NumLigne, CONCAT(path, '%' ) as lePath from RUBRIQUE
            WHERE MODELE = 'CAEEE64D-2B00-44EF-AA11-6B72ABD9FE38'
            and CODE in (SELECT ID FROM SYNTHETIC..EXTRANET_PURGE where TYPE_SUPPR='ResId')
        ) tmp
        WHERE tmp.NumLigne = @CounterResId)
        INSERT INTO  @listOfIDRUB(EXTERNALREFERENCE, ID, DOCID)
                SELECT  SEC.EXTERNALREFERENCE , SEC.ID, SEC.DOCUMENTID
                FROM WEBACCESS_FRONT..SECTIONS sec
                inner join rubrique rub ON rub.ID_RUBRIQUE = sec.EXTERNALREFERENCE
                inner join template_tree_item tti   ON tti.id_template_tree_item = rub.modele
                inner join template t               ON t.id_template = tti.template
                WHERE t.CODE IN (SELECT TEMPLATE_CODE from SYNTHETIC..EasyFlowEngineListTemplateCode)
                and rub.path like @lePath
        --print @CounterResId;
        --print @lePath;
        set @CounterResId = @CounterResId + 1;
    END;

    RETURN;
END

You can call it like this (very similar to a VIEW)

SELECT * FROM dbo.MyFunction();

And you might even use it in joins...

And last but not least I'm quite sure, that one could solve this without declares and a loop too...

Upvotes: 1

Related Questions