none
Create View using sp_executesql

    Question

  • Hello
    I am trying the following within a stored procedure. The complete code is listed below



    /****** Object:  StoredProcedure [dbo].[my_proc]    Script Date: 07/13/2008 22:00:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER proc [dbo].[my_proc]
            @id int
    as
    BEGIN

    declare @sql nvarchar(max)
    declare @blah nvarchar(max)
    drop view my_sysobjects
    select @sql = N'create view my_sysobjects as select  * from sysobjects where id =@my_id'

    select @blah =N'@my_id int'

    exec sp_executesql @sql,@blah,@id


    select * from my_sysobjects

    END

    When I execute the procedure, I get the following message

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'view'.

    Does anyone know what is wrong and why I am getting this message?
    Thanks




    Monday, July 14, 2008 3:31 AM

Answers

  • First of all view can not have parameters.  You have to change the logic. You may try UDF (also called parameterised view). Check BOL (books online)

     

    the following code will run

     

    create proc [dbo].[my_proc]

    @id int

    as

    BEGIN

    declare @sql nvarchar(max)

    declare @blah nvarchar(max)

    drop view my_sysobjects

    select @sql = N'create view my_sysobjects as select * from sysobjects'-- where id =@my_id'

    select @blah =N'@my_id int'

    exec sp_executesql @sql --,@blah,@id

     

    select * from my_sysobjects

    END

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

    Monday, July 14, 2008 5:11 AM
    Moderator