Dynamic sql query

Question

Hi there..

This is my query... but it returns incorrect syntax near '+'

 DECLARE @refKlinik_id INT
SET @refKlinik_id  = 24

DECLARE @kriter VARCHAR(50)

IF @refKlinik_id <=0 
BEGIN
  SET @kriter = ''
END
ELSE
    SET @Kriter =  'AND H.refKlinik_id =' + @refKlinik_id

SELECT        H.adi + ' ' + H.soyadi AS Hasta, H.tcKimlikNo, CONVERT(varchar, H.dogumTarihi, 103) AS DogumTarihi, K.kisaAdi AS Klinik, A.acikAdres + A.ilce + A.il AS Adres,
                          A.tel1, A.gsm, CASE H.hastaKartiVar WHEN 1 THEN 'Hasta Kartı Sahibi' WHEN 0 THEN 'Hasta Kartı Yok' WHEN NULL 
                         THEN 'Hasta Kartı Yok' END AS HastaKartiDurumu
FROM            Hastalar AS H INNER JOIN
                         Klinikler AS K ON K.klinik_id = H.refKlinik_id INNER JOIN
                         Adresler AS A ON A.refHasta_id = H.hasta_id
WHERE        (K.refKlinikGrup_id = 1) AND (H.durumu = 1) + @kriter + AND (A.aktif = 1)
ORDER BY H.adi

Answer 1

You can't concatenate @kriter into the rest of the where clause like that - it's evaluating it as a string, not a piece of SQL. You'd have to put the whole thing in a string variable and then use Exec

Answer 2

You're not using dynamic sql correctly - you have to concatenate the query into a varchar/nvarchar variable and then execute that.

e.g.

DECLARE @MyParam INTEGER
SET @MyParam = 1

DECLARE @nSQL NVARCHAR(1000)
SET @nSQL = 'SELECT * FROM SomeTable WHERE SomeField = @MyParam'
EXECUTE sp_executesql @nSQL, N'@MyParam INTEGER', @MyParam

Be careful with dynamic sql, this approach I've exampled is preferred over just concatenating the @MyParam directly on to the string as it helps guard against SQL injection.

In your case, you don't actually need to use dynamic SQL, you could do:

DECLARE @refKlinik_id INT
SET @refKlinik_id  = 24

SELECT        H.adi + ' ' + H.soyadi AS Hasta, H.tcKimlikNo, CONVERT(varchar, H.dogumTarihi, 103) AS DogumTarihi, K.kisaAdi AS Klinik, A.acikAdres + A.ilce + A.il AS Adres,
                          A.tel1, A.gsm, CASE H.hastaKartiVar WHEN 1 THEN 'Hasta Kartı Sahibi' WHEN 0 THEN 'Hasta Kartı Yok' WHEN NULL 
                         THEN 'Hasta Kartı Yok' END AS HastaKartiDurumu
FROM            Hastalar AS H INNER JOIN
                         Klinikler AS K ON K.klinik_id = H.refKlinik_id INNER JOIN
                         Adresler AS A ON A.refHasta_id = H.hasta_id
WHERE        (K.refKlinikGrup_id = 1) AND (H.durumu = 1) AND (@refKlinik_id<=0 OR H.refKlinik_id = @refKlinik_id) AND (A.aktif = 1)
ORDER BY H.adi

Answer 3

This should work:

 DECLARE @refKlinik_id INT
declare @query varchar(1000)
SET @refKlinik_id  = 24

DECLARE @kriter VARCHAR(50)

IF @refKlinik_id <=0 
BEGIN
  SET @kriter = ''
END
ELSE
    SET @Kriter =  'AND H.refKlinik_id =' + cast(@refKlinik_id as varchar(10))

set @query='SELECT        H.adi + '' '' + H.soyadi AS Hasta, H.tcKimlikNo, CONVERT(varchar, H.dogumTarihi, 103) AS DogumTarihi, K.kisaAdi AS Klinik, A.acikAdres + A.ilce + A.il AS Adres,
                          A.tel1, A.gsm, CASE H.hastaKartiVar WHEN 1 THEN ''Hasta Kartı Sahibi'' WHEN 0 THEN ''Hasta Kartı Yok'' WHEN NULL 
                         THEN ''Hasta Kartı Yok'' END AS HastaKartiDurumu
FROM            Hastalar AS H INNER JOIN
                         Klinikler AS K ON K.klinik_id = H.refKlinik_id INNER JOIN
                         Adresler AS A ON A.refHasta_id = H.hasta_id
WHERE        (K.refKlinikGrup_id = 1) AND (H.durumu = 1)' + @kriter + 'AND (A.aktif = 1)
ORDER BY H.adi'
exec(@query)

Answer 4

May I suggest before typing a single character of Dynamic SQL that you familiarise yourself with the following "essential reading".

The Curse and Blessings of Dynamic SQL

Then if you have any questions about the content just let me know.