Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    11

    Unanswered: SQL Server: How do I ignore errors on a view creation?

    I have a sql script which creates a view. This view references a table that does not exist when the view is created. So, it throws an error. I am wondering how I can catch the error and allow the view to be created anyway with no error thrown (important). I have tried wrapping it in a try catch, but it seems that is not allowed. I have tried using an if (@@error <> 0) statement, but it doesn't stop the error from being thrown.

    I just want to prevent the error from being returned at all. I have also found that Oracle has exactly what I want (CREATE FORCE VIEW), but that is not available in SQL Server.

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Why are you creating a view on a table that does not exist?

    The easy way to do it is temporarily create the table before you create the view then drop it afterwards.

    Code:
    -- If The table doesn't exists, create it.
    If Not Exists (Select 1 From Information_Schema.Tables Where Table_Name = 'TestTable')
    Begin
    	Create Table TestTable (Col1 int primary key)
    End
    Go
    -- If the view already exists, drop it first.
    If Exists (Select 1 From Information_Schema.Views Where Table_Name = 'TestView')
    Begin
    	Drop View TestView
    End
    Go
    -- Create the view.
    Create View TestView
    As
    	Select Col1 From TestTable
    Go
    --Drop the table
    Drop Table TestTable
    Go

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by EngadaSQL View Post
    Why are you creating a view on a table that does not exist?
    Seconded. With intensity.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2009
    Posts
    11
    The table is created later in the product's install process. It is not even in the same script and cannot be.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A stored procedure can reference an non-existent table, but not a view or function.

    You must create the table first.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would suggest you either create the dependent objects AFTER the base objects (radical thought, I know), or create the view as a stub with no table references, and then ALTER the view at the end of the install.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2009
    Posts
    11
    The latter is the route I have been forced to go.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •