SQL-Server Incorrect syntax in string query

Question

I am trying to create a dynamic query in sql-server as a string, it works perfect if the @SupervisorId or @UnitId is null, however when i test it like below i get this error (meaning if @SupervisorId or @UnitId is not null):

Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'c33fff6'. Msg 153, Level 15, State 2, Line 14 Invalid usage of the option next in the FETCH statement. Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'c33fff6'.

what i tried:

declare @SupervisorId       uniqueidentifier = null;
declare @UnitId             uniqueidentifier = null;
declare @Name               nvarchar(max) = null;
declare @PersonTypeId       int;
declare @PageNum            int;
declare @PageSize           int;
declare @KW nvarchar(max);
declare @Query nvarchar(max);
declare @Query2 nvarchar(max);
declare @WhereClause nvarchar(max);

set @PageNum = 0;
set @PageSize = 50;
set @PersonTypeId = 10003;
set @UnitId = '5c33fff6-ae91-4946-92e2-50f8ae9bd6f5'; 

set @WhereClause = ' 1=1 ';
if (@Name is not null)
    set @WhereClause =@WhereClause + ' And p.Name like N'''+'%'+cast(@Name as nvarchar(4000))+'%'+''' or p.Family like N'''+'%'+cast(@Name as nvarchar(4000))+'%'+''' ';
if(@SupervisorId is not null)
    set @WhereClause = @WhereClause + ' AND p.SupervisorId in ('+ CAST(@SupervisorId as nvarchar(4000)) +') ';
if(@UnitId is not null)
    set @WhereClause = @WhereClause + ' AND p.UnitId in (' + CAST(@UnitId as nvarchar(4000)) + ') ';

    Set @Query = '
     SELECT u.*, p.*, uu.Name as UnitName, jj.Name as JobName, pp.Name + '' '' + pp.Family as SupervisorName, s.IsActive as IsActive
        FROM [Membership].Users u 
        LEFT JOIN [Membership].Persons p on p.ID = u.ID 
        INNER JOIN [kernel].BaseEntity s on s.ID = p.ID and s.IsDelete = 0
        LEFT JOIN Base.Units uu on uu.ID = p.UnitId
        LEFT JOIN Base.Jobs jj on jj.ID = p.JobId
        LEFT JOIN [Membership].Persons pp on pp.ID = p.SupervisorId

    Where '+@WhereClause+ ' and p.PersonTypeId = '+ CAST(@PersonTypeId as nvarchar(100)) +' 

    Order by p.Family DESC
    Offset '+CAST(@PageSize as nvarchar(10))+' * ('+CAST(@PageNum as nvarchar(10))+') Rows
    Fetch next '+CAST(@PageSize as nvarchar(10))+' ROWS ONLY';


    set @Query2 ='
     SELECT COUNT(*)
        FROM [Membership].Users u 
        LEFT JOIN [Membership].Persons p on p.ID = u.ID 
        INNER JOIN [kernel].BaseEntity s on s.ID = p.ID and s.IsDelete = 0
        LEFT JOIN Base.Units uu on uu.ID = p.UnitId
        LEFT JOIN Base.Jobs jj on jj.ID = p.JobId
        LEFT JOIN [Membership].Persons pp on pp.ID = p.SupervisorId

    Where '+@WhereClause + ' and p.PersonTypeId = '+ CAST(@PersonTypeId as nvarchar(100)) +'' ;

    exec (@Query);
    exec (@Query2);

If you print the values of @query and @query2 instead of/as well as trying to execute them, the "typos" caused by the generating code should jump out at you, without having to post a question to SO. (You can print them to a console, or at least to a log at a suitable logging level) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 23, 2018 at 10:51
@Damien_The_Unbeliever as if i didn't try that, i know the drill sometimes u are in a bust and your brain freezes. but thank you. — Masoud Andalibi
– Masoud Andalibi, Commented Jul 23, 2018 at 10:54
If you'd tried that, it would have been far kinder to readers to have included that output in your question, rather than expecting us to run all of the logic through our heads. It's far easier to spot the error in the output then work out which piece of original code produced the error than the other way around. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 23, 2018 at 10:57

Gordon Linoff · Accepted Answer · 2018-07-23 10:46:24Z

1

You need to enclose the values in single quotes. This requires doubling up on the quotes. So instead of:

set @WhereClause = @WhereClause + ' AND p.UnitId in (' + CAST(@UnitId as nvarchar(4000)) + ') ';

You need:

set @WhereClause = @WhereClause + ' AND p.UnitId in (''' + CAST(@UnitId as nvarchar(4000)) + ''') ';

Note that if you actually have multiple values, you need to double up on the single quotes within the list as well.

answered Jul 23, 2018 at 10:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Masoud Andalibi Over a year ago

it might be single or multiple, thank you let me check this out,

Masoud Andalibi Over a year ago

worked like a magic, thank you =) however i need to wait for acceping the answer.

Collectives™ on Stack Overflow

SQL-Server Incorrect syntax in string query

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related