Database

vimal_yet wrote:

you can do it in two seperate quires....

seemamltn wrote:

want to pass all the rows one by
one to the parameter @starttime. I don't know how to do it.

CREATE FUNCTION dbo.TimeDifference(@startTime DATETIME, @endTime DATETIME)
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @Diff int;
    DECLARE @Day int;
    DECLARE @Hour int;
    DECLARE @Minute int;

    SELECT @Diff = DATEDIFF(minute, @startTime, @endTime);
    SELECT @Day = @Diff / 3600;
    SELECT @Hour = (@Diff - (@Day*3600)) / 60;
    SELECT @Minute = @Diff % 60

    RETURN CAST( @Day AS VARCHAR(5)) + 'd ' +
           CAST( @Hour AS VARCHAR(5)) + 'h ' +
           CAST( @Minute AS VARCHAR(5)) + 'm'; 
    
END
GO
 
CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
    SELECT 
        tck.tcktreceived, 
        tck.ticketid, 
        tckmsg.tcktmessage, 
        dbo.TimeDifference(tct.tckreceived, GETDATE())
    FROM tbtickets AS tck 
    INNER JOIN tbticketsmessages AS tckmsg ON tck.ticketid = tckmsg.ticketid 
END
GO

SqlConnection conn = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "MyStoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MyParameter", theValueOfTheParameter);
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
    // Use the Get...() methods to get at the data.
    // Each loop retrieves one row from the result set.
}

CREATE PROCEDURE dbo.MyStoredProcedureName
@MyParameter INT
BEGIN
    SELECT * FROM MyTable WHERE SomeColumn = @MyParameter
END

DECLARE @Day INT 
DECLARE @Hour INT 
DECLARE @Minute INT 
DECLARE @Start_Date DateTime 
DECLARE @End_Date DateTime
DECLARE @Diff VARCHAR(20)
SET @Start_Date = '12 jun 2007 12:36' 
SET @End_Date = '14 jun 2007 12:35' 
SET @Day = DATEDIFF(minute, @Start_Date, @End_Date) / (24 * 60)
SET @Hour = (DATEDIFF(minute, @Start_Date, @End_Date) / 60) % 24
SET @Minute = DATEDIFF(minute, @Start_Date, @End_Date) % 60
SET @Diff = RTRIM(
   CASE WHEN @Day > 0 THEN CONVERT(Varchar, @Day) + 'd ' ELSE '' END +
   CASE WHEN @Hour > 0 THEN CONVERT(Varchar, @Hour) + 'h ' ELSE '' END +
   CASE WHEN @Minute> 0 THEN CONVERT(Varchar, @Minute) + 'm ' ELSE '' END)
PRINT 'Difference : ' + @Diff

seemamltn wrote:

SET @itemReceived = SELECT tck.tcktreceived
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

SELECT @itemReceived = tck.tcktreceived 
FROM tbtickets AS tck 
INNER JOIN tbticketsmessages AS tckmsg ON tck.ticketid = tckmsg.ticketid

seemamltn wrote:

PRINT @table
return @table

SELECT * FROM @table