动态sql查询

Question

这是我的问题。但它在'+‘附近返回不正确的语法

 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

您没有正确使用动态sql -您必须将查询连接到varchar/nvarchar变量中，然后执行该变量。

例如：

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

要小心使用动态sql，我举的例子中的这种方法比直接将@MyParam连接到字符串上更可取，因为它有助于防止SQL注入。

在您的例子中，您实际上不需要使用动态SQL，您可以这样做：

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 2

您不能像这样将@kriter连接到where子句的其余部分-它将其计算为一个字符串，而不是一段SQL。您必须将整个代码放在一个字符串变量中，然后使用Exec

Answer 3

这应该是可行的：

 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)