[thelist] SQL apostrophe disorder

[Tab Alleman]

Joel D Canfield wrote:
> I'm accessing an app's stored procedure to write our HelpDesk requests
> direct to the MSSQL db. I just can't seem to get the apostrophical fix
> right.
> 
> It fails with an error about 2 not being a valid something or other;
> obviously, it's not working with the double apostrophes. Doesn't work
> with single or triple, either.
> 
> Am I missing an obvious escape mechanism for single quotes? Are these
> quoted right?
> 

My WAG would be that you should replace:

'''apostrophe between one and two 1''2'',

With 

''apostrophe between one and two 1'''2''


> 
> ------------------------------
> Data I'm sending:
> 
> DECLARE @RC int;
> DECLARE @Columns varchar(4000);
> DECLARE at WorkOrderNumber int;
> EXEC @RC = spAddTask '(WO_NUM, PARENTWOID, WOID, TASK, TYPE, REQUEST,
> REQDATE, OPENBY, OPENDATE, MODIBY, MODIDATE, CLSDBY, CLSDDATE,
> ELAPSETIME, ELAPSEMIN, PRIORITY, DUEDATE, RESPONS, ASSNDATE,
> COMPLETED, HOURS, RATE, CHARGE, WS_NUM, DEPT_NUM, DEPT, PHONE,
> PHONE_EXT, LOCATION, DESCRIPT, WO_TEXT1, WO_TEXT2, WO_TEXT3,
> WO_TEXT4, WO_TEXT5, WO_TEXT6, WO_DATE1, WO_DATE2, WO_NUM1, WO_INT1,
> NOTE, FT, COMPFLAG, STATUS, AGENTDATE, AGENTLEVEL, AWS_NUM, LOOKUP1,
> LOOKUP2, GUIDO, EMAILADDR, USERID, WOTYPE2, WOTYPE3)', '''apostrophe
> between one and two 1''2'', ''Non-PX2'', ''Canfield, Joel'',
> ''12/2/03 1:12:01 PM'', ''web'', ''12/2/03 1:12:01 PM'', ''web'',
> ''12/2/03 1:12:01 PM'', NULL, NULL, NULL, NULL, ''99 - FYI'',
> ''3/10/04 1:12:01 PM'', ''HelpDesk'', ''12/2/03 1:12:01 PM'', NULL,
> NULL, ''0'', ''0'', NULL, NULL, NULL, NULL, NULL, NULL,
> ''Repeatability: AlwaysDescription: apostrophe between three and four
> 3''4'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
> NULL, ''0'', NULL, ''Pending'', NULL, NULL, NULL, NULL, NULL, NULL,
> NULL, NULL, ''Computer'', ''''', @WorkOrderNumber OUTPUT 
> 
>
------------------------------------------------------------------------
> ----------------------
> 
> CREATE PROCEDURE [dbo].[spAddTask]
> /*********************************************************************
> ** PURPOSE:
> **    This stored procedure is used to get and increment the work
> **    order number and add a task.
> **
> ** TABLES/VIEWS USED:
> **    TASKS
> ** PARAMETERS:
> **    @Columns = Contains the column list
> **    @Values = Contains the column values
> **    @WorkOrderNumber = Used to return the work order number
> ** RETURNS:
> **    None
> ** EXAMPLE:
> **    EXEC spAddTask <columnlist>,<valuelist>,<WorkOrderNumber>
> ** VERSION   REVISION DATE   AUTHOR            COMMENTS
> **   1.0     01/10/2002      Frank Picardi     Created
>
**********************************************************************/
> @Columns varchar(4000)
> , at Values text
> , at WorkOrderNumber int = NULL OUTPUT
> AS
> 
> DECLARE
>   @WO2 varchar(16),
>   @WOID int
> SET NOCOUNT ON
> -- Get the next work order number and update counter
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> UPDATE LASTHD SET LAST = LAST + 1
> SELECT @WorkOrderNumber = LAST
> FROM LASTHD
> UPDATE LASTWOID SET LASTWOID = LASTWOID + 1
> SELECT @WOID = LASTWOID FROM LASTWOID
> 
> SELECT @WO2 = convert(varchar(15), @WorkOrderNumber) + ',' +
> convert(varchar(15), @WOID) + ','
> + convert(varchar(15), @WOID) + ','
> EXEC ('INSERT  INTO TASKS ' + @Columns
>      + ' VALUES (' + @WO2 + @Values+ ')' )
> 
> --SELECT @@ERROR
> COMMIT TRANSACTION
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> SET NOCOUNT OFF
> 
> GO
> 
>
------------------------------------------------------------------------
> ----------------------
> 
> CREATE TABLE [dbo].[TASKS] (
> 	[WO_NUM] [int] NOT NULL,
> 	[TASK] [varchar] (50),
> 	[TYPE] [varchar] (30),
> 	[REQUEST] [varchar] (30),
> 	[REQDATE] [datetime] NULL,
> 	[OPENBY] [varchar] (255),
> 	[OPENDATE] [datetime] NULL,
> 	[MODIBY] [varchar] (255),
> 	[MODIDATE] [datetime] NULL,
> 	[CLSDBY] [varchar] (15),
> 	[CLSDDATE] [datetime] NULL,
> 	[ELAPSETIME] [varchar] (20),
> 	[ELAPSEMIN] [int] NULL,
> 	[PRIORITY] [varchar] (30),
> 	[DUEDATE] [datetime] NULL,
> 	[RESPONS] [varchar] (30),
> 	[ASSNDATE] [datetime] NULL,
> 	[COMPLETED] [datetime] NULL,
> 	[HOURS] [float] NULL,
> 	[RATE] [float] NULL,
> 	[CHARGE] [float] NULL,
> 	[WS_NUM] [int] NULL,
> 	[DEPT_NUM] [varchar] (30),
> 	[DEPT] [varchar] (30),
> 	[PHONE] [varchar] (30),
> 	[PHONE_EXT] [varchar] (8),
> 	[LOCATION] [varchar] (30),
> 	[DESCRIPT] [text],
> 	[WO_TEXT1] [varchar] (40),
> 	[WO_TEXT2] [varchar] (40),
> 	[WO_TEXT3] [varchar] (40),
> 	[WO_TEXT4] [varchar] (40),
> 	[WO_TEXT5] [varchar] (40),
> 	[WO_TEXT6] [varchar] (40),
> 	[WO_DATE1] [datetime] NULL,
> 	[WO_DATE2] [datetime] NULL,
> 	[WO_NUM1] [float] NULL,
> 	[WO_INT1] [int] NULL,
> 	[NOTE] [text],
> 	[FT] [bit] NULL,
> 	[COMPFLAG] [varchar] (30),
> 	[STATUS] [varchar] (25),
> 	[AGENTDATE] [datetime] NULL,
> 	[AGENTLEVEL] [tinyint] NULL,
> 	[AWS_NUM] [varchar] (40),
> 	[LOOKUP1] [varchar] (30),
> 	[LOOKUP2] [varchar] (30),
> 	[GUIDO] [varchar] (40),
> 	[EMAILADDR] [varchar] (100),
> 	[USERID] [int] NULL,
> 	[PARENTWOID] [int] NULL,
> 	[WOID] [int] NULL,
> 	[WOTYPE2] [varchar] (30),
> 	[WOTYPE3] [varchar] (30)
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO