Stored Procedures make a query by one or two datad
I am trying to write a Stored Procedures in sql server 2008. I need to get user (all the columns in dbo.UserDetails) by UserId (the user id comes from another Table (dbo.UserSubjects)) and UserName (that comes from dbo.UserDetails). i will always have UserID but not always have UserName. So now for my question how can I write a Stored Procedures that if the UserName Is empty the query will search only by UserId and if UserName is not empty the query will search with UserId and UserName So far I have written: GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUserBySubjectID](@ID int = NULL, @Name string) AS SET NOCOUNT ON BEGIN TRY IF @ID IS NULL RAISERROR 400001 'User with given Subject ID does not exist' ELSE BEGIN SELECT UD.* FROM dbo.UserDetails UD , dbo.UserSubjects US WHERE US.UserID = UD.UserID and US.SubjectID = @ID and UD.UserName = @Name IF @@ROWCOUNT = 0 RAISERROR 400001 'User with given ID does not exist' END RETURN 0 END TRY BEGIN CATCH EXEC dbo.GetErrorInfo RETURN 1 END CATCH
There is a very good article about handling optional search terms using dynamic sql, by Erland Sommarskog at However a simple solution for you could be to do something like SELECT UD.* FROM dbo.UserDetails UD join dbo.UserSubjects US on US.UserID = UD.UserID WHERE US.SubjectID = @ID and (UD.UserName = @Name or @Name is null) Note how I have changed the join to use the standard syntax with a join condition, rather than have that in the where clause. The where clause now has a filter that returns true if the `@Name` matches `UD.Username` or `@Name` is null. You also should explicitly list the columns from the `UserDetails` table rather than use `select *`.