CODEWITHSUNDEEP
sqlsql-servert-sql
ghoston3rd
ghoston3rd

Reputation: 149

Error when using single quote with sp_executesql

I have the following SQL statement so that I can script out view creation using if not exists with sp_executesql but the statement is giving me errors due to the single quotes around 1 in my last where statement. Is there any way around this? 

    IF NOT EXISTS (SELECT * FROM SYS.objects WHERE NAME = 'vw_JeopardyAlertDetails' AND    TYPE = 'V')
    EXEC sp_executesql @statement = N'CREATE VIEW [dbo].[vw_JeopardyAlertDetails]
AS 
    SELECT Main.TicketNumber, TS.TicketStateDesc, Main.ApptEnd, ISNULL(C.FirstName, '') AS FirstName, ISNULL(C.LastName, '') AS LastName, ISNULL(Main.CustomerID, '') 
                   AS CustomerID, Main.ApptID, Main.ID, Main.TicketType
    FROM  (SELECT s.TicketState, s.TicketID AS ID, s.ApptEnd, dbo.Ticket.TicketNumber, dbo.Ticket.TimeOpened, dbo.Ticket.CreatedBy, dbo.Ticket.ReportedBy, 
                                  dbo.Ticket.ModifiedBy, dbo.Ticket.ChangedBy, dbo.Ticket.Priority, dbo.Ticket.ServingArea, dbo.Ticket.StructureLink, dbo.Ticket.CustomerID, 
                                  dbo.Ticket.TicketCategory, dbo.Ticket.TicketCode, s.ApptStart, s.TicketType, s.CanReschedule, ISNULL(s.ID, 0) AS ApptID
                   FROM   dbo.Schedule AS s INNER JOIN
                                  dbo.Ticket ON s.TicketID = dbo.Ticket.ID
                   WHERE (s.TicketState IN
                                      (SELECT DISTINCT TicketState
                                       FROM   dbo.AlertJeopardyTicketState
                                       WHERE (IsJeopardyState = '1'))) AND (s.ApptEnd >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111) + ' ' + dbo.GetJeopardyStartTime())) 
                                  AND (s.ApptEnd <= GETDATE())) AS Main LEFT OUTER JOIN
                   dbo.Customer AS C ON Main.CustomerID = C.ID LEFT OUTER JOIN
                   dbo.TicketStatus AS TS ON Main.TicketState = TS.ID
    GO' ;
    ELSE PRINT N'vw_JeopardyAlertDetails ALREADY EXISTS'
GO

Upvotes: 3

Views: 4319

Answers (1)

StuartLC
StuartLC

Reputation: 107267

You'll need to double up those quotes in your Sql @statement, e.g.

ISNULL(C.FirstName, '')

needs to be

ISNULL(C.FirstName, '''')

Simplified fiddle here

Upvotes: 8

Related Questions