SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to create a view dynamically thru a procedure


How to create a view dynamically thru a procedure

Author
Message
pvsrpk
pvsrpk
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 269
Hi Friends,

I need to create a view in another database dynamically from a procedure in "MAIN_DATABASE".

The following is my piece of code:


DECLARE @sql varchar(max)
SET @sql='
USE '+@Database1+'
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''test_view'')
BEGIN
DROP VIEW test_view
PRINT ''VIEW EXISTS''
END'
PRINT @sql
EXEC(@sql)

SET @sql=''
SET @sql='USE '+@Database1+'
GO
CREATE VIEW test_view
SELECT TOP 50 * FROM TEST_TABLE
'
PRINT @sql
EXEC(@SQL)

I am getting error "[color=#FF0000]Incorrect Syntax near GO[/color]"

Any help appreciated.

Thanks in advance
pvsrpk
pvsrpk
pvsrpk
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 269
Hi this is the correct piece of code. I missed AS BEGIN in view definition.

DECLARE @sql varchar(max)
SET @sql='
USE '+@Database1+'
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''test_view'')
BEGIN
DROP VIEW test_view
PRINT ''VIEW EXISTS''
END'
PRINT @sql
EXEC(@sql)

SET @sql=''
SET @sql='USE '+@Database1+'
GO
CREATE VIEW test_view
AS
BEGIN
SELECT TOP 50 * FROM TEST_TABLE
'
PRINT @sql
EXEC(@SQL)

