How to return multiple values?

mercredi 30 mai 2012 14:39

My data has timestamp, but every timestamp field will be the same for the return results of my stored proc. If I am returning up to fifty records to the calling webservice, I dont want to return the same timestamp fifty times. But if I run a query before/after to see what the timestamp used was, it would be right most of the time, but not all of the time.

Here is the stored proc, so far and the table.

USE [TW_Cloud]
GO

/****** Object:  StoredProcedure [dbo].[MetroGetLatestUp]    Script Date: 05/30/2012 07:25:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Daniel
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[MetroGetLatestUp] 
	-- Add the parameters for the stored procedure here
	@Sensitivity float = 0.25, 
	@timeBack int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @eventHorizonDate datetime
declare @olderDate datetime
  
 SELECT  @eventHorizonDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' ORDER BY EntryTime DESC)
--SELECT @laterDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' AND EntryTime <>  @eventHorizonDate ORDER BY EntryTime DESC)
  select @olderdate = (select top (1) entrytime from (select top(2 + @timeback) entryTime from mean where ticker = 'spy' order by entrytime desc)t order by entrytime asc)
 
  
select * from (select  t1.Ticker,t1.NEWS,
  (select News from mean t2 
   join quotecount200
   on quotecount200.ticker = t1.Ticker
   and quotecount200.mycount > 190    
   where entrytime = @eventHorizonDate 
   and t2.ticker = t1.Ticker)-t1.news as diff  
  
from mean t1 where entrytime = @olderdate) X
WHERE (diff >.1 + @sensitivity) and ticker in 
(select ticker from mean where --news <1 --and news > -1 
--and 
entrytime >@olderdate and entrytime <= @eventhorizondate)
order by (diff ) desc


-- declare @earlyDate datetime
-- declare @laterDate datetime
--  SELECT  @earlyDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' ORDER BY EntryTime DESC)
--SELECT @laterDate = (SELECT TOP 1 EntryTime FROM Mean WHERE ticker = 'spy' AND EntryTime <>  @earlyDate ORDER BY EntryTime DESC)
--  select @laterdate = (select top (1) entrytime from (select top(2) entryTime from mean where ticker = 'spy' order by entrytime desc)t order by entrytime asc)
--print @earlydate
--print @laterdate

END

GO

USE [TW_Cloud]
GO

/****** Object:  Table [dbo].[Mean]    Script Date: 05/30/2012 07:40:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Mean](
	[MeanId] [int] IDENTITY(1,1) NOT NULL,
	[Ticker] [varchar](10) NOT NULL,
	[Price] [money] NOT NULL,
	[EntryTime] [datetime] NOT NULL,
	[News] [float] NULL,
	[ActualPrice] [money] NULL,
 CONSTRAINT [PK_Mean] PRIMARY KEY CLUSTERED 
(
	[MeanId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UK_Mean_EntryTime_Ticker] UNIQUE NONCLUSTERED 
(
	[EntryTime] ASC,
	[Ticker] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Toutes les réponses

mercredi 30 mai 2012 14:45

0

Without sample data it is almost impossible to guess. Post a concise and complete example.
- Réponse
- Citation
mercredi 30 mai 2012 15:12

0

Please convert Your Time Stamp to required format and then try with distinct key word.

This is assumption answer.Please explain with sample data and expected Output
With Thanks and Regards
Sambath Raj.C
- Réponse
- Citation
mercredi 30 mai 2012 15:17

0

Do you have a link by chance on how to prepare sample data correctly, to be submitted?

Thanks,

Daniel
- Réponse
- Citation
mercredi 30 mai 2012 15:21

0

A concise and complete example consist of a table declaration, either a temporary table or table variable (or more of them). Appropriate insert statements filling it with sample data. And your (failing) query and your desired output. In your special case of an stored procedure you don't the create procedure part, refactor it to a simple query..
- Réponse
- Citation
mercredi 30 mai 2012 15:21

0
As mentioned by the other replies here, without seeing your sample data , our suggestions are ONLY a guess.

The data type of your "timestamp" columns is datetime. The datetime datatype has precision/accuracy of 300 milliseconds only. This means if you have had multiple rows inserted within a 300 ms timeframe, they will all have the same value in the EntryTime column - which is of datetime data type - http://msdn.microsoft.com/en-us/library/ms187819.aspx

This could possibly be the reason for the intermittent nature of the issue. This is ONLY a guess since there is no sample data in your post.
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com
- Proposé comme réponse Naomi NMicrosoft Community Contributor, Moderator mercredi 30 mai 2012 15:28
- Réponse
- Citation
mercredi 30 mai 2012 15:28

0
Hi Sanil,

All I was saying was I dont know how to save the sample data in such a way as to make it easy for others to recreate. The last time I did it, I was told I did it wrong, and required extra effort on the part of the person who was helping me. I guess when I said in my previous message, "Do you have a link by chance on how to prepare sample data correctly, to be submitted? " that I wasnt clear.

In any case, I have a number of TSQL problems and have decided to hire someone for a few hours, rather than spend days trying to do it myself. So, I guess I can say, "Nevermind" though I would still like to know how to post sample data.

Oh, the timestamp for my records only change every five minutes. I dont have a query that has a problem, because I dont return the timestamp. I wanted to know if there was a way to return it, without returning it fifty times. In otherwords, I probably should have said "Is it possible to return a scalar value along with my regular data returned, in c#."

In fact, maybe I will repost that as my question(g), though I am still going to hire someone.

Thanks,

Daniel
- Modifié danielsn mercredi 30 mai 2012 15:29
- Modifié danielsn mercredi 30 mai 2012 15:29
- Réponse
- Citation
mercredi 30 mai 2012 15:35

0
Something like this:
```
Declare @tvTable Table (
	MeanId int,
	Ticker varchar(10),
	Price money,
	EntryTime datetime,
	News float,
	ActualPrice money
	)

Insert @tvTable
Select 1, 'ticker01', 1.05, '2012-01-01', 1.12345, 2.10
UNION ALL
Select 2, 'ticker02', 4.05, '2012-02-01', 5.4321, 12.10

Select * From @tvTable
```
Basically, provide the table definition in the form of a temp table or table variable, and then give us some insert statements into that table. The data doesn't have to be "real", just has to make sense for the definition and query.

Edit:
The OP in this thread gave table definition as a table variable, the insert statements, and the desired output of the query they needed help with.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/abb5c5c7-9ccd-42fb-8955-58078ca5b416
- Modifié dgjohnson mercredi 30 mai 2012 15:39
- Réponse
- Citation
mercredi 30 mai 2012 15:39

0

You can do that: Use an additional output parameter for your stored procedure.
- Réponse
- Citation
mercredi 30 mai 2012 15:41

0
Thanks DGJohnson,

On my next question I will be sure and do that.

On this question, I was able to deduce the answer on my own though, so I might as well post it.

I can return a single value, by adding another

Begin

select @eventhorizondate

end

and then in the C# code I do this

while(Read.Read())

{}

Read.NextRecord() //advances to the next result set

while(Read.Read())

Thanks,

Daniel
- Marqué comme réponse danielsn mercredi 30 mai 2012 15:41
- Réponse
- Citation
mercredi 30 mai 2012 15:43

Modérateur

0
If you install SSMS Tools Pack (assuming you're using SQL Server 2008/R2), it has an option to generate insert scripts for the table.

Take a look here

SSMS Tools PACK, Something Every SQL Server Developer That Uses SSMS Should Have Installed

Alternatively, Brad Schulz posted a script you can use to generate insert statements, but as usual I'm having a hard time searching for that blog - spent already 10 minutes - no cigar.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
- Marqué comme réponse danielsn mercredi 30 mai 2012 15:53
- Réponse
- Citation
mercredi 30 mai 2012 15:43

0

Consider using an output parameter instead..
- Réponse
- Citation
mercredi 30 mai 2012 15:53

0

thanks Naomi
- Réponse
- Citation

How to return multiple values?

Toutes les réponses

SSMS Tools PACK, Something Every SQL Server Developer That Uses SSMS Should Have Installed

Produits

Ressources

Solutions

Mises à jour

Évaluations

Sites connexes

Formation

Certifications

Autres ressources

Support TechNet

Autres liens support