Inserting whole sql script in variable

Question

I have problem inserting sql script in the variable.I have this code

   declare @result_var varchar(max);
SET @result_var=''; 
DECLARE @cursor CURSOR, 
@name   VARCHAR(100) 
SET @cursor = CURSOR 
FOR SELECT [NAME] 
    FROM   [iflvs08].mds.mdm.lv_budget_employee
OPEN @cursor
FETCH next FROM @cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF (RIGHT(@Name, 1) = ' ')
        begin
            IF( Len(@result_var) > 1 ) 
              SET @result_var=@result_var + ','+ @name 
            ELSE 
              SET @result_var=@name 
        END
      FETCH next FROM @cursor INTO @name 
  END

I want to insert into variable it but when try to insert it like this

set @sql='code here'

It get confuse whit this ' symbol and dont insert it I always get error like this Msg 102, Level 15, State 1, Line 13 Incorrect syntax near ' begin IF( Len(@result_var) > 1 ) SET @result_var=@result_var + '.

There are better ways than using cursors for concatenating values from several rows into a comma-separated list; see e.g. stackoverflow.com/questions/194852/…. — stakx - no longer contributing
– stakx - no longer contributing, Commented Mar 27, 2015 at 14:21
What are you really trying to do here? Cursors are very poor performance wise and should be used only in certain situations. Data manipulation is not one of those times. — Sean Lange
– Sean Lange, Commented Mar 27, 2015 at 14:21

roman · Accepted Answer · 2015-03-27 14:19:42Z

3

basically you have to replace all ' symbols with ''

set @sql = '

  declare @result_var varchar(max);
SET @result_var=''''; 
DECLARE @cursor CURSOR, 
@name   VARCHAR(100) 
SET @cursor = CURSOR 
FOR SELECT [NAME] 
    FROM   [iflvs08].mds.mdm.lv_budget_employee
OPEN @cursor
FETCH next FROM @cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF (RIGHT(@Name, 1) = '' '')
        begin
            IF( Len(@result_var) > 1 ) 
              SET @result_var=@result_var + '',''+ @name 
            ELSE 
              SET @result_var=@name 
        END
      FETCH next FROM @cursor INTO @name 
  END'

answered Mar 27, 2015 at 14:19

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nils Ozols Over a year ago

Thx it was so simple :)

Collectives™ on Stack Overflow

Inserting whole sql script in variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related