Database

18-Jun-07 13:17

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 @starttime. I don't know how to do it.

CREATE PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@starttime DateTime

AS
BEGIN
DECLARE @Diff Varchar(15)
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @itemReceived DateTime
DECLARE @ID INT
DECLARE @message VARCHAR(50)

DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message text,
Differnce VARCHAR(20)

)
SET NOCOUNT ON;
SET @Start_Date = @starttime

SET @End_Date = GETDATE()
SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) +
'h ' + CONVERT(Varchar , @Minute) +'m'

INSERT INTO @table(ItemReceived, ID, message, Differnce)
select tck.tcktreceived, tck.ticketid,tckmsg.tcktmessage,@Diff
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

select * from @table

END

seema

Re: pass the values

Colin Angus Mackay18-Jun-07 13:27

18-Jun-07 13:27

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

Use CURSORs for that. However, that is generally very bad practice and should only be done when all other avenues have been exhausted.

SQL Server, and any relational database, works best on sets of data rather than individual items of data. If you can do what you want in one SELECT statement the operation will be much faster and you will use less resources on the SQL Server because you are taking advantage of its capabilities.

It might be best to tell us what the big picture is. Then we might be able to guide you to a set based solution.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: pass the values

seemamltn18-Jun-07 13:32

18-Jun-07 13:32

Hi,

Here is my Specs. I created Stored Procedure Which I posted befor.for meeting the requriements
I will be really grateful if you could solve my problem.

I have a table which has a field "tcktReceived" of type datetime. I
have a grid view which has two columns.

In first column i have to show the data from field "tcktReceived" and
in second column I have to show
difference between Currenttime and date from tcktReceived.

e'g

ItemRecieved Difference
6/13/2007 12:38am 1d 21h 45m
6/13/2007 3:54pm 1d 06h 10m
6/15/2007 12:26pm 34m

So the second coulmn displays the difference of current time minus
ItemRecieved. the format shouild be <mintues>m,
or<hours>h, <mintues>m, or <days>d.

How can I do that.

Thanks in advance

seema

Re: pass the values

Colin Angus Mackay18-Jun-07 14:26

Return Value from Stored Procedure

18-Jun-07 14:26

Create a function to calcualte the difference and format it as you need it.

Something like this:

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

seemamltn18-Jun-07 9:47

Re: Return Value from Stored Procedure

18-Jun-07 9:47

I have a stored proceure whic is returning a table. Now I need ADO.Net statement So that IO can pass parameters to this stored procedure and get Retuened table from stored procedure.

Could you please send me code how can I reterive a returen value of tyoe table from stroed procedure in ADO.Net

Tnanks.

seema

Colin Angus Mackay18-Jun-07 13:18

18-Jun-07 13:18

You don't return tables from a stored procedure, you SELECT them. It will then return as a result set.

Off the top of my head, something like this will work:

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.
}

The stored procedure needs to SELECT the data, as I told you before, in order for it to be collected.

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

Error message

seemamltn18-Jun-07 9:43

18-Jun-07 9:43

Again thanks a lot but When I execute only this portion it gives me error

Syntax error converting the varchar value '2d 0h 0m' to a column of data
type int.

My stored procedure is the following.

Followed is the SQL Script and you can use this code in your SQL Function to
get the difference and make function with StartDate and EndDate parameters
and return the @Diff

DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
SET @Start_Date = '6/13/2007 12:38 AM'
SET @End_Date = '6/14/2007 11:59 PM'
SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT( Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) + 'h '
+ CONVERT(Varchar , @Minute) +'m'
PRINT
'Difference : ' + @Diff

seema

Re: Error message

andyharman19-Jun-07 7:23

andyharman

19-Jun-07 7:23

Hi Seema

The @Diff variable should be something like a varchar(20). Try the following:

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

If you use it a lot then perhaps you should set this up as a UDF.

Regards
Andy

Stored Proc

seemamltn18-Jun-07 8:51

18-Jun-07 8:51

I have the following stroed procedue. But whebnnI execute it it gives me following errors.
Could you please tell me what is wrong

Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 43
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 47
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure usp_DateDiff, Line 52
Incorrect syntax near the keyword 'select'.
Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 58
Must declare the variable '@table'.
Msg 137, Level 15, State 2, Procedure usp_DateDiff, Line 59
Must declare the variable '@table'.

alter PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@endDate DateTime

AS
BEGIN
DECLARE @Diff INT
DECLARE @Day INT
DECLARE @Hour INT
DECLARE @Minute INT
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @itemReceived DateTime
DECLARE @ID INT
DECLARE @message VARCHAR(50)

DECLARE @table TABLE
(
ItemReceived DateTime,
ID INT,
message VARCHAR(100),
Differnce VARCHAR(20)

)
SET NOCOUNT ON;
SET @Start_Date = DateTime.Now
SET @End_Date = @endDate

