Putting @Parameters in Stored Procedures

I am creating a stored Procedure and I am getting an error which relates toDATENAME.

SELECTCOUNT(*)AScalls,DATENAME(@.varDate, CALLSTARTTIME)AS 'Total Calls'

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS='1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <=@.EndDate

sql doesn't like: DATENAME( @.varDate, CallStartTime)
sql works fine if I change @.varDate into 'yy', 'mm', 'dd', or 'wk'
Since I do not want to make 5 unique Stored Proc just because of @.varDate....

Is there any way to work around this problem?

Well, you could do something like this:

DECLARE @.varDate VARCHAR(10)
SELECT @.varDate = 'mm'
SELECT
OrderDate,
CASE @.varDate
WHEN 'mm' THEN DATENAME(mm,OrderDate)
WHEN 'yy' THEN DATENAME(yy,OrderDate)
WHEN 'dw' THEN DATENAME(dw,OrderDate)
END -- added this as an EDIT to my post
FROM Orders

|||I tried,

ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.VarDate as char,
@.StartDate as char,
@.EndDate as char
)
AS
SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME) AS 'Year'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)
SQL wouldn't allow me to save. What did I do wrong?
|||Somehow when I coped and pasted my test code I left out an END, sorry about that!
Try it like this:

SELECT
COUNT(*) AS 'Calls',
CASE @.VarDate
When 'yy' Then DATEPART(yy, CALLSTARTTIME)ENDAS 'Year'

FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME IS NOT NULL) AND (CALLENDTIME IS NOT NULL)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)

Also, in the future, you need to be more specific than "SQL wouldn'tallow me to save". Exact error messages go a long way to gettingexact help.
|||thanks for the advice.
got another error:
ADO error: Column 'Call_LOG_MASTER.CALLSTARTTIME' is invalid in theselect list because it is not contained in either an aggregate functionor the GROUP BY clause.

SELECT
CASE @.varDate
when 'yy' Then DATEPART(yy, CALLSTARTTIME) END as 'Year',
COUNT(DATEPART(yy, CALLSTARTTIME)) AS 'Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY DATEPART(yy, CALLSTARTTIME)|||

I think I found the problem,

ALTER PROCEDUREdbo.GET_TOTALCALLS

(

@.varDateas varchar(255),

@.StartDateas datetime,

@.EndDateas datetime

)

AS

SELECT

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

END,

Count(*)as'Total Calls'

FROMCALL_LOG_MASTER

WHERE(COMMERCIALS = '1')AND(CALLSTARTTIME >= @.StartDate)AND(CALLENDTIME <= @.EndDate)

GROUP BY

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

End

ORDER BY

CASE@.varDate

When'Year'Then DATEPART(yy, CALLSTARTTIME)

When'Month'Then DATEPART(mm, CALLSTARTTIME)

When'Week'Then DATEPART(wk, CALLSTARTTIME)

When'Day'Then DATEPART(dd, CALLSTARTTIME)

when'Quarter'Then Datepart(qq, CALLSTARTTIME)

End

RETURN

However, the column can't be named this way. My new question is how do I renamed the datepart column by case?
Namely,
when @.varDate = 'Year', the column name should be 'Year',
when @.varDate = 'Month', the column name should be as 'Month', etc..

|||still stuck on this problem, could use some help if possible =)|||If that is your requirement, then I would scrap what you have and just do something like this:
ALTER PROCEDURE dbo.GET_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS
IF @.varDate = 'Year'
BEGIN
SELECT
DATEPART(yy, CALLSTARTTIME) AS Year,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(yy, CALLSTARTTIME)
ORDER BY 1
END
IF @.varDate = 'Month'
BEGIN
SELECT
DATEPART(mm, CALLSTARTTIME) AS Month,
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1')AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY DATEPART(mm, CALLSTARTTIME)
ORDER BY 1
END
-- etc
RETURN

|||yes, thank you. That will work fine