Miss out parameter

[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

[Carlos J. Quintero [.NET MVP]]

Re: Miss out parameter

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

"Gav" <spam@spam.co m> escribió en el mensaje
news:%23xGxG8TY FHA.2128@TK2MSF TNGP14.phx.gbl. ..[color=blue]
>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
>
>[/color]

[KH]

Re: Miss out parameter

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)


"Carlos J. Quintero [.NET MVP]" wrote:
[color=blue]
> 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
>
> "Gav" <spam@spam.co m> escribió en el mensaje
> news:%23xGxG8TY FHA.2128@TK2MSF TNGP14.phx.gbl. ..[color=green]
> >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
> >
> >[/color]
>
>
>[/color]

[Sgt. Sausage]

Re: Miss out parameter


"Gav" <spam@spam.co m> wrote in message
news:%23xGxG8TY FHA.2128@TK2MSF TNGP14.phx.gbl. ..[color=blue]
>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[/color]

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

[Gav]

Re: Miss out parameter

Is there anything I can do if I'm not using stored procedures?

"KH" <KH@discussions .microsoft.com> wrote in message
news:62F6AB72-7C55-4395-A1C8-DB139DD910BC@mi crosoft.com...[color=blue]
> 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)
>
>
> "Carlos J. Quintero [.NET MVP]" wrote:
>[color=green]
>> 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
>>
>> "Gav" <spam@spam.co m> escribió en el mensaje
>> news:%23xGxG8TY FHA.2128@TK2MSF TNGP14.phx.gbl. ..[color=darkred]
>> >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
>> >
>> >[/color]
>>
>>
>>[/color][/color]