'='附近的语法不正确 [英] Incorrect syntax near '='

问题描述

我收到错误：（SQL Server 2014）

I get error:(SQL Server 2014)

Incorrect syntax near '='

这是我的SP，我希望过滤数据与商店程序：

我尝试所有方式。我在我的代码中使用以下语句得到语法错误，我已经通过它，除非我错过了什么，否则我看不出任何错误：

This is my SP, I want filt data with store procedure:
I try all the ways. I am getting a syntax error with the following statement within in my code, i have went through it and i cant see anything wrong unless i have missed something:

ALTER PROCEDURE [dbo].[XACNHANTHUNOPTIENHOAMANG_Test1] @TUNGAY varchar(20), @DENNGAY varchar(20), @PhongBanId int, @DiaBanId int, @ToBHKV int, @NhanVienId varchar(20), @TrangThaiId int, @loaitien int
    as
    begin
    	DECLARE @sql nvarchar(max)
    	SET @sql=
    	    'select distinct TIEN.TienHDKHId, phong.TenPhongBan, TOBH.TenPhongBan AS TOBHKV, TKV.TenTuyenDT AS TenTuyen, nv.TuyenId, nhanvien.HoTen as HoTen, TIEN.NguoiCNID,
    		case when' +CAST(@loaitien AS NVARCHAR(10))+ '=1 then TIEN.TienKhachHangTT
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=2 then TIEN.TienThietBi
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=3 then TIEN.TienSetTopBox
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=4 then TIEN.TienTheChan
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=5 then TIENSUDUNGDVGT
    		end as TienTT,
    		.....
    		where tb.MaTB is not null 
    		.....
    		AND (nhanvien.NhanVienId IN (' +@NHANVIENID+ ')OR ' +@NHANVIENID+ '=0)
    		and nv.TuyenId=case when'+ CONVERT(varchar(20),@DiaBanId)+ '=0 then nv.TuyenId else'+ CONVERT(varchar(20),@DiaBanId) +'end 
    		and BHKV.TuyenId=NV.TuyenId
    		and BHKV.ToBHKVId=CASE WHEN'+ CONVERT(varchar(20),@ToBHKV) +'=0 THEN BHKV.ToBHKVId ELSE'+ CONVERT(varchar(20),@ToBHKV)+ 'END
    	    AND BHKV.ToBHKVId=TOBH.PhongBanId 
    		and dm.PhongBanId=case when'+ CONVERT(varchar(20),@PhongBanId) +'=0 then dm.PhongBanId else'+ CONVERT(varchar(20),@PhongBanId) + 'end
    		.....
    		and isnull(TIEN.TrangThai,0)=CASE WHEN'+ convert(varchar(10),@TrangThaiId) + '=2 then isnull(TIEN.TrangThai,0) else'+ convert(varchar(10),@TrangThaiId) +'end
    		order by TenPhongBan, TOBH.TenPhongBan , TKV.TenTuyenDT  , nhanvien.HoTen'
    		EXEC sp_executesql  @SQL
    end

我无法修复他的错误。我需要帮助。和@NHANVIENID一样'101,102,103'。

I cannot fix this error. I need help. And @NHANVIENID like '101,102,103'.

推荐答案

请尝试

Please try

ALTER PROCEDURE [dbo].[XACNHANTHUNOPTIENHOAMANG_Test1] @TUNGAY varchar(20), @DENNGAY varchar(20), @PhongBanId int, @DiaBanId int, @ToBHKV int, @NhanVienId varchar(20), @TrangThaiId int, @loaitien int
    as
    begin
    	DECLARE @sql nvarchar(max)
    	SET @sql=
    	    'select distinct TIEN.TienHDKHId, phong.TenPhongBan, TOBH.TenPhongBan AS TOBHKV, TKV.TenTuyenDT AS TenTuyen, nv.TuyenId, nhanvien.HoTen as HoTen, TIEN.NguoiCNID,
    		case when' +CAST(@loaitien AS NVARCHAR(10))+ '=1 then TIEN.TienKhachHangTT
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=2 then TIEN.TienThietBi
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=3 then TIEN.TienSetTopBox
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=4 then TIEN.TienTheChan
    			 when' +CAST(@loaitien AS NVARCHAR(10))+ '=5 then TIENSUDUNGDVGT
    		end as TienTT,
    		.....
    		where tb.MaTB is not null 
    		.....
    		AND (nhanvien.NhanVienId IN (' +@NHANVIENID+ ') OR nhanvien.NhanVienId =0)
    		and nv.TuyenId=case when'+ CONVERT(varchar(20),@DiaBanId)+ '=0 then nv.TuyenId else'+ CONVERT(varchar(20),@DiaBanId) +'end 
    		and BHKV.TuyenId=NV.TuyenId
    		and BHKV.ToBHKVId=CASE WHEN'+ CONVERT(varchar(20),@ToBHKV) +'=0 THEN BHKV.ToBHKVId ELSE'+ CONVERT(varchar(20),@ToBHKV)+ 'END
    	    AND BHKV.ToBHKVId=TOBH.PhongBanId 
    		and dm.PhongBanId=case when'+ CONVERT(varchar(20),@PhongBanId) +'=0 then dm.PhongBanId else'+ CONVERT(varchar(20),@PhongBanId) + 'end
    		.....
    		and isnull(TIEN.TrangThai,0)=CASE WHEN'+ convert(varchar(10),@TrangThaiId) + '=2 then isnull(TIEN.TrangThai,0) else'+ convert(varchar(10),@TrangThaiId) +'end
    		order by TenPhongBan, TOBH.TenPhongBan , TKV.TenTuyenDT  , nhanvien.HoTen'
    		EXEC sp_executesql  @SQL
    end

由rhgarner更正。



错误是因为查询开发了一个语句'when0 '而是给一个空格。

As corrected by rhgarner.

The error is because the query develops a statement 'when0' instead give a space.

这篇关于'='附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！