SET @Day = DATEDIFF( day, @Start_Date, @End_Date)
SET @Hour = DATEDIFF(hour , @Start_Date, @End_Date)
SET @Minute = DATEDIFF(minute , @Start_Date, @End_Date)
SET @Minute = @Minute-(@HOUR* 60)
SET @Hour = @Hour-(24* @Day)
SET @Diff = CONVERT(Varchar, @Day) +'d ' + CONVERT(Varchar , @Hour) + 'h ' + CONVERT(Varchar , @Minute) +'m'
SET @itemReceived = SELECT tck.tcktreceived
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

SET @ID = select tck.ticketid
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

SET @message = select tckmsg.tcktmessage
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

INSERT INTO @table(ItemReceived, ID,message,Differnce)
Values(@itemReceived, @ID, @message, @Diff)
PRINT @table
return @table
END

seema

Re: Stored Proc

Colin Angus Mackay18-Jun-07 9:22

18-Jun-07 9:22

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

Try

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

The same goes for the other two SETs with a SELECT in them.

seemamltn wrote:
PRINT @table
return @table

I don't think you can do either of those things.

If you want to return a result set from a stored procedure SELECT it:

SELECT * FROM @table

Re: Stored Proc

seemamltn18-Jun-07 9:43

18-Jun-07 9:43

seema

Re: Stored Proc

Colin Angus Mackay18-Jun-07 13:10

Set Time Part Of DateTime

18-Jun-07 13:10

You have declared @Diff as an integer. You have supplied it with a string that cannot be interpreted as a number.

Either Diff is incorrectly declared, or you need to find a way to convert the string to an appropriate number.

Polymorpher18-Jun-07 7:41

Polymorpher

18-Jun-07 7:41

Anyone have any idea how to set the time part of an SQL DateTime Field?

ALTER FUNCTION dbo.GetClassDueDate(@DropDate DATETIME,<br />
								   @ClassID  UNIQUEIDENTIFIER, <br />
								   @StoreID  UNIQUEIDENTIFIER)<br />
 RETURNS VARCHAR(50)<br />
AS<br />
 BEGIN<br />
  DECLARE @OutDate   DATETIME<br />
  DECLARE @TimeIn    DATETIME<br />
  DECLARE @NumDays   SMALLINT<br />
  DECLARE @DayOfWeek SMALLINT<br />
  DECLARE @Ret       VARCHAR(50)<br />
  DECLARE @Drop      DATETIME<br />
  <br />
  --drop needs to be the current drop, with time set to 17:00:00<br />
  SET @Drop = <br />
  <br />
  SELECT @TimeIn    = CAST(TimeIn AS DATETIME), <br />
		 @NumDays   = NumDays, <br />
		 @DayOfWeek = DayOfWeek <br />
  FROM  tblClass <br />
  WHERE ID      = @ClassID <br />
  AND   StoreID = @StoreID<br />
  <br />
  IF (DATEPART(HOUR,@TimeIn) >= DATEPART(HOUR,@DropDate)) AND (DATEPART(MINUTE,@TimeIn) > DATEPART(MINUTE,@DropDate))<br />
   BEGIN<br />
    SET @OutDate  = DATEADD(DAY, (@NumDays + 1), @DropDate)<br />
   END<br />
  ELSE<br />
   BEGIN<br />
    SET @OutDate  = DATEADD(DAY, @NumDays, @DropDate)<br />
   END<br />
  <br />
  IF NOT @DayOfWeek > 0<br />
   BEGIN<br />
    WHILE dbo.IsWorkDay(@OutDate, @StoreID) = 0<br />
     BEGIN<br />
      SET @OutDate = DATEADD(DAY, 1, @OutDate)<br />
     END<br />
   END<br />
  ELSE<br />
   BEGIN<br />
    CheckDayOff:<br />
    WHILE dbo.IsWorkDay(@OutDate, @StoreID) = 0<br />
     BEGIN<br />
      SET @OutDate = DATEADD(DAY, 1, @OutDate)<br />
     END<br />
    <br />
    IF DATEPART(WEEKDAY, @OutDate) = @DayOfWeek<br />
     BEGIN<br />
      SET @OutDate = DATEADD(DAY, 1, @OutDate)<br />
      GOTO CheckDayOff<br />
     END<br />
   END<br />
  <br />
  RETURN CAST(DATEPART(MONTH, @OutDate) AS VARCHAR(2)) + '/' + CAST(DATEPART(DAY, @OutDate) AS VARCHAR(2)) + ' ' + SUBSTRING(CAST(DATENAME(WEEKDAY, @OutDate) AS VARCHAR(10)),1,3)<br />
 END

Apparently it's not OK to start a bonfire of Microsoft products in the aisles of CompUSA even though the Linuxrulz web site says so

CDATA Sections in For XML query

GaryWoodfine 18-Jun-07 6:28

GaryWoodfine

18-Jun-07 6:28

Hi Guys,

I have a stored proc that is returning XML to the client, as listed below.
The question I have is that the description field can contain some HTMLa and possibly some illegal XML characters i.e &,<,>, ' etc.
I would like to wrap these sections in a CDATA field but am a little unsure how to do it.

