0

I'm working on procedure, which uses OPENROWSET. I need to pass 1 varchar parameter into it. That's how I've decided to do that:

ALTER PROCEDURE [dbo].[GetId]
@designatio varchar
AS
BEGIN
DECLARE @SQLStr varchar(max)=
    'select * FROM OPENROWSET(''SQLOLEDB'',''SQLSERVER'';''sss'';''sss''
    ,''select *
        from db.dbo.table)
        where DESIGNATIO = ''' + @designatio + ')'

EXECUTE(@SQLStr)
END

Everything is OK, but I get error when execute it:

exec GetId 'П-11792.00'

Incorrect syntax near 'П'

I've tried to cast passing parameter hard, so the error was the same, but

Incorrect syntax near 'П-11792.00'

Please, tell me, where am I wrong.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Харламов Даниил
  • 163
  • 1
  • 10
  • The procedure declaration is `GetId` yet the example you give is `GetSearchId`, is this a typo? – Filipe Nóbrega May 25 '21 at 09:38
  • @FilipeNóbrega Yes, sorry for that) – Харламов Даниил May 25 '21 at 09:41
  • If you replace `varchar` with `nvarchar` and define some size for parameter (like `nvarchar(100)`), then replace all strings with unicode ones (`''` -> `N''`) does something improve? And don't forget to sanitize input string - at least replace apostrophes with double ones. – Arvo May 25 '21 at 09:57
  • As I've understood you right, you would write this: DECLARE @SQLStr varchar(max)= 'select * FROM OPENROWSET(N''SQLOLEDBN'',N''SQLSERVERN'';N''saN'';N''wmprnxugnspvfrgpagejucN'' ,N''select * from Search.dbo.DOCLIST with(nolock)) where DESIGNATIO = N''' + designatio + ')' , and replace varchar with nvarachar. But that doesn't help – Харламов Даниил May 25 '21 at 10:04
  • 1
    You don't have enough quotes. Remember, the query in `OPENROWSET` is itself quoted and you're constructing it dynamically, so you need four quotes to get two quotes to end up with one quote in the final query. (And you're missing the closing quotes.) Always try to build the query up in layers, bottom-up, replacing quotes systematically as you go up a level. That way you don't accidentally miss one. `PRINT`ing what you construct is also always a good idea. – Jeroen Mostert May 25 '21 at 10:08
  • 2
    Also, where possible, see if `EXECUTE .. AT` with a linked server is an option, because unlike `OPENROWSET`, it does support parameters (`EXECUTE ('SELECT * FROM db.dbo.table WHERE DESIGNATIO = ?', @designatio) AT LinkedServerName`), saving a whole bunch of quoting hassles. – Jeroen Mostert May 25 '21 at 10:13
  • 1
    `@designatio varchar` That is a single character parameter - a common mistake. ALWAYS define the length of any variable length datatype. – SMor May 25 '21 at 10:43
  • Thank you, all, I see, that the problem might be in quotes amount, but I still cannot find, where exactly. – Харламов Даниил May 25 '21 at 11:25
  • The first thing you should do is print your SQL and inspect it. Don't try and debug it without actually looking at it – Nick.Mc May 25 '21 at 12:19

1 Answers1

0
ALTER PROCEDURE [dbo].[GetId]
@designatio varchar
AS
BEGIN
DECLARE @SQLStr varchar(max)=
    'select * FROM OPENROWSET(''SQLOLEDB'',''SQLSERVER'';''sss'';''sss''
    ,''select *
        from db.dbo.table)
        where DESIGNATIO = ''''' + @designatio + ''''' '')'

EXECUTE(@SQLStr)
END

The problem was in understanding, that string value in query must have own quotes, so if it is already in double quotes, it must be in 4 quotes + own quotes.

Харламов Даниил
  • 163
  • 1
  • 10
  • 1
    Note that this will still (silently) fail if you pass a `@designation` of more than a single character, be sure to correct that. It's a [bad habit to kick](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). – Jeroen Mostert May 25 '21 at 12:16
  • Oh, thank you, I see that. I've posted the procedure in such view, because there have been a lot of tries to make it right before I've decided to ask. – Харламов Даниил May 25 '21 at 12:26