想要在存储过程中进行修改 [英] modification want in storedprocedure

问题描述

我采用5个列表框进行多项选择，我想为我的数据多项选择更改逗号分隔值

我的存储过程中的混乱之处是我的存储过程

i take 5 listbox for multiple selection and i want to change comma seprated value for my multiple selection of my data

what is the chane in my stored procedure here is my stored procedure

ALTER PROCEDURE data.Search

(
,

@MartialStatus varchar(20),
@Education    varchar(50),
@Country    varchar(50),
@State    varchar(50),
@ResidentStatus    varchar(50),
@ReligionId int,
@CasteId int,
@EatingHabits    varchar(50),
@SmokingHabits    varchar(50),
@DrinkHabits    varchar(50),
@Complextion    varchar(50),
@BodyType    varchar(50)
)

AS
    
    
    
    SELECT Registration.RegId, Registration.FirstName, Registration.LastName,Registration.ImageId, Registration.Age, Registration.Gender, Registration.Height, Registration.Weight,
                  ReligionMaster.ReligionName, Registration.Country, CasteMaster.CasteName, Registration.Education,SubCasteMaster.SubCasteName,Registration.Occupation
FROM     Registration INNER JOIN
                  ReligionMaster ON Registration.ReligionId = ReligionMaster.ReligionId INNER JOIN
                  CasteMaster ON Registration.CasteId = CasteMaster.CasteId
                  INNER JOIN
                  SubCasteMaster ON Registration.SubcasteId = SubCasteMaster.SubcasteId
WHERE  
                  (Registration.MartialStatus = @MartialStatus) OR
                  (Registration.Education = @Education) OR
                  (Registration.AnnualIncome = @AnnualIncome) OR
                  (Registration.Country = @Country) OR
                  (Registration.State = @State) OR
                  (Registration.ResidentStatus = @ResidentStatus) OR
                  (Registration.ReligionId = @ReligionId) OR
                  (Registration.EatingHabits = @EatingHabits) OR
                  (Registration.SmokingHabits = @SmokingHabits) OR
                  (Registration.DrinkHabits = @DrinkHabits) OR
                  (Registration.Complextion = @Complextion) OR
                  (Registration.BodyType = @BodyType)

推荐答案

以下函数用于拆分逗号分隔的值，您可以在存储过程中使用它.我认为这将对您的问题有所帮助.让我们知道更多信息...

创建功能[dbo].[FnSplit]
(
@Source NVARCHAR(4000)，
@SplitChar NCHAR(1)
)
返回@tbNames表
(
名称nvarchar(256)NOT NULL主键
)
AS
开始
从@tbNames删除

DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
宣告@Name nvarchar(256)

SET @Num = 0
SET @位置= 1
WHILE(@Pos< = LEN(@Source))
开始
选择@NextPos = CHARINDEX(@ SplitChar，@ Source，@ Pos)
IF(@NextPos = 0或@NextPos IS NULL)
SELECT @NextPos = LEN(@Source)+1
选择@Name = RTRIM(LTRIM(SUBSTRING(@Source，@Pos，@NextPos-@Pos)))
SELECT @Pos = @ NextPos + 1

插入@tbNames值(@Name)
SET @Num = @Num +1
结束
返回
END

The below function is used to split the comma seperated values and you can use it in your stored procedure. I think this will be a bit helpful for your problem. Let us know for further info...

CREATE FUNCTION [dbo].[FnSplit]
(
@Source NVARCHAR(4000),
@SplitChar NCHAR(1)
)
RETURNS @tbNames table
(
Name nvarchar(256) NOT NULL PRIMARY KEY
)
AS
BEGIN
DELETE FROM @tbNames

DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)

SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@Source))
BEGIN
SELECT @NextPos = CHARINDEX(@SplitChar, @Source, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@Source) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@Source, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END
RETURN
END

这篇关于想要在存储过程中进行修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！