Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Thursday, July 17, 2014 4:39 AM
Points: 29, Visits: 253
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: Thursday, July 17, 2014 4:39 AM
Points: 29, Visits: 253
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 1,805, Visits: 5,872
You can't use GO in a dynamic query like this.



MM


  • MMGrid Addin
  • MMNose Addin


  • 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: Today @ 1:11 AM
    Points: 2,840, Visits: 3,976
    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
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse