concat string SQL to sql command

Question

 declare  
 @Kol as cursor
 if(len(@to_num)<>0) 
   begin
       if(len(@to_num)<18)  
        begin
            set @payam='error'              
            return
        end
        else
        begin
            if(LTRIM(RTRIM(@from_num)) = LTRIM(RTRIM(@to_num))) 
                set @StrSQL = 'Mashaghel.Code='' & LTRIM(RTRIM(@to_num)) & '''
            else
            begin
                if (LTRIM(RTRIM(@StrSQL)) = '') 
                    set @StrSQL = 'Mashaghel.Code<='' & LTRIM(RTRIM(to_num)) & '''
                else
                    set @StrSQL = @StrSQL + ' AND Mashaghel.Code<='' & LTRIM(RTRIM(@to_num)) & '''
            end

        end         
   end

set @Kol=cursor for SELECT * FROM AvarezMashaghel WHERE am in (SELECT Code FROM Mashaghel Where + @StrSQL+  )

This code say error::

set @Kol=cursor for SELECT * FROM AvarezMashaghel WHERE am in (SELECT Code FROM Mashaghel Where + @StrSQL+  )

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

How can I add string StrSQL to SQL command ?

What line does the error occur on? Why did you ask the question 6 times? — Nick.Mc
– Nick.Mc, Commented Aug 31, 2014 at 12:01
@ElecticLIama:set @Kol=cursor for SELECT * FROM AvarezMashaghel WHERE am in (SELECT Code FROM Mashaghel Where + @StrSQL+ ) — ashkufaraz
– ashkufaraz, Commented Aug 31, 2014 at 12:08

Ann L. · Accepted Answer · 2014-08-31 16:46:57Z

Your primary problem is that it isn't possible to combine SQL commands with strings representing SQL commands. This (from your code) is just not going to work.

 SELECT Code FROM Mashaghel Where + @StrSQL  -- won't work

(Your error message is telling you that the expression + @StrSQL doesn't evaluate to true or false: it evaluates to a string.)

The only way to build up an expression dynamically and then execute it is to build all of the expression dynamically, like this:

DECLARE @sql nvarchar(200)
SET @sql = 'SELECT Code FROM Mashaghel Where ' + @StrSQL
exec @sql

But I highly doubt that will work with a cursor, although I could be wrong. It's a long time since I used a cursor.

You could try something like this:

set @Kol=cursor for 
  SELECT * FROM AvarezMashaghel 
           WHERE ( LTRIM(RTRIM(@from_num)) = LTRIM(RTRIM(@to_num))) 
             AND Mashaghel.Code= LTRIM(RTRIM(@to_num)) )
           OR    (Mashaghel.Code<=LTRIM(RTRIM(to_num)) )

It won't be that speedy, but it would probably work.

ashkufaraz · Accepted Answer · 2014-09-01 03:02:18Z

0

set @StrSQL = 'Declare  users_cursor CURSOR FOR SELECT Code FROM Mashaghel Where ' + @StrSQL+'

exec sp_executesql  @StrSQL


FETCH NEXT FROM users_cursor
INTO ...

WHILE @@FETCH_STATUS = 0
....

answered Sep 1, 2014 at 3:02

ashkufaraz

5,2997 gold badges54 silver badges85 bronze badges

Collectives™ on Stack Overflow

concat string SQL to sql command

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related