question

NaamiAyman-5203 avatar image
0 Votes"
NaamiAyman-5203 asked NaamiAyman-5203 commented

An expression of non-boolean type specified in a context where a condition is expected

Hi,

I have an issue with this stored procedure (code below), it returns me the following error:

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

Can you please help me with it ?
Thank you


 DECLARE @table_name nvarchar(max);
     DECLARE @sequence nvarchar(max);
     DECLARE @batch_no nvarchar(max);
     DECLARE @SQL nvarchar(500);
     DECLARE @ParmDef nvarchar(500)
          
     SELECT @SQL ='select @batch_no_output=(select max(batch_no) from [glb_ops_tsq_audit].[batch]), @table_name_out=a.table_name, @sequence_out=a.sequence from [glb_ops_tsq_audit].[table_config] a
     left join (select table_name,sequence,max_batch from (select table_name,sequence,max(batch_no) max_batch from [glb_ops_tsq_audit].[batch_processing_log]
     group by table_name,sequence) a 
     where max_batch=(select(max(batch_no)) aa from [glb_ops_tsq_audit].[batch])) b
     on trim(a.table_name)=trim(a.table_name) and a.sequence=b.sequence
     where b.table_name is null'  ;
        
     SET @ParmDef = N'@table_name_out nvarchar(max) OUTPUT,@sequence_out nvarchar(max) OUTPUT,@batch_no_output nvarchar(max) OUTPUT';
        
     EXEC sp_executesql @SQL, @ParmDef, @table_name_out=@table_name OUTPUT,@sequence_out=@sequence OUTPUT,@batch_no_output=@batch_no OUTPUT;
     select @batch_no, @table_name ,@sequence





sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered NaamiAyman-5203 commented

Try increasing the size:

 DECLARE @SQL nvarchar(max)

Show the new errors, if any.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanHoffmann avatar image
0 Votes"
StefanHoffmann answered NaamiAyman-5203 commented

Start by correcting the SQL statement in @SQL. Run it standalone to get it right.

btw, you don't need dynamic SQL in general here.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StefanHoffmann,

I ran the query in a standalone query, and it worked fine.
The issue rises when I run the stored proc with all 3 column/variables, here are the scenarios I have:
1- table_name & sequence: OK
2- batch_no: OK
3 - batch_no & sequence : Not OK
4- batch_no & tabe_name Not OK

Can you explain to me how dynamic SQL is not needed ?

Regards,

0 Votes 0 ·

You don't need dynamic SQL, cause the executed statement is static without input parameters.

0 Votes 0 ·

the stored procedure will be ingested in azure data factory pipelines, and values will change in each run, I hope it would make sense. If not please tell me if there are other alternatives.
Kind Regards,

0 Votes 0 ·
NaamiAyman-5203 avatar image
0 Votes"
NaamiAyman-5203 answered

Hi,

The issue was in @SQL value declared with limit of 500, I changed it to max and the stored proc worked fine.

Thank you all for your help.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.