Alternative notation for Stored procedure queries

Before I start documenting my recommendations, let me ask you a question-if you now have a user table Sysuser, which has IDs, UserName, DeptID, CreateDate, and so on, now you need to write a stored procedure to query the user's table information. In addition, the query will be based on one or more of the four fields listed above as criteria to query, what is your writing?

After you have come up with your writing, come and see how I write it.

CREATE PROCEDUREsysuser_gettable@Id                 INT=NULL,@UserName           NVARCHAR( -)=NULL,@DeptId             INT=NULL,@BeginCreateDate    DATETIME=NULL,@EndCreateDate      DATETIME=NULL asDeclare @strSQL   nvarchar( -) Declare @strWhere nvarchar( +)="'Set @strWhere=@strWhere        + ISNULL('and id=" "+LTRIM(@Id)+" '","')    + ISNULL('and UserName like"'%'+LTRIM(@UserName)+'%" ","')    + ISNULL('and DeptID =" "+LTRIM(@DeptId)+" '","')     + ISNULL('and createdate>=" "+LTRIM(@BeginCreateDate)+" '","')    + ISNULL('and createdate<=" "+LTRIM(@EndCreateDate)+" '","')IF LEN(@strWhere)>0     SET @strWhere = STUFF(@strWhere,1,4,'WHERE')SET @strSQL='SELECT * from Sysuser'+@strWhereEXEC(@strSQL)

I do not know your writing is not to use if to determine whether the arguments passed in is empty to join SQL, or whether the parameters are empty directly with like to query?

The above notation mainly uses the IsNull function, which is used to determine whether the passed in parameter is empty.

Personally, there are two benefits to this writing:

1, put the conditions of the stored procedures in one place to write, so use the whole stored procedures clearer, more elegant.

2, solve the stitching out of the SQL statement there are dispensable conditions, such as the conditions such as 1=1.

If you have a better way of writing, or if you think it's a disadvantage, I hope you can correct it, and hope that we can share some good things and grow together.

Alternative notation for Stored procedure queries