|
why you need to do it in a single query...
you can do it in two seperate quires....
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
vimal_yet wrote: you can do it in two seperate quires....
Thank you. But I don't want to do this. If I am satisfied with doing this, what is the necessity of posting a question here on this topic  .
|
|
|
|
|
try using this
drop table [servername].[instancename].username.<tablename>
example:
drop table [dbserver].collegedetails.dbo.stud
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
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
|
|
|
|
|
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
|
|
|
|
|
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())
{
}
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
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
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
|
|
|
|
|
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
|
|
|
|
|
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.
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
|
|
|
|
|
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
|
|
|
|
|
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')
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
hello Dear ,
The Snippet really works ,
I really appretiate your help
thanks Again
Thanks And Regards
Navneet Hegde
Nashik(City Of Pilgrimage)
Develop2Program & Program2Develop
|
|
|
|
|
Can this is possible. I want to create a table using stored procedure with parameters. The given values that I enter to the parameter must me the name of the table and the column. If so plz help me.
Regards,
LEE
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
