January 26, 2005 at 7:09 am
I have two question.
1) I have one query. I am passing two parameters one is @From_Date and other is @To_Date.
I would like to pass default value for these two parameter.
@To_Date = First day of current date. Like
@From_Date = First day of previous month. Like
How can I do that.
2) I have other query.
Select Firstname,Lastname,Address,Employee_Id
From Employee
Where Employee_Id = @ID
I am passing value for @ID parameter and it work fine. What I want a do if I don’t pass any think then I would like to run my query and give me all employee info.
I tried this @Id varchar(20)= Null
It give me nothing how can I get all results. If I don’t pass value for parameter.
January 26, 2005 at 7:43 am
Answer to 2)
Select Firstname,Lastname,Address,Employee_Id
From Employee
Where Employee_Id = ISNULL(@ID,Employee_Id)
or
Select Firstname,Lastname,Address,Employee_Id
From Employee
Where @ID IS NULL OR Employee_Id = @ID
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2005 at 12:51 am
Answer to 1)
select @dte = convert(datetime, convert(varchar, @dte - day(@dte)+1,112))
Sets the value of @dte to 1st of month and midnight. Day() gives # of days in month, date format 112 is yyyymmdd
Answer to 2)
If I understand you correctly the following should work:
if isnull(@id,0) = 0
begin
Select Firstname,Lastname,Address,Employee_Id
From Employee
end
else
begin
Select Firstname,Lastname,Address,Employee_Id
From Employee
Where Employee_Id = @ID
end
Regards, Ian Scott
January 27, 2005 at 8:21 am
2) If a parameter is passed as Null, and you want Null to mean a wildcard, the simplest query to accomplish this does not require any conditional T-SQL code:
Select Firstname,Lastname,Address,Employee_Id
From Employee Where Employee_ID = IsNull(@ID, Employee_Id)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply