CODEWITHSUNDEEP
rstored-proceduressql-server-2016quote
mzhasan
mzhasan

Reputation: 55

Pass string with quotes as input parameter to a SQL Server 2016 stored procedure using R Service

I am using SQL Server 2016 with R service. I am trying to create a stored procedure which I want to execute inserting text as parameter value.

e.g. exec test N'Hello World'

Generally it works fine but if input text which contain quotes it shows error.

Here my sample stored procedure:

CREATE PROCEDURE [dbo].[test] 
    (@param1 NVARCHAR(MAX)) 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql VARCHAR(MAX)
    SET @sql = 'select = ''' + @param1 + ''';'

    PRINT(@sql)
    EXEC sp_execute_external_script 
               @language = N'R', 
               @script = N'data<-mynumbers 
                            OutputDataSet<-data.frame(data)',
               @params = N' @mynumbers nvarchar (max)',
               @mynumbers = @sql
 END

If I execute the procedure inserting text as parameter value which contain single or multiple quotes (e.g. don't , can't, Tom's), it shows an error:

exec test N' Don't do late making Tom's work '

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ot'.

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

Upvotes: 2

Views: 10351

Answers (1)

Niels Berglund
Niels Berglund

Reputation: 1698

The problem is that single quote is a string identifier in SQL, e.g. single quotes "delimits" strings. If you want single quotes inside a string you need to "double" them. Your example would be:

exec test N' Don''t do late making Tom''s work '

Hope this helps.

Niels

Upvotes: 4

Related Questions