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, Aug 31 '14 at 12:01
http://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure — Laurence, Aug 31 '14 at 12:06
@ElecticLIama:set @Kol=cursor for SELECT * FROM AvarezMashaghel WHERE am in (SELECT Code FROM Mashaghel Where + @StrSQL+ ) — ashkufaraz, Aug 31 '14 at 12:08

score 1 · Answer 1 · answered Aug 31 '14 at 16:46

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.

score 0 · Answer 2 · answered Sep 01 '14 at 03:02

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 01 '14 at 03:02

ashkufaraz

5,179
6
51
82

concat string SQL to sql command

2 Answers2