calling stored procedure with apostrophe in argume

问题:

I have an app in C# when part of its job is to insert all the data from an Excel file to a database.

The job of getting the data from Excel is alright, and I am using stored procedures to insert the data into the tables in the DB.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_Insert]

@id int,
@brand nvarchar(50),
@model nvarchar(50),
@prodNumber nvarchar(50),
@category nvarchar(50),
@local nvarchar(50)

AS
INSERT INTO Tbl_Registos (ID, Brand, Model, [Product Number], Category, Local)
VALUES (@id, @brand, @model, @prodNumber, @category, @local)

This is one example when the SP works perfectly:

sp_Insert N'1', N'Brand example', N'ABC123', N'123456-7890', 
          N'Laptop', N'18'

The problem comes when I have cases like this:

sp_Insert N'1', N'Brand example', N'ABC123', N'123456-7890',
          N'Laptop', N'Director's floor'

When I have this ' in the middle of the argument. I thought to use quotes [], but in cases where I have integers, I can't put them between quotes.

After I got a row from Excel, I am using a loop to put the arguments in the string that is the query.

回答1:

You can escape the ' in your input by putting another ' in front of it and the insert will work. You can either do this or parse the data for characters like this and disregard them. So your insert statement with this escaped would be:

sp_Insert N'1', N'Brand example', N'ABC123', N'123456-7890', N'Laptop', N'Director''s floor'