I am getting error "Incorrect Syntax near GO"
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10637 Visits: 7891
You can't use GO in a dynamic query like this.

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Bhuvnesh
    Bhuvnesh
    SSChampion
    SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

    Group: General Forum Members
    Points: 13330 Visits: 4077
    DECLARE @sql varchar(max), @Database1 varchar(300)
    set @Database1 = 'tempdb'
    SET @sql='
    USE '+@Database1+';
    IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''test_view'')
    BEGIN
    DROP VIEW test_view
    PRINT ''VIEW EXISTS''
    END'
    PRINT @sql
    --EXEC(@sql)

    SET @sql=''
    SET @sql='USE '+@Database1+'
    ;
    CREATE VIEW test_view
    AS
    BEGIN
    SELECT TOP 50 * FROM TEST_TABLE
    '
    PRINT @sql
    --EXEC(@SQL)




    This code wiil work for you.
    Now home work for you, find what i have changed in you code :-P

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
    wschampheleer
    wschampheleer
    SSCommitted
    SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

    Group: General Forum Members
    Points: 1694 Visits: 303
    This is how I do this:

    DECLARE @sp_executesql NVARCHAR(150) = QUOTENAME('name of your database goes here') + '.sys.sp_executesql' ;



    I put the statement to create the view in @sql of type NVARCHAR(MAX).

    To execute this statement on the 'other' database, run

    EXEC @sp_executesql @sql ;



    Regards,

    Willem
    http://wschampheleer.wordpress.com
    immanuel2112
    immanuel2112
    SSC Rookie
    SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

    Group: General Forum Members
    Points: 48 Visits: 2
    Hi,
    I tried to using the suggestion we have in this thread. But when executing it i am getting the 2 errors,
    1. Msg 156, Level 15, State 1, Line 4 - Incorrect syntax near the keyword 'VIEW'.
    2. Msg 102, Level 15, State 1, Line 21 - Incorrect syntax near 'END'.

    Below is the query, Please advise.

    declare @query nvarchar(max);
    SET @query='USE <DatabaseName>;
    IF NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''PrimaryKeyDetails'')
    BEGIN
    CREATE VIEW PrimaryKeyDetails AS
    BEGIN
    SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)), CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)),
    PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)), PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE, 
    REFERENCE_TABLE_NAME='', REFERENCE_COL_NAME=''
    FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
    ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
    ON PKnUColIdx.object_id = PKnUTable.object_id
    AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
    ON PKnUKEYCol.object_id = PKnUTable.object_id
    AND PKnUKEYCol.column_id = PKnUColIdx.column_id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
    ON oParentColDtl.TABLE_NAME=PKnUTable.name
    AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
    END
    ';

    print @query;

    EXECUTE sp_executesql @query;

    Cheers,
    Immi
    Sean Lange
    Sean Lange
    SSC Guru
    SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

    Group: General Forum Members
    Points: 62833 Visits: 17959
    immanuel2112 (10/7/2015)
    Hi,
    I tried to using the suggestion we have in this thread. But when executing it i am getting the 2 errors,
    1. Msg 156, Level 15, State 1, Line 4 - Incorrect syntax near the keyword 'VIEW'.
    2. Msg 102, Level 15, State 1, Line 21 - Incorrect syntax near 'END'.

    Below is the query, Please advise.

    declare @query nvarchar(max);
    SET @query='USE <DatabaseName>;
    IF NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''PrimaryKeyDetails'')
    BEGIN
    CREATE VIEW PrimaryKeyDetails AS
    BEGIN
    SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)), CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)),
    PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)), PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE, 
    REFERENCE_TABLE_NAME='', REFERENCE_COL_NAME=''
    FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
    ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
    ON PKnUColIdx.object_id = PKnUTable.object_id
    AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
    ON PKnUKEYCol.object_id = PKnUTable.object_id
    AND PKnUKEYCol.column_id = PKnUColIdx.column_id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
    ON oParentColDtl.TABLE_NAME=PKnUTable.name
    AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
    END
    ';

    print @query;

    EXECUTE sp_executesql @query;

    Cheers,
    Immi


    You have this great debugging line right there in your code. It looks like this: print @query;

    Can you share those results with us?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
    Understanding and Using APPLY (Part 1)
    Understanding and Using APPLY (Part 2)
    immanuel2112
    immanuel2112
    SSC Rookie
    SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

    Group: General Forum Members
    Points: 48 Visits: 2
    Hi Sean,

    yes you are right, the debug line print @query was the was of the 2nd issue i reported. On removing it, thats no longer an issue. But the 1st issue is still persisting. Please find attached the screenshot of the error for the modified code.

    declare @query nvarchar(max)
    SET @query='USE <DatabaseName>;
    IF NOT EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME =''PrimaryKeyDetails'')
    BEGIN
    CREATE VIEW PrimaryKeyDetails AS
    BEGIN
    SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)), CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)),
    PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)), PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE, 
    REFERENCE_TABLE_NAME='', REFERENCE_COL_NAME=''
    FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
    ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
    ON PKnUColIdx.object_id = PKnUTable.object_id
    AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
    ON PKnUKEYCol.object_id = PKnUTable.object_id
    AND PKnUKEYCol.column_id = PKnUColIdx.column_id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
    ON oParentColDtl.TABLE_NAME=PKnUTable.name
    AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
    END
    ';

    EXECUTE sp_executesql @query;

    Cheers,
    Immi
    Attachments
    Screenshot.JPG (41 views, 103.00 KB)
    Luis Cazares
    Luis Cazares
    SSC-Forever
    SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

    Group: General Forum Members
    Points: 42107 Visits: 19829
    Why are you using a BEGIN with your view? Views are a single statement and they can't include a BEGIN...END block.


    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


    How to post data/code on a forum to get the best help: Option 1 / Option 2
    Luis Cazares
    Luis Cazares
    SSC-Forever
    SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

    Group: General Forum Members
    Points: 42107 Visits: 19829
    And by the way, the CREATE VIEW must be the first statement in a query batch. The IF and any other things that you might want to include must be in a separate batch.
    As dynamic code doesn't allow GO as a batch separator, you need to either nest your dynamic code or keep it as static SQL.


    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


    How to post data/code on a forum to get the best help: Option 1 / Option 2
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search