April 14, 2008 at 6:50 am
I am having trouble getting a NULL parameter to work (See bold). The Code:
DECLARE @ConsultantID AS nVarChar(50)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @status nvarchar(100)
DECLARE @State nvarchar(2)
SET @StartDate = '01/01/2008'
SET @EndDate = '04/08/2008'
SET @status = 'All'
SET @State = 'MN'
IF @status = 'Active'
BEGIN
SET @status=1
END
ELSE
IF @status = 'Inactive'
BEGIN
Set @status = 0
END
ELSE
IF @status = 'All'
BEGIN
SET @status=NULL
END
IF @State = 'All'
Begin
Set @State = NULL
END
SET@StartDate = Convert(DateTime,Convert(nVarChar(10),@StartDate,101) + ' 00:00:00.000')
SET@EndDate = Convert(DateTime,Convert(nVarChar(10),@EndDate,101) + ' 23:59:59.997')
Select c.ConsultantID
,c.FirstName + ' ' + c.LastName as ConsultantName
,c.CurrentLevelXID
,c.BillToState
,c.BillToPhone
,c.SponsorXID
,MAX(c.NACDate) As NACDATE
,c.DeactivationDate
,c.Active
INTO #Temp FROM uvwConsultantDownline c
WHERE c.NACDate >= @StartDate AND c.NACDate <=@EndDATE
AND c.Active = @status OR (@Status IS NULL AND c.NACDate >= @StartDate AND c.NACDate <=@EndDATE)
GROUP BY
c.ConsultantID
,c.FirstName
,c.lastName
,c.CurrentLevelXID
,c.BillToState
,c.BillToPhone
,c.SponsorXID
,c.DeactivationDate
,c.Active
Select * from #Temp
WHERE @State=BillToState
OR (@State IS NULL)
Drop Table #Temp
The @State works fine when I pass in a State abbreviation ('MN') but if I set that @state parameter to 'All' which is actuallly setting it to NULL my recordset comes back empty when I should expect all states.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 14, 2008 at 8:19 am
@State is declared NVARCHAR(2)
Setting it to 'All' actually makes it 'Al' and I don't think you want the Alabama information.
Make it 3 characters.
April 14, 2008 at 8:22 am
Geez. Well it is Monday!!! Thanks a lot that worked.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 14, 2008 at 5:13 pm
Hey Art, as long as you are here, ...
I notice that you post a lot of code on the forums, which is great, however, it would help some of us a lot if you could use the code blocks. Or at least switch the font to "Courier New" for the SQL code. It makes it a lot easier for some of us to read. Like so:[font="Courier New"]
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @status nvarchar(100)
DECLARE @State nvarchar(2)
SET @StartDate = '01/01/2008'
SET @EndDate = '04/08/2008'
SET @status = 'All'
SET @State = 'MN'
IF @status = 'Active'
BEGIN
SET @status=1
END
ELSE
IF @status = 'Inactive'
BEGIN
Set @status = 0
END
ELSE
IF @status = 'All'
BEGIN
SET @status=NULL
END
IF @State = 'All'
Begin
Set @State = NULL
END
SET@StartDate = Convert(DateTime,Convert(nVarChar(10),@StartDate,101) + ' 00:00:00.000')
SET@EndDate = Convert(DateTime,Convert(nVarChar(10),@EndDate,101) + ' 23:59:59.997')
Select c.ConsultantID
,c.FirstName + ' ' + c.LastName as ConsultantName
,c.CurrentLevelXID
,c.BillToState
,c.BillToPhone
,c.SponsorXID
,MAX(c.NACDate) As NACDATE
,c.DeactivationDate
,c.Active
INTO #Temp FROM uvwConsultantDownline c
WHERE c.NACDate >= @StartDate AND c.NACDate <=@EndDATE
AND c.Active = @status OR (@Status IS NULL AND c.NACDate >= @StartDate AND c.NACDate <=@EndDATE)
GROUP BY
c.ConsultantID
,c.FirstName
,c.lastName
,c.CurrentLevelXID
,c.BillToState
,c.BillToPhone
,c.SponsorXID
,c.DeactivationDate
,c.Active
Select * from #Temp
WHERE @State=BillToState
OR (@State IS NULL)
Drop Table #Temp[/font]
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 15, 2008 at 9:22 am
Not a problem. I will start doing that in the future.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply