|
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
|
|
|
|
|
It is possible to do this using embedded SQL, but is this really a valid design. Following the creation of the table, you would somehow have to be able to insert records into this table, update it and so on. The ability to do this would have to be persisted somewhere in your calling application. Are you really sure that this is what you want to do?
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am just curious, but what exactly are you trying to do?
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hello friends
i have a doubt pls clarify it. i want to transfer all data to one table to another table .How can i do it pls explain me and give syntax or tell wizard how to do it.
pls helpme urgent
kankeyan
|
|
|
|
|
At its simplest:
INSERT INTO DestinationTable
SELECT * FROM SourceTablel;
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
|
|
|
|
|
thank u Mr colin its very simple
thanks
|
|
|
|
|
Hi,
I work on a function which extract a blob field from a database access with datareader but I have an error:
specified cast is not valid
The code is:
Dim sql As String = DB_CONNECT<br />
Dim connect As New OleDb.OleDbConnection(sql)<br />
<br />
connect.Open()<br />
<br />
Dim command As New OleDb.OleDbCommand("select bindata from notiz", connect)<br />
<br />
Dim reader As OleDb.OleDbDataReader = command.ExecuteReader(CommandBehavior.SequentialAccess)<br />
<br />
<br />
Dim i As Integer = 0<br />
Dim v(100) As String<br />
<br />
<br />
<br />
While reader.Read()<br />
<br />
v(i) = reader.GetString(0)<br />
<br />
Maybe you have an idea.
Thanks.
|
|
|
|
|
It will arrive as binary data in which case you need a byte array. I don't know enough VB to say how you declare that byte[] in C#.
Dim v() As Byte perhaps?
Then use GetBytes[^] to get the data from the column.
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,
I am using DAAB January 2006. I have the following declaration, do I need it, I get an error:
db.AddOutParameter(command, "@ReturnValue", DbType.Int32, 4);
Error:
Procedure or function sp_Property_InsertProperty has too many arguments specified.
I use it to get the value returned like:
using (dr = db.ExecuteReader(command))
{
intResultCode = (int)db.GetParameterValue(command, "@ReturnValue");
}
The variable @ReturnValue is what SQL returns and need not be declared?
Please can someone help?
Regards
ma se
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
