[thelist] SQL apostrophe disorder
Tab Alleman
Tab.Alleman at MetroGuide.com
Mon Dec 8 11:01:11 CST 2003
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
More information about the thelist
mailing list