Monday, March 26, 2012

Pass values

I have the following Stored procedure. There is column named
"tcktreceived" in my database and I want to pass all the rows one by
one to the parameter @dotnet.itags.org.starttime. I don't know how to do it.
CREATE PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@dotnet.itags.org.starttime DateTime
AS
BEGIN
DECLARE @dotnet.itags.org.Diff Varchar(15)
DECLARE @dotnet.itags.org.Day INT
DECLARE @dotnet.itags.org.Hour INT
DECLARE @dotnet.itags.org.Minute INT
DECLARE @dotnet.itags.org.Start_Date DateTime
DECLARE @dotnet.itags.org.End_Date DateTime
DECLARE @dotnet.itags.org.itemReceived DateTime
DECLARE @dotnet.itags.org.ID INT
DECLARE @dotnet.itags.org.message VARCHAR(50)
DECLARE @dotnet.itags.org.table TABLE
(
ItemReceived DateTime,
ID INT,
message text,
Differnce VARCHAR(20)
)
SET NOCOUNT ON;
SET @dotnet.itags.org.Start_Date = @dotnet.itags.org.starttime
SET @dotnet.itags.org.End_Date = GETDATE()
SET @dotnet.itags.org.Day = DATEDIFF( day, @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Hour = DATEDIFF(hour , @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Minute = DATEDIFF(minute , @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Minute = @dotnet.itags.org.Minute-(@dotnet.itags.org.HOUR* 60)
SET @dotnet.itags.org.Hour = @dotnet.itags.org.Hour-(24* @dotnet.itags.org.Day)
SET @dotnet.itags.org.Diff = CONVERT(Varchar, @dotnet.itags.org.Day) +'d ' + CONVERT(Varchar , @dotnet.itags.org.Hour) +
'h ' + CONVERT(Varchar , @dotnet.itags.org.Minute) +'m'
INSERT INTO @dotnet.itags.org.table(ItemReceived, ID, message, Differnce)
select tck.tcktreceived, tck.ticketid,tckmsg.tcktmessage,@dotnet.itags.org.Diff
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
select * from @dotnet.itags.org.table
ENDI'm not sure what your after, but try looking at using "Cursor" that might
give you what you need.

0 comments:

Post a Comment