Incorrect Syntax using quotes in stored procedures

Question

I want to make this SQL query into a stored procedure. I have this code I want to use to fetch values for currency. It does not work as expected.

The code looks something like this :

CREATE PROCEDURE ShowVaultBalances
    @SOL_ID varchar(50) = NULL,
    @acct_crncy_code varchar(50) = NULL
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM OPENQUERY(LinkedServer_Name,
                   'select foracid, acct_name, acct_crncy_code, clr_bal_amt from table_name where bacid = ''1010000001'' and acct_crncy_code = ' + @acct_crncy_code + ' and sol_id = ' + @SOL_ID + '')
END

And get this error :

Msg 102, Level 15, State 1, Procedure ShowVaultBalances, Line 8
Incorrect syntax near '+'.

Did I miss something?

Are you using MS SQL Server or Oracle? The same code will not run on both. — jarlh, Jul 16 '20 at 11:23
SQL server , its a Linked Server. which is Linked from Oracle to SQL server — Husseiny, Jul 16 '20 at 11:24
Does this answer your question? [including parameters in OPENQUERY](https://stackoverflow.com/questions/3378496/including-parameters-in-openquery) — Preben Huybrechts, Jul 16 '20 at 16:28

Preben Huybrechts · Answer 1 · 2020-07-16T12:25:34.537

1

OPENQUERY doesn't support string concatenation. You have to use Dynamic SQL for the full statement.

Try something like this:

CREATE PROCEDURE ShowVaultBalances
    @SOL_ID varchar(50) = NULL,
    @acct_crncy_code varchar(50) = NULL
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'
    SELECT *
    FROM OPENQUERY(LinkedServer_Name,
                   ''select foracid
                   , acct_name
                   , acct_crncy_code
                   , clr_bal_amt 
                   from table_name 
                   where bacid = ''''1010000001'''' 
                   and acct_crncy_code = ''''' + QUOTENAME(@acct_crncy_code,'''') + ''''' 
                   and sol_id = ''''' + QUOTENAME(@SOL_ID,'''') + ''''''')'
    
    EXEC (@SQL)
END

edited Jul 16 '20 at 12:25

answered Jul 16 '20 at 11:50

Preben Huybrechts

5,853
2
27
63

Err you did not add this SOL_ID and acct_crncy_code, wont i be needing those too? – Husseiny Jul 16 '20 at 11:55
This is, however, wide open to injection. – Thom A Jul 16 '20 at 11:58
@Larnu I Agree, SQL string concattenation should be avoided at all times, but sadly we don't have a parameter option for `OPENQUERY` and calling the linked server without open query can sometimes result in very bad performance. I'm open for other solutions – Preben Huybrechts Jul 16 '20 at 12:02
1

Which is why `QUOTENAME`/`REPLACE` is important when injecting, @PrebenHuybrechts . – Thom A Jul 16 '20 at 12:03
Added the `QUOTENAME` My bad, thought they where in OP's question – Preben Huybrechts Jul 16 '20 at 12:06
complains seriously, says Msg 102, Level 15, State 1, Line 27 Incorrect syntax near 'NGN'. Msg 105, Level 15, State 1, Line 27 Unclosed quotation mark after the character string ')'. – Husseiny Jul 16 '20 at 12:18
Updated, i've missed some escape quotes – Preben Huybrechts Jul 16 '20 at 12:25
This is correct, but here is a more complete answer: https://stackoverflow.com/questions/3378496/including-parameters-in-openquery – Nick Fotopoulos Jul 16 '20 at 14:19

Incorrect Syntax using quotes in stored procedures

1 Answers1