CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Akshay Deshpande
Akshay Deshpande

Reputation: 73

SQL Schema Creation Failed

I am getting error while executing below statement from my db script.

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROJ_GetQuestions]') AND type in (N'P', N'PC'))
BEGIN
EXEC('
CREATE PROCEDURE [dbo].[PROJ_GetQuestions]
              (
                    @TestID INTEGER   
              )
              AS            
            SELECT  b.* FROM [IER_TestsCatalogue] a 
            INNER JOIN [IER_QuestionsCatalogue] b 
            ON ',' + a.Selected_CategoryIds + ',' LIKE '%,' + CAST(Category_Id AS VARCHAR(10)) + ',%' 
            WHERE   a.Test_Id = @TestID Order by NEWID()            
      RETURN
')
END

Error after executing above statement.

Schema Creation Failed: Incorrect syntax near '%'.

Upvotes: 1

Views: 227

Answers (1)

Andomar
Andomar

Reputation: 238246

You can do that with a much simpler exec using alter procedure, like:

if not exists (select * from sys.procedures where name = 'PROJ_GetQuestions')
    exec ('create procedure dbo.PROJ_GetQuestions as select 1')
go
alter procedure dbo.PROJ_GetQuestions(
    @TestID int)
as
    ... body of stored procedure ...
go

If you have to write it using create procedure, here's a version with correct quote escaping:

EXEC('
CREATE PROCEDURE [dbo].[PROJ_GetQuestions]
              (
                    @TestID INTEGER   
              )
              AS            
            SELECT  b.* FROM [IER_TestsCatalogue] a 
            INNER JOIN [IER_QuestionsCatalogue] b 
            ON '','' + a.Selected_CategoryIds + '','' LIKE ''%,'' +
                CAST(Category_Id AS VARCHAR(10)) + '',%'' 
            WHERE   a.Test_Id = @TestID Order by NEWID()            
      RETURN
')

Upvotes: 1

Related Questions