question

yoni2012 avatar image
yoni2012 asked

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
sql-server-2008stored-procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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 *`.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What would happen if the underlying table changed? You add 4 more columns, will the calling application handle this different result set or will it break. You add 50 more columns, and need to pass all this extra data across the network when the app doesn't need it. You remove a column, and the stored procedure will still compile, but the app may still be expecting a column. By specifying the columns explicitly, you are defining the signature of the procedure and hopefully working to an interface specification, where only the data that is needed is used, and any breaking changes are easy to stop.
1 Like 1 ·
thank you for your answer, way using * (all) is lass good then explicitly list the columns?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.