Miss out parameter

  • Thread starter Thread starter Gav
  • Start date Start date
G

Gav

I have a select statement with parameter

ie

SELECT * FROM USERS WHERE UserID = @UserID

If I do not supply @UserID I get an error, how can I set it up so that if I
don't supply @UserID it returns all of them?

Help would be much appreciated
Thanks
Gav
 
If you the UserID variable is optional, build the statement dynamically to
include or not the Where clause.

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com
 
Better to not build queries dynamically. Use the ISNULL function and set a
default value on the parameter in your stored procedure (presuming you're
using one).

CREATE PROCEDURE foo
(
@UserID int = NULL
)
AS
SELECT * FROM USERS WHERE UserID = ISNULL(@UserID, UserID)
 
Gav said:
I have a select statement with parameter

ie

SELECT * FROM USERS WHERE UserID = @UserID

If I do not supply @UserID I get an error, how can I set it up so that if
I
don't supply @UserID it returns all of them?

Help would be much appreciated
Thanks
Gav
Click to expand...

I usually do this as:

CREATE PROC usp_GetUser

@UserID bigint = null

AS


SELECT

u.*

FROM

Users u

WHERE

(
(@UserID IS NULL) OR
((@UserID IS NOT NULL) AND (u.UserID = @UserID))

)

GO
 
Back
Top