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)
|
 |
|
|
|
|