Select By ID Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Select By ID Problem

Author

Topic

just.net
Starting Member

24 Posts

Posted - 2009-05-07 : 13:24:55

Hello,

I have this SP, that has a parameter @cityID, if the user will not choose a city I want to select all the cities, else only the city he choose (by ID), how can I do it?

ALTER PROCEDURE [dbo].[spReports_VaccinationsReport]
@cityID nvarchar(10) = NULL,
@startDate datetime = NULL,
@endDate datetime = NULL
AS

BEGIN

SET NOCOUNT ON;
SET @cityID = COALESCE (@cityID,'')
SET @startDate = COALESCE (@startDate,'01/01/1999')
SET @endDate = COALESCE (@endDate,'01/01/2099')

SELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDate,

FROM SomeView
WHERE City LIKE '%' + @cityID + '%' AND
CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDate
END

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 13:34:25

try replacing setting cityid with this,

SET @cityID = COALESCE (@cityID,'%')

just.net
Starting Member

24 Posts

Posted - 2009-05-07 : 14:25:23

the SP that i wrote is working, but what if
the user choose city with ID 1 for exmp. this SP will not
work, because the LIKE will bring also id 11, id 12....

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 14:39:24

from what I understood by your first post, you asked if city is not chosen (is NULL),, you'd want to return everything.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 14:42:37

from your 2nd post, may be what you need is ,,

ALTER PROCEDURE [dbo].[spReports_VaccinationsReport]
@cityID nvarchar(10) = NULL,
@startDate datetime = NULL,
@endDate datetime = NULL
AS

BEGIN

SET NOCOUNT ON;
SET @cityID = COALESCE (@cityID,'%')
SET @startDate = COALESCE (@startDate,'01/01/1999')
SET @endDate = COALESCE (@endDate,'01/01/2099')

IF EXISTS (SELECT 1 FROM SOMEVIEW WHERE CITY=@CITYID)
SELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDate

FROM SomeView
WHERE City = @cityID  and
CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDate

ELSE

SELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDate

FROM SomeView
WHERE City LIKE '%' + @cityID + '%' AND 
CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDate

END

just.net
Starting Member

24 Posts

Posted - 2009-05-07 : 15:06:58

this is what i thought i will do eventually, but like this:

IF(@cityID NOT NULL)
SELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDate
FROM SomeView
WHERE City = @cityID and
CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDate

ELSE

SELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDate
FROM SomeView
WHERE City LIKE '%' + @cityID + '%' AND
CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDate

but i thought there is maybe another way, more sophisticated one,
instead of writing the same command twice:
SELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDate
FROM SomeView

this command can be stored in a variable or something like that?
(sorry about my english, i am from israel - need more practice)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources