SQL Server stored procedure concat string as query

Question

I wonder if can I define some parts of the my sql query as a string.

I worked on the code below but I could not manage to concat that pre-defined string part to the existing query.

Actually @sirketid, @uzman, @basvurukodu params works well, however the @ORA_BASVURU_KESIN_KOSUL param is causing a problem.

I think because it has some sql-spesific expression like and, it is treated diffrently than simple variables used for comparison or assigning such as @sirket_id.

It does not throw any error message, the code simply does not excute the operation.

SET @ORA_BASVURU_KESIN_KOSUL = 'and akftif = 1';

UPDATE basvuru 
SET sirket = @sirketid,
    talep_gorevlendirme_rapor = 'G',
    birimi = 'SS', 
    uzman = @uzman,
WHERE
    kod = @basvurukodu + ' ' + @ORA_BASVURU_KESIN_KOSUL;

Can I concat query parts like this, if so, how?

Thanks

you will need dynamic sql. first concatenate the whole SQL you want to execute and finally execute the SQL with EXEC — Esteban P.
– Esteban P., Commented Aug 7, 2017 at 12:57
You cannot concat e new condition on like that. To do that you have to make the query dynamic — Søren Kongstad
– Søren Kongstad, Commented Aug 7, 2017 at 12:57
Background material for your perusal, and also this more in general for dynamic SQL. There's a lot to say about this topic. Passing in arbitrary SQL as a parameter is almost never a good approach, due to the risk of injection involved. — Jeroen Mostert
– Jeroen Mostert, Commented Aug 7, 2017 at 13:01

Esteban P. · Accepted Answer · 2017-08-07 13:03:55Z

5

Your query should work like:

Concatenate the whole Query
Execute the query with EXEC

of course you have to declare the other variables too:

SET @ORA_BASVURU_KESIN_KOSUL = 'and akftif = 1';

DECLARE @MyExecSQL varchar(2000) =
    'UPDATE basvuru 
        SET sirket = @sirketid
           ,talep_gorevlendirme_rapor = ''G''
           ,birimi = ''SS''
           ,uzman = ' + @uzman + 
     ' WHERE kod = ' + @basvurukodu + 
        ' ' + @ORA_BASVURU_KESIN_KOSUL + ''
;     
EXEC @MyExecSQL

answered Aug 7, 2017 at 13:03

Esteban P.

2,8203 gold badges31 silver badges45 bronze badges

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

2 Comments

Jeroen Mostert Over a year ago

Use NVARCHAR(MAX), there's no reason to settle for anything less. @sirketid will be undefined. Concatenating string parameters this way won't work, they'll need to be escaped. Using EXEC rather than sp_executesql with parameters may cause a lot of query plan cache pollution. With dynamic SQL, the devil's in the details.

AJ Dhaliwal Over a year ago

I had to use EXEC (@MyExecSQL)

Collectives™ on Stack Overflow

SQL Server stored procedure concat string as query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related