I'm trying to assign a default value to an int type column in a stored
procedure that will display all records if the user does not select an item
from a drop down list. I have tried ">0" and "%" but I get an error. How do
I select all records. Thanks for any .

Here is one way:

CREATE PROCEDURE Foo
@selected_item INT = 0
AS
SELECT <columns>
FROM Items
WHERE item_id = @selected_item
OR @selected_item = 0;

--
Plamen Ratchev
http://www.SQLStudio.com

The ddl would return a valid ID if the user makes a selection or if nothing
is selected from the ddl I want the sp to reurn all records not those with
null or "0".

"John Bell" wrote:

>
> "AkAlan" <AkAlan@discussions.microsoft.com> wrote in message
> news:82B26BE7-9AD7-41B0-A3EA-61B27EABB71A@microsoft.com...
> > I'm trying to assign a default value to an int type column in a stored
> > procedure that will display all records if the user does not select an
> > item
> > from a drop down list. I have tried ">0" and "%" but I get an error. How
> > do
> > I select all records. Thanks for any .
>
> Hi
>
> You would need to post your DDL and expected results to make sure that
> people understand what you want.
>
> If you have a parameter to the stored procedure that is optional, the value
> of that parameter is probably NULL
>
> e.g.
>
> CREATE PROCEDURE usp_GetEmployee @empid int = NULL
> AS
> SELECT [EmployeeID]
> ,[NationalIDNumber]
> ,[ContactID]
> ,[LoginID]
> ,[ManagerID]
> ,[Title]
> ,[BirthDate]
> ,[MaritalStatus]
> ,[Gender]
> ,[HireDate]
> ,[SalariedFlag]
> ,[VacationHours]
> ,[SickLeaveHours]
> ,[CurrentFlag]
> ,[rowguid]
> ,[ModifiedDate]
> FROM [AdventureWorks].[HumanResources].[Employee]
> WHERE [EmployeeID] = @empid OR @empid IS NULL
>
>
> EXEC usp_GetEmployee 1
>
> EXEC usp_GetEmployee NULL
>
> EXEC usp_GetEmployee
>
> John
>

Here is the solution I was looking for.

create procedure up_GetData @Id int =null
as
select * from MyTable
where id = isnull(@Id,id)




"AkAlan" wrote:

> The ddl would return a valid ID if the user makes a selection or if nothing
> is selected from the ddl I want the sp to reurn all records not those with
> null or "0".
>
> "John Bell" wrote:
>
> >
> > "AkAlan" <AkAlan@discussions.microsoft.com> wrote in message
> > news:82B26BE7-9AD7-41B0-A3EA-61B27EABB71A@microsoft.com...
> > > I'm trying to assign a default value to an int type column in a stored
> > > procedure that will display all records if the user does not select an
> > > item
> > > from a drop down list. I have tried ">0" and "%" but I get an error. How
> > > do
> > > I select all records. Thanks for any .
> >
> > Hi
> >
> > You would need to post your DDL and expected results to make sure that
> > people understand what you want.
> >
> > If you have a parameter to the stored procedure that is optional, the value
> > of that parameter is probably NULL
> >
> > e.g.
> >
> > CREATE PROCEDURE usp_GetEmployee @empid int = NULL
> > AS
> > SELECT [EmployeeID]
> > ,[NationalIDNumber]
> > ,[ContactID]
> > ,[LoginID]
> > ,[ManagerID]
> > ,[Title]
> > ,[BirthDate]
> > ,[MaritalStatus]
> > ,[Gender]
> > ,[HireDate]
> > ,[SalariedFlag]
> > ,[VacationHours]
> > ,[SickLeaveHours]
> > ,[CurrentFlag]
> > ,[rowguid]
> > ,[ModifiedDate]
> > FROM [AdventureWorks].[HumanResources].[Employee]
> > WHERE [EmployeeID] = @empid OR @empid IS NULL
> >
> >
> > EXEC usp_GetEmployee 1
> >
> > EXEC usp_GetEmployee NULL
> >
> > EXEC usp_GetEmployee
> >
> > John
> >