[thelist] SQL apostrophe disorder

Joel D Canfield joel at spinhead.com
Sat Dec 6 20:17:03 CST 2003


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?

I'm including the table structure and the stored procedure as well.

Thanks.

spinhead

------------------------------
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


More information about the thelist mailing list