Get records for ID or all 1

[SatishPutcha]

Hello Everybody,

Recently I moved an ASP-Access system to ASP-SQL2k5 database. I am also now using Stored Procedures.

My problem is:
I have a table called tblLocations with LocationID(PKey int) and LocationName(nvarchar(100)).

Now when using a stored procedure I send a parameter LocationID to get the Location name for that ID.

However I want it such that if I don't send a LocationID then it should retrieve all records.

I am able to do the same for Location name using

SELECT LocationName FROM tblLocations WHERE LocationName= COALESCE(@LocationName, '%')

All help will be greatly helpful

~Satish

 

[bborissov]

--- Assuming that parameter passed is @ID

SELECT .......
  FROM .....
WHERE (@ID IS NULL OR LocationID = @Id)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SatishPutcha]

Thanks Boris.

This has been greatly helpful. Not knowing what to do I finally was toying with the idea of creating two SP: one with ID as parameter and another without.

But you have saved me so much time. Thanks again.

~Satish