[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