Oh yeah the XML is going to be transformed by XSLT on the client.

Any tips would be greatly appreciated.
Ideally what I would like to achieve is to get rid of calling my DBO.HTMLENcode function I have written to encode the illegal characters.

Select @Brief as briefdescription,<br />
Cast('' + replace(cast(DBO.HTMLENcode(@Desc) as varchar(max)) , char(13),'' ) + '' as xml) as descriptions ,<br />
cast('' + replace(cast(DBO.HTMLENcode(Features) as varchar(max)), char(13)+char(10), '') + '' as xml) AS Features,<br />
cast(price as money) as price, bedrooms as bedroom, status_id,<br />
cast(replace(('' + replace(cast(DBO.HTMLENcode(rooms) as varchar(max)), char(13), '')+ ''),'','') as xml) as rooms,<br />
Size_metre_sq as sqm,<br />
COALESCE (ADDRESS1 + ', ', '') + COALESCE (ADDRESS2 + ', ', '') + COALESCE (ADDRESS3 + ', ',<br />
'') + COALESCE (ADDRESS4, '') AS Address ,directions ,<br />
Saleterms.SALETERMS as saleterm ,Saleterms.price_label as PriceLabel, leaseterms, rating,<br />
<br />
Convert(varchar(11) ,Availability, 113) as availability<br />
<br />
from dbo.PROPERTY as Property<br />
inner join Saleterms on Property.Saleterms_ID = Saleterms.ID<br />
where Property.id = @id<br />
FOR XML PATH('Property'), ROOT('Detail')

Kind Regards,
Gary

My Website || My Blog || My Articles

Database documentation tool recommendations?

Max Stayner18-Jun-07 4:55

Max Stayner

18-Jun-07 4:55

Hi,

Long time listener, first time caller...

I've got a reasonably significant database in production (120 tables, 100+ UDFs and SPs) and (horrors) no real documentation for it. That's been (sort of) OK up to now because I've been the sole developer, but now more folks are going to be working on it, so I need to document it.

Does anyone have any recommendations for tools to use? I'd like to be able to:
- Record EXTENSIVE notes about tables, columns, functions, SPs, indexes
- Ideally, record relationships between functions, i.e. to be able to group related functions together
- Generate a nice ER diagram.
- Print things out nicely

Thanks in advance!

Max.

Query sql datatype Sql server 2005 [modified]

Chazzysb18-Jun-07 4:31

Chazzysb

18-Jun-07 4:31

How can I dynamically query the xml in an xml datatype field?

The functions ive used are value, exist - but these assume you know the names of the xml tags.

The xml that the field holds is dynamic, so if the field contains something like:
<field1>value1</field1><field2>value2</field2>

I want the select statement to return the contents of a temp table:
field1 field2
------- -------
value1 value2

Is this possible in a select statement?

Many thanks in advance.

-- modified at 10:58 Monday 18th June, 2007

Get Value

Navneet Hegde18-Jun-07 0:24

Navneet Hegde

18-Jun-07 0:24

Hi All,

I have Table with Columns

DataID EntryDate DataValue
1 21/2/2007 25
2 22/2/2007 35
3 23/2/2007 65
4 24/2/2007 15

Now I want an extra Column which is max value of that day and it's previous day

DataID EntryDate DataValue NewColumn
1 21/2/2007 25 25
2 22/2/2007 35 35
3 23/2/2007 65 65
4 24/2/2007 15 65

How can I get the Dataset In such manner.
Though i can Process the data in coding
But Can I query database to get such Dataset output

Thanks And Wishes
Navneet Hegde
Nashik(City Of Pilgrimage)

Develop2Program & Program2Develop

Sylvester george18-Jun-07 1:07

Sylvester george

18-Jun-07 1:07

Question is little confusing ...Please provide the data for NewColumn.So that I can suggest how it would be implemented

Regards,
Sylvester G
sylvester_g_m@yahoo.com

Navneet Hegde18-Jun-07 1:21

Navneet Hegde

18-Jun-07 1:21

Hi S,

Though I have Given value above for new Column ,
Anyway

Id dateOf Entry value NewColumn
1 25/02/2007 15 15
2 26/02/2007 25 25
3 27/02/2007 18 25
4 28/02/2007 65 65

hope U get what i mean
U can just think ok Cumulative Value , but only difference is that we want the max of value's

Thanks & Wishes
Navneet Hegde
Nashik(City Of Pilgrimage)

Develop2Program & Program2Develop

Sylvester george18-Jun-07 1:52

Sylvester george

18-Jun-07 1:52

hey try this query...I tried with my table just replace with your filedname

select Created,Total, (select max(b.Total) from purchasertransactionmaster b where b.Created<=a.Created) newtotal from purchasertransactionmaster a

created = dateOf Entry
Total = value
newtotal = new column value
purchasertransactionmaster = table name

Regards,
Sylvester G
sylvester_g_m@yahoo.com