建立Store Procedure
Source Code：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/****** Object: StoredProcedure [dbo].[spMember] Script Date: 2022/10/31 下午 04:10:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spMember]
-- Add the parameters for the stored procedure here
@UserID int,
@UserName varchar(50),
@UserEmail varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from Member where UserName = @UserName
or UserEmail = @UserEmail
END
GO
如果輸入” “ 或Null 回傳全部資料的方式
若不特別處理的話會Search “ “或Null
通常使用操作上如果不輸入數值,通常會希望將資料全部帶出來 Select的部分改成如下
1
2
3
4
FROM [dbo].[Member]
where
(@UserName IS NULL OR @UserName = [UserName] or LEN(LTRIM(@UserName)) = 0 ) and
(@UserEmail IS NULL OR @UserEmail = [UserEmail] or LEN(LTRIM(@UserEmail)) = 0 )
備註
如果想要繼續精進SQL，記得從這下面的Blog文章繼續學習