存储过程无法正常工作 [英] Stored procedure not working properly
本文介绍了存储过程无法正常工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好，
我正在使用Windows Form Application，我需要在StoredProcedure下方使用搜索选民列表。
我有以下StoredProcedure
USE [VMS]
GO
/ * *****对象：StoredProcedure [dbo]。[GetVotersList]脚本日期：11/13/2013 16:13:11 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo]。[GetVotersList]
@ language nvarchar （ 255 ），
@ firstName nvarchar （ 255 ），
@ middleName nvarchar （ 255 ），
@lastNAme nvarchar （ 255 ），
@ SrNo int ，
@ sex nvarchar （ 10 ），
@minAge int ，
@ maxAge int ，
@ vCardId nvarchar （ 50 ），
@ address nvarchar （ 255 ），
@ yadiNo nvarchar （ 255 ），
@ mLanguage nvarchar （ 50 ），
@ Religion nvarchar （ 50 ），
@Castel nvarchar （< span class =code-digit> 50 ）
as
开始
如果（ @ language = ' English'）
开始
if （ @ sex = ' A'）
开始
选择 SrNo as ' Serial＃'，eLastName + ' ' + eFirstName + ' ' + eMiddleName as ' 名称，性别，年龄，
VCardId，GharNo，eAddress as ' 地址'，YadiNo，mobileNo as ' 移动号码，
来自投票
where eFirstName like @ firstName + ' ％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和年龄> @ minAge 和 age< @ maxAge 和
VCardId like @ vCardId + ' ％' < span class =code-keyword>和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
- 获取男性记录的数量
选择 count（*）来自 Voter
其中 eFirstName 喜欢 @ firstName + ' ％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和年龄> @ minAge 和 age< @ maxAge 和
VCardId like @ vCardId + ' ％' < span class =code-keyword>和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
和 sex = ' M'
end
< span class =code-keyword> else
begin
选择 SrNo as ' Serial＃'，eLastName + ' ' + eFirstName + ' < span class =code-string>' + eMiddleName as ' 名称'，性别，
年龄，VCardId，GharNo，eAddress as ' 地址'，YadiNo，mobileNo as ' Mobile No'，
emailId as ' 电子邮件ID'
来自选民
其中 eFirstName 喜欢 @ firstName + ' ％' 和 emiddleName like @ middleName + ' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和年龄> @ minAge 和 age< @ maxAge 和 sex = @ sex < span class =code-keyword>和
VCardId 喜欢 @ vCardId + ' ％' 和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
- 获取男性记录的数量
选择 count（*）来自选民
其中 eFirstName 喜欢 @ firstName + ' ％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和年龄> @ minAge 和 age< @ maxAge 和 sex = @ sex < span class =code-keyword>和
VCardId 喜欢 @ vCardId + ' ％' 和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
和 sex = ' M'
end
end
else - 语言是MARRATHI
开始
如果（ @sex = ' A'）
开始
选择 SrNo 为 ' Serial＃'，mLastName + ' ' + mFirstName + ' ' + mMiddleName as ' 姓名'，性别，
年龄， VCardId，GharNo，mAddress as ' Address' ，YadiNo，mobileNo as ' Mobile No '，
emailId as ' 电子邮件ID'
来自选民
其中 eFirstName 喜欢 @ firstName + ' ％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和 age> @ minAge 和 age< @ maxAge 和
VCardId like @ vCardId + ' ％' < span class =code-keyword>和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
- - 获取男性记录数
选择 count（*）来自 Voter
其中 eFirstName 喜欢 @firs tName + ' ％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和 age> @ minAge 和 age< @ maxAge 和
VCardId like @ vCardId + ' ％' < span class =code-keyword>和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
和 sex = ' M'
end
< span class =code-keyword> else
begin
选择 SrNo as ' Serial＃'，mLastName + ' ' + mFirstName + ' ' + mMiddleName as ' 名称'，性别，年龄，VCardId，GharNo，mAddress as ' 地址'，YadiNo，mobileNo as ' Mobile No'，emailId as ' 电子邮件ID' 来自选民
其中 eFirstName 喜欢 @ firstName + ' ％' 和 emiddleName 喜欢 @ middleName + < span class =code-string>' ％' 和
eLastName 喜欢 @ lastName + ' ％' 和 age> @ minAge 和 age< @ maxAge 和 sex = @ sex < span class =code-keyword>和
VCardId 喜欢 @ vCardId + ' ％' 和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
和 sex = ' M'
- 获取男性记录的数量
选择 count（*）来自选民
其中 eFirstName 喜欢 @ firstName + ' ％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName like @ lastName + ' ％' 和年龄> @ minAge 和 age< @ maxAge 和 sex = @ sex < span class =code-keyword>和
VCardId 喜欢 @ vCardId + ' ％' 和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
和 sex = ' M'
end
end
结束
这个工作正常（给我的选民列表与搜索查询完全匹配喜欢肯定的名字，名字等）但是当我添加一些更多的功能给它错误的输出。附加功能代码如下
首先我只搜索以下数值
@language nvarchar（255 ），
@firstName nvarchar（255），
@middleName nvarchar（255），
@lastNAme nvarchar（255），
@SrNo int，
@sex nvarchar（10），
@minAge int，
@maxAge int，
@vCardId nvarchar（50），
@address nvarchar（255），
@yadiNo nvarchar（255），
现在我想添加三个值来搜索它们是
@mLanguage nvarchar（50），
@Religion nvarchar（50 ），
@Caste nvarchar（50）
我编码为先前编码的值，但它只给我前两行。< br $> b $ b
请告诉我这里有什么问题。
对不完整的信息，很抱歉。希望现在有点清楚。
提前谢谢。
解决方案
我想问题是，你的计数查询中有@Caste问题。< br $>
- 获取男性记录数
选择 count（*）来自 Voter
其中 eFirstName 喜欢 @ firstName + ' < span class =code-string>％' 和 emiddleName 喜欢 @ middleName + ' ％' 和
eLastName < span class =code-keyword> like @ lastName + ' ％' 和年龄> @ minAge 和 age< @ maxAge 和 sex = @ sex < span class =code-keyword>和
VCardId 喜欢 @ vCardId + ' ％' 和 eAddress 喜欢 ' ％' + @ address + ' ％' 和 yadiNo 喜欢 @ yadiNo + ' ％'
和 sex = ' M'
和 mLanguage 喜欢 @ mLanguage + ' ％' 和 eReligion 喜欢 @ Religion + ' ％'
< span class =code-keyword>和 eCaste 喜欢 @Castel
它应该是 -
和 eCaste 喜欢 ' ％' + @Caste + ' ％'
Hi All,
I am working on a Windows Form Application where I need to use below StoredProcedure for search voter list.
I have below StoredProcedure
USE [VMS]
GO
/****** Object: StoredProcedure [dbo].[GetVotersList] Script Date: 11/13/2013 16:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetVotersList]
@language nvarchar(255),
@firstName nvarchar(255),
@middleName nvarchar(255),
@lastNAme nvarchar(255),
@SrNo int,
@sex nvarchar(10),
@minAge int,
@maxAge int,
@vCardId nvarchar(50),
@address nvarchar(255),
@yadiNo nvarchar(255),
@mLanguage nvarchar(50),
@Religion nvarchar(50),
@Caste nvarchar(50)
as
begin
if(@language = 'English')
begin
if(@sex = 'A')
begin
Select SrNo as 'Serial #', eLastName + ' '+ eFirstName + ' '+ eMiddleName as 'Name', sex, age,
VCardId, GharNo, eAddress as 'Address', YadiNo, mobileNo as 'Mobile No',
from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
-- Get count of Male records
select count(*) from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
and sex='M'
end
else
begin
Select SrNo as 'Serial #', eLastName + ' '+ eFirstName + ' '+ eMiddleName as 'Name', sex,
age, VCardId, GharNo, eAddress as 'Address', YadiNo, mobileNo as 'Mobile No',
emailId as 'Email Id'
from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and sex = @sex and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
-- Get count of Male records
select count(*) from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and sex = @sex and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
and sex='M'
end
end
else --Language is MARRATHI
begin
if(@sex = 'A')
begin
Select SrNo as 'Serial #', mLastName + ' '+ mFirstName + ' '+ mMiddleName as 'Name', sex,
age, VCardId, GharNo, mAddress as 'Address', YadiNo, mobileNo as 'Mobile No',
emailId as 'Email Id'
from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
--- Get count of Male records
select count(*) from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
and sex='M'
end
else
begin
Select SrNo as 'Serial #', mLastName + ' '+ mFirstName + ' '+ mMiddleName as 'Name', sex, age, VCardId, GharNo, mAddress as 'Address', YadiNo, mobileNo as 'Mobile No', emailId as 'Email Id' from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and sex = @sex and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
and sex='M'
-- Get count of Male records
select count(*) from Voter
where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and
eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and sex = @sex and
VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%'
and sex='M'
end
end
end
this works fine (gives me list of voter exactly match the search query like sure name, first name etc.)but when I add some more functionality to it gives wrong output. Additional functionality code is below
Firstly I am searching for below values only
@language nvarchar(255),
@firstName nvarchar(255),
@middleName nvarchar(255),
@lastNAme nvarchar(255),
@SrNo int,
@sex nvarchar(10),
@minAge int,
@maxAge int,
@vCardId nvarchar(50),
@address nvarchar(255),
@yadiNo nvarchar(255),
and now I want to add three more values to search they are
@mLanguage nvarchar(50),
@Religion nvarchar(50),
@Caste nvarchar(50)
I have coded as values coded earlier but it gives me only first 2 rows only.
Please advise me what's wrong here.
Sorry for incomplete info. Hope it's little bit clear now.
Thanks in advance.
解决方案
I guess, issue is with @Caste in your count queries.
-- Get count of Male records select count(*) from Voter where eFirstName like @firstName+'%' and emiddleName like @middleName+'%' and eLastName like @lastName+ '%' and age > @minAge and age < @maxAge and sex = @sex and VCardId like @vCardId +'%' and eAddress like '%'+@address+'%' and yadiNo like @yadiNo +'%' and sex='M' and mLanguage like @mLanguage+'%' and eReligion like @Religion+'%' and eCaste like @Caste
It should be -
and eCaste like '%'+@Caste+'%'
这篇关于存储过程无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
