想要在存储过程中进行修改 [英] 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屋!
查看全文