Hi,
I want to do something like this is my SP. If ' ' emplty string parameters are sent, I want the SP to use certain default values and use them in the query. code below is a rough idea..
CREATE PROCEDURE CabsSchedule_ViewLatest
(
@.SiteCode smallint,
@.YearMonth int
)
AS
IF @.YearMonth = ' ' OR @.SiteCode = ' '
BEGIN
@.YearMonth = DateTime.Now()
@.SiteCode = 'GN'
END SELECT JulianDate, CalendarDay, BillPeriod, WorkDay,CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO
CREATE PROCEDURE CabsSchedule_ViewLatest
(
@.SiteCode smallint = NULL,
@.YearMonth int = NULL
)
ASSET NOCOUNT ON
IF @.YearMonth IS NULL OR @.SiteCode IS NULL
BEGIN
SET @.YearMonth = Getdate()
SET @.SiteCode = 'GN'
END
SELECT...
SET NOCOUNT OFF
hth|||You have numeric parameters, so checking for ' ' is not meaningful. Try using default values for the SP:
CREATE PROCEDURE CabsSchedule_ViewLatest(
@.SiteCode smallint = -1,
@.YearMonth int = -1
)
This presumes -1 is a reasonable default (probably not)|||Hi Dinakar,
Thanks for the reply. I want to assign the latest YearMonth available in the my table to the @.YearMonth field. The format of YearMonth would be 200408 representing Year=2004 and Month=08 respectively, could this formatting be done using T-SQL functions??
Thanks,|||yeah there are some datefunctions available in SQL Server. check out BOL. in your case you wud prbly need Month(Getdate()).
hth|||REPLACE(CONVERT(nvarchar(10),DATEPART(yy,GetDate())) + Str(DATEPART(mm,GetDate()),2),' ','0')
You could do it more easily using Month() and Year() if you always want to use todays date.|||Douglas and Dinakar,
Thanks for the replies but, What I'm really looking for is the the latest YearMonth value available in the Table field YearMonth. I need this b'cos I need to extract all the rows corresponding to the YearMonth field as all the rows in a particular schedule could be classified by this field. What this means is for the Year 2004 and Month 08 I've got the number of rows corresponding to the number of working days in month represented by 08.
So, what I need is to pull all the rows with latest YearMonth field. Any ideas on how to pull the latest group of rows classified by the YearMonth field??
Thanks|||SELECT * FROM mytable WHERE YearMonth=(SELECT MAX(YearMonth) FROM myTable)|||Thanks for the reply Doug. I'm wiht another problem now. What I'm trying to do is to pass values from a SP as alias. ie. if I come across a value -1 in one of my rows in Column Called WorkDay, Then I want to pass string 'HOLIDAY' instead. I've been playing around with the CAST and CASE statements in my SP but couldn't make them to work. Any help with this??
The check syntax throws an error saying that the syntax is incorrect near '='.
Thanks,
CREATE PROCEDURE CabsSchedule_ViewLatest
(
@.SiteCode smallint = 0,
@.YearMonth int = NULL
)
AS
IF @.YearMonth IS NULL OR @.YearMonth = 0
BEGIN
SET @.YearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
ENDSELECT CAST(BillPeriod AS VARCHAR(7)) AS NewBillPeriod=
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE BillPeriod
END,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO
|||What is NewBillPeriod?
SELECT
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE BillPeriod
END as SomeOtherFieldName,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
BTW, What you are doing (overloading values to mean something "special" other than what they normally mean) is a bad thing...|||Thanks Doug,
But I really neeed to use this for storing optimal data in the table. I mean just to store just 2 different kinds of strings that too which can occur only a cpl of times, I thought of having alias values. Yes it does mean that a value being stored into the table means something special, but I thought that I'm better of storing special values instead of storing strings which have few occurances.
Also, before getting to your solution, I tried this and got it working but was having a few problems accessing it from my program. The error said that a string could not be implicitly converted into a smallint. Why is it saying that??
Thanks,|||It is saying that because you are using a string where the system expects a smallint. If you have a smallint field, it cannot store "FRED" or even "123", but can store 123. A string is a string, and a smallint is a smallint, and if you are converting from one to the other, you need to do it explicitly, using a cast.|||Doug,
I had tried using CAST on the fields in which I wanted to have alias values. I did domething like this but it didn't work and was saying that there was some incorrect syntax at the '=' on the CAST line in the below code. Could this be corrected to get what I'm looking for?? Also, the error says that there is not function called VARCHAR, but this is not a function.
What is going wrong here?
CREATE PROCEDURE CabsSchedule_ViewSchedule
(
@.SiteCode smallint = 0,
@.YearMonth int = NULL
)
AS
IF @.YearMonth IS NULL OR @.YearMonth = 0
BEGIN
SET @.YearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
ENDSELECT CAST(BillPeriod AS VARCHAR(7)) =
CASE
WHEN BillPeriod = '32' THEN 'NB'
WHEN BillPeriod = '33' THEN 'Holiday'
ELSE BillPeriod
END,
CAST(WorkDay AS VARCHAR(7))=
CASE
WHEN WorkDay = '-1' THEN ''
WHEN WorkDay = '0' THEN 'Holiday'
ELSE WorkDay
END,
JulianDate, CalendarDay, CalDayBillRcvd, Remarks
FROMCabsSchedule
WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
GO
Thanks|||In a previous post, I gave an example of how to do what you wish (I added a CONVERT call here, since that might be required, depending upon your data):
SELECT
CASE NewBillBeriod
WHEN 32 THEN 'NB'
WHEN 33 THEN 'HOLIDAY'
ELSE CONVERT(nvarchar(20),BillPeriod)
END as SomeOtherFieldName,
JulianDate, CalendarDay, WorkDay, CalDayBillRcvd, Remarks
FROM CabsSchedule WHERE YearMonth = @.YearMonth AND SiteCode = @.SiteCode
The syntax you are continuing to try to use will NEVER work.|||Thanks Doug,
The above change in my SP worked, but I was wondering why it wouldn't work if I CAST the BillPeriod in the CASE statement itself. Since, I CAST it to VarChar, the options for assignment it would have would still be the same and I did not care about CASTING the field BillPeriod in the ELSE statement (as you did) as smallint to varchar and Vice versa conversion needs no explicit CASTING(From what I read in BOL). What am I missing in my learning here?
Thanks again!
