sql-server - 如何根据存储过程过滤具有多个条件的记录
问题描述
我有一个学校管理系统。我刚刚创建了一个用于从数据库中检索学生列表的存储过程。如果我只是将 null 传递给所有参数，则存储过程可以正常工作，但是当我添加一些参数值时，我会收到此错误：
消息 50000，级别 15，状态 1，过程 GetStudentList，第 91 行
关键字“和”附近的语法不正确
这是我的存储过程代码：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetStudentList]
(@rollno VARCHAR(5) = NULL,
@name VARCHAR(25)= NULL,
@classid INT = NULL,
@sectionid INT = NULL)
AS
DECLARE @qry NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @rollcond NVARCHAR(5)
DECLARE @namecond NVARCHAR(25)
DECLARE @classcond NVARCHAR(10)
DECLARE @sectioncond NVARCHAR(10)
BEGIN
BEGIN TRY
SET @qry = N'select PK_PID as ID,
StudentName as Name
,Gender
,FatherName as Parents
,Address
,format(DOB,''dd/MM/yyyy'') as DOB
,EmailID
,AdmissionDate
,(select [ClassName] from dbo.classes where classes.PK_PID = s.Class) AS Class
,(select [SectionName] from dbo.sections where sections.PK_PID = s.Section) AS Section
,RollNo
,MobileNo
,Active
,StudentPhoto
from dbo.students s where 1=1'
IF @rollno is null
BEGIN
SET @rollcond = ''
END
ELSE
BEGIN
SET @rollcond = ' and s.RollNo like ''%'+@rollno+'%'' '
END
IF @name IS NULL
BEGIN
SET @namecond = ''
END
ELSE
BEGIN
SET @namecond = ' and s.StudentName like ''%'+@name+'%'' '
END
IF @classid IS NULL
BEGIN
SET @classcond = ''
END
ELSE
BEGIN
SET @classcond =' and s.Class = '+CONVERT(INT,@classid)+' '
END
IF @sectionid IS NULL
BEGIN
SET @sectioncond = ''
END
ELSE
BEGIN
SET @sectioncond =' and s.Section = '+CONVERT(INT,@sectionid)+' '
END
SET @sql = @qry + @rollcond + @namecond + @classcond + @sectioncond
--+' order by ID asc '
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState); -- State.
END CATCH;
END
这里出了什么问题？请大家帮帮我...
- - - - - - - - 更新 - - - - - - - - - - - -
USE [SM_00001]
GO
/****** Object: StoredProcedure [dbo].[GetStudentList] Script Date: 11/7/2019 8:56:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetStudentList]
(
@searchtext varchar(200) = null,
@classid int = null,
@sectionid int = null
)
AS
BEGIN
--new script
select [PK_PID] as ID,
[StudentName] as Name
,[Gender]
,[FatherName] as Parents
,[Address]
,format([DOB],'dd/MM/yyyy') as DOB
,[EmailID]
,[AdmissionDate]
,C.[ClassName] as Class
,Section.[SectionName] as Section
,[RollNo]
,[MobileNo]
,[Active]
,[StudentPhoto]
from [dbo].[students] as s
outer apply (select isnull([ClassName],'Default Class') from [dbo].[classes] where [classes].[PK_PID] = s.[Class]) as C
outer apply (select isnull([SectionName],'Default Section') from [dbo].[sections] where [sections].[PK_PID] = s.[Section]) as Section
where (@searchtext is null or s.[StudentName] LIKE '%'+@searchtext+'%'
or s.[Gender] LIKE '%'+@searchtext+'%'
or s.[FatherName] LIKE '%'+@searchtext+'%'
or s.[Address] LIKE '%'+@searchtext+'%'
or s.[DOB] LIKE '%'+@searchtext+'%'
or s.[Address] LIKE '%'+@searchtext+'%'
or s.[MobileNo] LIKE '%'+@searchtext+'%'
or s.[EmailID] LIKE '%'+@searchtext+'%')
and (@classid is null or s.[Class] = CONVERT(INT,@classid))
and (@sectionid is null or s.[Section] = CONVERT(INT,@sectionid))
--old script
--select PK_PID as ID,
-- StudentName as Name
-- ,Gender
-- ,FatherName as Parents
-- ,Address
-- ,format(DOB,'dd/MM/yyyy') as DOB
-- ,EmailID
-- ,AdmissionDate
-- ,(select [ClassName] from dbo.classes where classes.PK_PID = s.Class) AS Class
-- ,(select [SectionName] from dbo.sections where sections.PK_PID = s.Section) AS Section
-- ,RollNo
-- ,MobileNo
-- ,Active
-- ,StudentPhoto
--from dbo.students s order by ID asc
END
我只是在这里得到 sql 错误：
Msg 8155, Level 16, State 2, Procedure GetStudentList, Line 36
No column name was specified for column 1 of 'C'.
Msg 8155, Level 16, State 2, Procedure GetStudentList, Line 38
No column name was specified for column 1 of 'Section'.
Msg 207, Level 16, State 1, Procedure GetStudentList, Line 27
Invalid column name 'ClassName'.
Msg 207, Level 16, State 1, Procedure GetStudentList, Line 28
Invalid column name 'SectionName'.
------------------------------------更新2------------- -
CREATE TABLE [dbo].[students](
[PK_PID] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [varchar](25) NULL,
[Gender] [varchar](10) NULL,
[FatherName] [varchar](25) NULL,
[MotherName] [varchar](25) NULL,
[DOB] [datetime] NULL,
[Religion] [varchar](15) NULL,
[BloodGroup] [varchar](5) NULL,
[FatherOccupation] [varchar](25) NULL,
[EmailID] [varchar](150) NULL,
[AdmissionDate] [datetime] NULL,
[AdmissionID] [varchar](25) NULL,
[Class] [int] NULL,
[Section] [int] NULL,
[RollNo] [varchar](5) NULL,
[Address] [varchar](200) NULL,
[BIO] [varchar](200) NULL,
[MobileNo] [varchar](15) NULL,
[PhoneNo] [varchar](15) NULL,
[Active] [bit] NULL,
[Entry_By] [int] NULL,
[Enty_date] [smalldatetime] NULL CONSTRAINT [DF_students_Enty_date] DEFAULT (getdate()),
[StudentPhoto] [image] NULL,
CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED
(
[PK_PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[classes](
[PK_PID] [int] IDENTITY(1,1) NOT NULL,
[ClassName] [varchar](25) NULL,
[Entry_By] [int] NULL,
[Entry_Date] [smalldatetime] NULL CONSTRAINT [DF_classes_Entry_Date] DEFAULT (getdate()),
CONSTRAINT [PK_classes] PRIMARY KEY CLUSTERED
(
[PK_PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[sections](
[PK_PID] [int] IDENTITY(1,1) NOT NULL,
[SectionName] [varchar](10) NULL,
[Entry_By] [int] NULL,
[Entry_Date] [smalldatetime] NULL CONSTRAINT [DF_sections_Entry_Date] DEFAULT (getdate()),
CONSTRAINT [PK_sections] PRIMARY KEY CLUSTERED
(
[PK_PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
解决方案
这是编写查询的更好方法，它不使用动态 sql：
select PK_PID as ID,
StudentName as Name
,Gender
,FatherName as Parents
,Address
,format(DOB,'dd/MM/yyyy') as DOB
,EmailID
,AdmissionDate
,(select [ClassName] from dbo.classes where classes.PK_PID = s.Class) AS Class
,(select [SectionName] from dbo.sections where sections.PK_PID = s.Section) AS Section
,RollNo
,MobileNo
,Active
,StudentPhoto
from dbo.students s
where (@rollno is null or s.RollNo like '%'+@rollno+'%' )
and (@name is null or s.StudentName like '%'+@name+'%')
and (@classid is null or s.Class = CONVERT(INT,@classid))
and (@sectionid is null or s.Section = CONVERT(INT,@sectionid))