Dynamic queries - Error converting data type nvarchar to BIGINT

Dynamic queries - Error converting data type nvarchar to BIGINT

  

Hi Team ,

I am trying to create dynamic queries based on the input sent where in i am not able to proceed because of the below error .

Error converting data type nvarchar to BIGINT


Dynamic Query :

SyntaxEditor Code Snippet

Update {ArchiveToEntity} set {ArchiveToEntity}.[IsArchive]='true' where
{ArchiveToEntity}.[EntityTypeId]=@EntityTypeId and {ArchiveToEntity}.[EntityRecordId] in (@selectQuery)


Inputs Passed :

EntityTypeId = 1

selectQuery : Select {TableName}.[Id] from {TableName} where {TableName}.[ForeignKey]= Integer  (This is the input based created query which is sent as input to SQLQuery )


As i understand it is failing only while executing the subquery which is sent dynamically . I even tried try_cast() function to cast the datatype but it is always returning 0 since it is failign to convert .Can someone please suggest what is the issue here with this or any other alternative to acheive this.Please let me know if you would want to have a look at oml file .

Hi Naga,

Are you sure that "IsArchive" is a Text Attribute? Given its name I would assume it's a Boolean, which means in the database it's represented by an integer. So it should read "set {ArchiveEntity}.[IsArchive] = 0".

Solution

Hi Kilian,

I'm not sure, but at least in SQL Server, it understand the 'True'
I think the problem is with the @selectQuery parameter.

Naga, 

Did you set the Expand Inline property of this query input parameter to True? If not, your subquery will be escaped and the database will think it is a string, than the error.

Cheers.

Solution

Thanks much Eduardo Jauch , it solved the problem .