advance sql query

advance sql query

  

i want to pass filtered ids in advance sql query to update. How should i pass because advance sql query input parameter cant be a list . I tried with text variable by converting ids to string join but it gives an error sql commant not properly ended


my query:

SyntaxEditor Code Snippet

update {UserProfile}
set {UserProfile}.[ISLOCKED] = @IsLocked 
WHERE {UserProfile}.[UserId] IN @UserIds;

IsLocked=False

UserIds="18,19,20"


Is there any way to pass them in correct format?

Hi Lovish,

Based on your post it seems you are missing the parenthesis around your user ids, “IN(@UserIds)”.

Justin

Justin Babel wrote:

Hi Lovish,

Based on your post it seems you are missing the parenthesis around your user ids, “IN(@UserIds)”.

Justin

i had tried with "(19,20)". But still it is giving me error 

sql commant not properly ended



Lovish Goyal wrote:

Justin Babel wrote:

Hi Lovish,

Based on your post it seems you are missing the parenthesis around your user ids, “IN(@UserIds)”.

Justin

i had tried with "(19,20)". But still it is giving me error 

sql commant not properly ended


Hi Lovish,

I think you need to set @UserIds parameter's Expend Inline property as 'Yes', remove semicolon(;) symbol & IsLocked =0.

Sachin


As said in the above posts but to put it all together to avoid confusion. The correct way is

update {UserProfile}
set {UserProfile}.[ISLOCKED] = @IsLocked 
WHERE {UserProfile}.[UserId] IN (@UserIds)

And to set the UserIds property to expand inline


Hello,


I have the same problem. Expand Inline is true, the query works when exist only one value for example "5", but when I have two values for example "1,2" occurs one error: 

An expression of non-boolean type specified in a context where a condition is expected, near ','.


My expression is 

SyntaxEditor Code Snippet

{UsersStatusLabel}.[Id] IN (@FilterListState)


Thanks in advance


Double check your ID string and ensure you haven't added a "," to the end. For example 1,2,3, instead of 1,2,3 or a "," at the front even ,1,2,3 

You can look at Executed SQL window, and debug from there...

Or if you're database is Oracle, you can look at v$session to see the real executed sql.


John Williams wrote:

Double check your ID string and ensure you haven't added a "," to the end. For example 1,2,3, instead of 1,2,3 or a "," at the front even ,1,2,3 

Hi,  no... the value is correct. 


Ok then as Harlin said you need to get the actual generated SQL and post that here, we should be able to spot what is wrong from that.

I don't know how... but now work with two or more values. My problem now is when I don't have a values...

SyntaxEditor Code Snippet

    @FilterListState = '' or {UsersStatusLabel}.[Id] IN (@FilterListState) or 
    @FilterListBusinessPlan = '' or {BusinessPlan}.[Id] IN (@FilterListBusinessPlan)

I try this way but when @FilterListState or @FilterListBusinessPlan are "" returned: Incorrect syntax near '='.