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 = NULLAS 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 @endDateEND |
|
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 ifthe user choose city with ID 1 for exmp. this SP will notwork, 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 = NULLASBEGINSET 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, BirthDateFROM SomeViewWHERE City = @cityID andCONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDateELSESELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDateFROM SomeViewWHERE City LIKE '%' + @cityID + '%' AND CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDateEND |
 |
|
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, BirthDateFROM SomeViewWHERE City = @cityID andCONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDateELSESELECT ClientID, FirstName + ' ' + LastName AS ClientName, CONVERT(VARCHAR,StartTime,103) AS VacDate, Street, City, ChipNo, BirthDateFROM SomeViewWHERE City LIKE '%' + @cityID + '%' AND CONVERT(VARCHAR,StartTime,1) BETWEEN @startDate AND @endDatebut 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, BirthDateFROM SomeViewthis command can be stored in a variable or something like that?(sorry about my english, i am from israel - need more practice) |
 |
|
|
|
|