-1

Please help me to find the mistakes. I think it is due to apostrophe but I dont know how to correct.

My code returned error :

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'or'.

Use YEAR_TO_DATE;

Declare @policy NVARCHAR(MAX);
Declare @account_list NVARCHAR(MAX);
Declare @sql NVARCHAR(MAX);
Set @policy ='TTY%';
Set @account_list='18000,19000';

Set @sql = 
'Select
case
when [Debit_Account] in ('+@account_list+') and ([policy1] like '+@policy+' or [policy2] like '+@policy+' or [policy3] like '+@policy+') then -[Amount]
when [Credit_Account] in ('+@account_list+') and ([policy1] like '+@policy+' or [policy2] like '+@policy+' or [policy2] like '+@policy+') then [Amount]
else 0
end as [Amount]
from CTGS 
where [Debit_Account] in ('+@account_list+') or [Credit_Account] in ('+@account_list+')';

EXEC sp_executesql @sql;
Peter B
  • 22,460
  • 5
  • 32
  • 69
Dinh Quang Tuan
  • 468
  • 3
  • 10
  • Does this answer your question? [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) – Twonky Jan 09 '20 at 09:23
  • 1
    [`sp_executesql`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) allows you to use parameters. You should rework the script to use them. – sticky bit Jan 09 '20 at 09:23
  • That code is product specific? Which dbms are you using? (Perhaps SQL Server?) – jarlh Jan 09 '20 at 09:32

1 Answers1

1

You forgot to add string delimiter quotes into your dynamic SQL string.

Your code (repeated several times):

' .... [policy1] like '+@policy+' .... '

If you add this:

DECLARE @qt VARCHAR(1) = CHAR(39)

Then you can fix the problem by using this (also several times):

' .... [policy1] like '+@qt+@policy+@qt+' .... '

Note that instead of @qt you can also use '' (two single quotes) directly in your strings where it is needed, but in my opinion that often produces hard-to-read code.

Also note that if @policy could itself also contain one or more quotes, you'd have to escape those with a REPLACE as follows:

' .... [policy1] like '+@qt + REPLACE(@policy, @qt, @qt+@qt) + @qt+' .... '
Peter B
  • 22,460
  • 5
  • 32
  • 69
  • It would be easier and safer to use `@policy` as a proper parameter *inside* the query string ie `policy1 like @policy or policy2 like @policy ...` and pass the value as a parameter value to `sp_executesql`. – Panagiotis Kanavos Jan 09 '20 at 15:21