SQL 动态条件查询语句的一种新尝试

SQL动态查询的存储过程，一般有两种写法 ，一种是排列结合,另一种是动态拼接SQL 发现新的写法，测试通过。 在此例中，如果条件ID为－1（前台是绑定到DROPDOWNLIST SELECT ALL的），条件不做任何限制。其他按照ID来查询。 CREATE PROCEDURE [dbo].[QuizQuestions_BY_AFFILIATION_SPORT_CATEGORY_Sel] @AFFILIATIONID INT, @G_SPORTID INT, @CATEGORYID INT AS -------------------------------------------------------------------------------- -- Name: QuizQuestions_BY_AFFILIATION_SPORT_CATEGORY_Sel -- Desc: Returns an entities from the QuizQuestions table. -- Auth: Achievo -------------------------------------------------------------------------------- BEGIN SELECT * FROM dbo.QuizQuestions AS Q WHERE ( @G_SPORTID=Q.SportID or @G_SPORTID=-1) AND ( @AFFILIATIONID =-1 or @AFFILIATIONID=Q.AffiliationID )AND ( @CATEGORYID =-1 or @CATEGORYID=Q.CategoryID) END 附件：表结构如下： USE [ArbiterQuiz] GO /****** Object: Table [dbo].[QuizQuestions] Script Date: 09/27/2006 14:08:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[QuizQuestions]( [QuestionID] [int] NOT NULL, [SportID] [int] NOT NULL, [AffiliationID] [int] NOT NULL, [CategoryID] [int] NOT NULL, [Title] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NOT NULL, [QuestionType] [int] NOT NULL, [FileName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [FileType] [int] NULL, [Align] [int] NULL, [Valign] [int] NULL, CONSTRAINT [PK_QuizQuestions] PRIMARY KEY CLUSTERED ( [QuestionID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO USE [ArbiterQuiz] GO ALTER TABLE [dbo].[QuizQuestions] WITH CHECK ADD CONSTRAINT [FK_QuizQuestions_Categories] FOREIGN KEY([CategoryID]) REFERENCES [dbo].[Categories] ([CategoryID])