how to run this proc in SQL Analyzer?

hi, guys

I have a stored procedure, like that:

CREATE PROCEDURE [dbo].[ViewTitles]
@.UID int,
@.DateStart datetime,
@.DateEnd datetime,
........

When I run this query in SQL Analyzer like this:
ViewTitles 6165, '2006-01-29 10:00:00', '2006-02-29 10:00:00'

It alwasy shows:
Error converting data type varchar to datetime.

If I run it in code, asp.net, or report, no problem at all.
What is wrong with that?

Thanks.

What is the dateformat of the session when logged in through ISQLW? You can verify this by looking at output of DBCC USEROPTIONS. Look for set option "dateformat". In addition to this, you should one of the ISO 8601 formats for datetime literals that should be interpreted correctly irrespective of the language or dateformat settings. Specify the value instead like '2006-01-29T10:00:00'.

How To Return a Static Value?

I have the following stored procedure...

CREATE Procedure UserGetInfo2
(
@.UserID int, @.SystemTimePeriodID int
)
As
set nocount on
SELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccounted
FROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserID
WHERE (users.userID= @.UserID) AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)
return
GO

The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.

Obviously, I need to wrap this in an IF...EXISTS

IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @.SystemTimePeriodID) AND (UserID = @.UserID))

(the SELECT above, since that's what needs to come back if the data exists)

ELSE

Do the same select but put in a static value for BudgetCode, like '0000'

GO

How could I do the part where the IF...EXISTS fails?

I'm... not sure I can use RETURNS, since it feeds into this recordset:

rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")

and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>

Hi friend, I dont know if I understand you in the right way. Try this:

CREATE Procedure UserGetInfo2 (@.UserIDint, @.SystemTimePeriodIDint )As set nocount onreturnGOIFNOT EXISTS (SELECT UserTimePeriodIDFROM UserTimePeriodWHERE (SystemTimePeriodID = @.SystemTimePeriodID)AND (UserID = @.UserID))SET @.SystemTimePeriodID = 0;SELECT Users.UserIdas UserID, Users.UserNameas UserName, Users.RealNameas RealName, UserTimePeriod.BudgetCodeas BudgetCode, UserTimePeriod.SystemTimePeriodIDas SystemTimePeriodID, Users.Passwordas Password, Users.SSNas SSN, Users.Locationas Location, Users.ScheduleTypeas ScheduleType, Users.EmployeeTypeas EmployeeType, Users.TimeAccountedas TimeAccountedFROM UsersINNERJOIN UserTimePeriodON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @.UserID)AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)

Hope this helps.

|||No, I would need to set the outgoing BudgetCode to something, not the incoming SystemTimePeriodID.|||

It's as easy as:

Select..... , '0000' as BudgetCode, ...

|||I knew I had seen that done somewhere. Thank you for clearing up my brain's log jam.

how to retrieve the largest value from a column?

lets say a column using int as datatype

for eg:

the current method i used to retrieve the largest value is by...

select TOP 1 ...from....order by desc

so for eg:
let say the column contains
10
60
40
25
40

i will only retrieve 60. are there any loophole in the above method?Why not try this:

SELECT MAX(<field>) as MaxValue <from <TABLE> where <CLAUSE>|||Originally posted by Bascy
Why not try this:

SELECT MAX(<field>) as MaxValue <from <TABLE> where <CLAUSE>

Thanks. :)|||TOp n gives u the number of rows.
Max gives u the maximum value in the column|||Originally posted by cyrus
TOp n gives u the number of rows.
Max gives u the maximum value in the column

That is not quite correct...

TOP n gives you the first n rows of the resultset, so TOP 1 is the same as MAX as long as the order is on the same field.|||Originally posted by Bascy
That is not quite correct...

TOP n gives you the first n rows of the resultset, so TOP 1 is the same as MAX as long as the order is on the same field.

i am saying the same thing without order by