Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create a view dynamically thru a procedure Expand / Collapse
Author
Message
Posted Thursday, February 11, 2010 12:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 10, 2015 6:43 AM
Points: 30, 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
Post #864259
Posted Thursday, February 11, 2010 12:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 10, 2015 6:43 AM
Points: 30, 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"
Post #864262
Posted Thursday, February 11, 2010 4:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:23 AM
Points: 2,194, Visits: 7,781
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
  • Post #864408
    Posted Friday, February 12, 2010 6:21 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Tuesday, July 19, 2016 4:34 AM
    Points: 2,850, Visits: 4,076
    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


    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done
    Post #864571
    Posted Friday, February 12, 2010 6:29 AM
    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Tuesday, February 19, 2013 2:02 AM
    Points: 846, 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
    Post #864577
    Posted Wednesday, October 7, 2015 2:16 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, October 8, 2015 9:22 AM
    Points: 2, 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
    Post #1726340
    Posted Wednesday, October 7, 2015 3:35 PM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Tuesday, December 6, 2016 8:08 PM
    Points: 16,145, Visits: 16,850
    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 Moden's 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)
    Post #1726375
    Posted Thursday, October 8, 2015 4:43 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, October 8, 2015 9:22 AM
    Points: 2, 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


      Post Attachments 
    Screenshot.JPG (5 views, 103.37 KB)
    Post #1726492
    Posted Thursday, October 8, 2015 9:09 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Yesterday @ 1:48 PM
    Points: 8,233, Visits: 17,809
    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
    Post #1726573
    Posted Thursday, October 8, 2015 9:12 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Yesterday @ 1:48 PM
    Points: 8,233, Visits: 17,809
    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
    Post #1726575
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse