原来是这样的
ALTER PROCEDURE [dbo].[T_CustomerInfos_NewTaskAllotSelect]
@Monthold int,
@RegionID TINYINT,
@ProvincesID TINYINT,
@CityID TINYINT,
@UsersTypeID TINYINT,
@SourceCustomersID TINYINT,
@PurchaseChannelsID TINYINT,
@BrandID TINYINT,
@ProductNameID TINYINT,
@IsClubMember TINYINT,
@RegistrationChannelsID TINYINT,
@OutboundTimes INT
AS
DECLARE @whereStr VARCHAR(255),
@count INT
SET @count=0
SET @whereStr=''
IF @Monthold IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND DATEDIFF(Month, [DateOfBirth], getdate())='+Convert(Varchar(10),@Monthold)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' DATEDIFF(Month, [DateOfBirth], getdate())='+Convert(Varchar(10),@Monthold)
END
SET @count=@count +1
END
IF @RegionID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [RegionID]='+Convert(Varchar(10),@RegionID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [RegionID]='+Convert(Varchar(10),@RegionID)
END
SET @count=@count +1
END
IF @ProvincesID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [ProvincesID]='+Convert(Varchar(10),@ProvincesID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [ProvincesID]='+Convert(Varchar(10),@ProvincesID)
END
SET @count=@count +1
END
IF @CityID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [CityID]='+Convert(Varchar(10),@CityID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [CityID]='+Convert(Varchar(10),@CityID)
END
SET @count=@count +1
END
IF @UsersTypeID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [UsersTypeID]='+Convert(Varchar(10),@UsersTypeID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [UsersTypeID]='+Convert(Varchar(10),@UsersTypeID)
END
SET @count=@count +1
END
IF @SourceCustomersID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [SourceCustomersID]='+Convert(Varchar(10),@SourceCustomersID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [SourceCustomersID]='+Convert(Varchar(10),@SourceCustomersID)
END
SET @count=@count +1
END
IF @PurchaseChannelsID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [PurchaseChannelsID]='+Convert(Varchar(10),@PurchaseChannelsID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [PurchaseChannelsID]='+Convert(Varchar(10),@PurchaseChannelsID)
END
SET @count=@count +1
END
IF @BrandID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [BrandID]='+Convert(Varchar(10),@BrandID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [BrandID]='+Convert(Varchar(10),@BrandID)
END
SET @count=@count +1
END
IF @ProductNameID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [ProductNameID]='+Convert(Varchar(10),@ProductNameID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [ProductNameID]='+Convert(Varchar(10),@ProductNameID)
END
SET @count=@count +1
END
IF @IsClubMember IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [IsClubMember]='+Convert(Varchar(10),@IsClubMember)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [IsClubMember]='+Convert(Varchar(10),@IsClubMember)
END
SET @count=@count +1
END
IF @RegistrationChannelsID IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [RegistrationChannelsID]='+Convert(Varchar(10),@RegistrationChannelsID)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [RegistrationChannelsID]='+Convert(Varchar(10),@RegistrationChannelsID)
END
SET @count=@count +1
END
IF @OutboundTimes IS NOT null
BEGIN
IF(@count>0)
BEGIN
SELECT @whereStr=@whereStr+' AND [OutboundTimes]='+Convert(Varchar(10),@OutboundTimes)
END
ELSE
BEGIN
SELECT @whereStr=@whereStr+' [OutboundTimes]='+Convert(Varchar(10),@OutboundTimes)
END
SET @count=@count +1
END
IF @count>0
SELECT @whereStr='SELECT * FROM T_CustomerInfos WHERE '+ @whereStr
ELSE
SELECT @whereStr='SELECT * FROM T_CustomerInfos'
--return @whereStr
EXEC (@whereStr)
-------------------------------------------------------------------------------------------------------------------------
现在业务需求更改了
以上条件都没变 SELECT @whereStr='SELECT
0 as enbale,
CustomerID,
MemberNumber,
FatherName,
BabyName,
BabySex,
DateOfBirth,
ContactPhoneNumber,
(select [ProvinceName] from T_Province where ProvinceID = T_CustomerInfos.ProvincesID) as ProvincesID,
(select [CityName] from T_City where CityID = T_CustomerInfos.CityID) as CityID,
(select [ParaName] from T_SysPara where GroupID=3 and ParaID = T_CustomerInfos.UsersTypeID) as UsersTypeID,
(select [ParaName] from T_SysPara where GroupID=4 and ParaID = T_CustomerInfos.SourceCustomersID) as SourceCustomersID,
(select [ParaName] from T_SysPara where GroupID=9 and ParaID = T_CustomerInfos.IsClubMember) as IsClubMember,
OutboundTimes,
OutboundTimes AS enbale1
FROM T_CustomerInfos WHERE '+ @whereStr
ELSE
SELECT @whereStr='SELECT
0 as enbale,
CustomerID,
MemberNumber,
FatherName,
BabyName,
BabySex,
DateOfBirth,
ContactPhoneNumber,
(select [ProvinceName] from T_Province where ProvinceID = T_CustomerInfos.ProvincesID) as ProvincesID,
(select [CityName] from T_City where CityID = T_CustomerInfos.CityID) as CityID,
(select [ParaName] from T_SysPara where GroupID=3 and ParaID = T_CustomerInfos.UsersTypeID) as UsersTypeID,
(select [ParaName] from T_SysPara where GroupID=4 and ParaID = T_CustomerInfos.SourceCustomersID) as SourceCustomersID,
(select [ParaName] from T_SysPara where GroupID=9 and ParaID = T_CustomerInfos.IsClubMember) as IsClubMember,
OutboundTimes,
OutboundTimes as enbale1
FROM T_CustomerInfos'
--return @whereStr
EXEC (@whereStr)运行时候出现问题请高手帮助一下
VS2005中的报错
System.Data.SqlClient.SqlException: 列名 'CustomerID' 无效。
列名 'MemberNumber' 无效。
列名 'FatherName' 无效。
列名 'BabyName' 无效。
列名 'BabySex' 无效。
列名 'DateOfBirth' 无效。
列名 'ContactPhoneNumber' 无效。
无法绑定由多个部分组成的标识符 "T_CustomerInfos.ProvincesID"。
无法绑定由多个部分组成的标识符 通常是用“.”来连接的各个名称之间的关系有问题。楼主你的sql太长了,看着眼晕 。。