SQL Server 2008 R2存储过程

I am facing a problem with my slow improvement in SQL Server 2008 R2 and here is the case:

我在SQL Server 2008 R2中的缓慢改进面临着一个问题,就是这样:

I want to create a stored procedure that accepts parameters, but I want it to work in a special way so that if I pass 2 parameters in search in a table patient for example where as if I pass to it one parameter I want it to search in a table school and so on.

我想创建一个接受参数的存储过程,但是我想让它以一种特殊的方式工作,这样如果我在一个表格患者中传递2个参数,例如我传递给它的一个参数,我希望它能够搜索在一个表学校等等。

Any help will be really appreciated

任何帮助将非常感激

Thank you

USE []
GO
/****** Object:  StoredProcedure [dbo].[proc_search_patient_ByID]    Script Date: 11/28/2014     07:47:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_search_patient_ByID]
( 
@PatID_pk int ,
@Cntid  smallint,
@FamID int
)
AS
SET NOCOUNT ON
select  cntid AS Center , PatFName AS FirstName   , PatMName AS MiddleName , PatLName AS LastName   , PatMother AS MotherName   ,PatDOB AS DOB
from tbl 
where Cntid=@Cntid  and PatId_PK = @PatID_pk

i want my procedure to work in this way if i supply 2 param but if i supply @FamID i want it to search in completely another table

我希望我的程序以这种方式工作如果我提供2个参数但是如果我提供@FamID我希望它在另一个表中搜索

1 个解决方案

#1

Try passing null value for unused parameters and inside the stored procedure put a check for nulls to switch tables.

尝试为未使用的参数传递空值,并在存储过程内部检查空值以切换表。

CREATE PROCEDURE [dbo].[proc_search_patient_ByID]
( 
@PatID_pk int ,
@Cntid  smallint,
@FamID int
)
AS
SET NOCOUNT ON
IF @Cntid IS NULL
BEGIN
   --Use select stmt of a table
END
ELSE
   --Use select stmt of another table
BEGIN
END

Likewise switch tables with appropriate parameters.

同样,切换表具有适当的参数。

#1