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

Solution

Your query should work like:

1. Concatenate the whole Query
2. 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

Comments

[Esteban P.]

you will need dynamic sql. first concatenate the whole SQL you want to execute and finally execute the SQL with EXEC

[Søren Kongstad]

You cannot concat e new condition on like that. To do that you have to make the query dynamic

[Jeroen Mostert]

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.

[Ali insan Soyaslan]

This is really complicated than I thoght :)

[Jeroen Mostert]

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.