存储过程动态/泛型 where [英] Stored Procedure dynamic / generic where

问题描述

我知道这个问题有几个答案，但我认为它们都不符合我的需求，所以我的问题来了

I know there a several Answers to this question but it i thing non of them fit my needs so here comes my question

基于此 SQL Fiddle 我将向您解释我想要实现的目标.

based on this SQL Fiddle i will explain you what i'm trying to achieve.

你怎么能看到它只是一个简单的人表，所以现在我想通过他的 id 来获取一个人，所以我必须写一个简单的查询 SELECT * FROM Persons WHERE PersonID =@PersonID;为了得到他，我现在可以为任何可能的组合执行此操作，并且我将为此表获得大量存储过程

How you can see it's just a simple table of persons so now i want to get a person by his id so i have to write a simple query SELECT * FROM Persons WHERE PersonID =@PersonID; to get him i can do this now for any possible combination and i will get tons of stored procedures just for this Table

所以我的问题是如何创建一个将所有这些功能合二为一的存储过程.这样我就可以在此处提供姓氏Schmidt"并获得所有 Schmidt.而不是向她询问纽约的所有保罗

So here is my question how do i create a stored procedure that combines all this functionality in one. So that i will be able to just give here the LastName 'Schmidt' and get all Schmidt's. than ask her for all Paul's in NewYork

让它更容易:

• 如果这个存储过程只能处理AND"就足够了
• 如果它选择所有列就足够了

推荐答案

试试这个:

SELECT * FROM Persons
WHERE PersonID  = ifnull(@PersonID,personID)
  AND LastName  = ifnull(@LastName,LastName)
  AND FirstName = ifnull(@FirstName, FirstName)
  AND Address   = ifnull(@Address,Address)
  AND City      = ifnull(@City,City)

并在 SQL Fiddle 上查看.

这篇关于存储过程动态/泛型 where的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！