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: Yesterday @ 4:54 PM
Points: 1,974, Visits: 6,667
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: Today @ 12:35 AM
    Points: 2,849, Visits: 4,036
    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