October 31, 2016 at 10:04 pm
Hi ,
I want to take the data from database based on Id order by timestamp desc and then select first 20 records from the database.
How to achieve this?In C# using lambda expression but i need as a stored procedure.
create PROCEDURE [dbo].[GetTop20MessageByRoomIdOrderByTimeStamp]
@roomId int
as
begin
Select TOP 10 * from (Select * from message where RoomID=@roomId) ORDER BY [TimeStamp] DESC
end
But this Script has problem in execution.Please help me to overcome this..I am new to stored procedure.
Regards,
Poornima
November 1, 2016 at 2:02 am
poornima.s_pdi (10/31/2016)
Hi ,I want to take the data from database based on Id order by timestamp desc and then select first 20 records from the database.
How to achieve this?In C# using lambda expression but i need as a stored procedure.
create PROCEDURE [dbo].[GetTop20MessageByRoomIdOrderByTimeStamp]
@roomId int
as
begin
Select TOP 10 * from (Select * from Room where RoomID=@roomId) ORDER BY [TimeStamp] DESC
end
But this Script has problem in execution.Please help me to overcome this..I am new to stored procedure.
Regards,
Poornoma
Can you post the DDL (create table) script for the table?
😎
November 1, 2016 at 2:14 am
Hi Eirikur Eiriksson,
CREATE TABLE [dbo].[Message](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[RoomID] [int] NULL,
[UserID] [int] NOT NULL,
[ToUserID] [int] NULL,
[Text] [varchar](100) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[Color] [varchar](50) NULL,
CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
[MessageID] 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
ALTER TABLE [dbo].[Message] ADD CONSTRAINT [DF_Message_TimeStamp] DEFAULT (getdate()) FOR [TimeStamp]
GO
ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [FK_Message_Room] FOREIGN KEY([RoomID])
REFERENCES [dbo].[Room] ([RoomID])
GO
ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_Room]
GO
ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [FK_Message_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_User]
GO
ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [FK_Message_User1] FOREIGN KEY([ToUserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_User1]
GO
Above is my table script.
I want to take the messages from "messages" table using RoomID and need to order by timestamp then after that need to select top20.
Waiting for your reply.
Regards,
Poornima
November 1, 2016 at 3:10 am
This procedure returns the top 20 rows, filtered on the RoomID in the descending order of the TimeStamp
😎
CREATE PROCEDURE [dbo].[GetTop20MessageByRoomIdOrderByTimeStamp]
(
@ROOMID INT
)
AS
SET NOCOUNT ON;
SELECT
TOP (20)
MSG.MessageID
,MSG.RoomID
,MSG.UserID
,MSG.ToUserID
,MSG.[Text]
,MSG.[TimeStamp]
,MSG.Color
FROM dbo.Message MSG
WHERE MSG.RoomID = @ROOMID
ORDER BY MSG.[TimeStamp] DESC;
Edit: Correction, wrong template.
November 1, 2016 at 3:26 am
Hi,
Thanks for your reply.
But I am getting the below error while executing the stored procedure.
Msg 487, Level 16, State 1, Procedure GetTop20MessageByRoomIdOrderByTimeStamp, Line 6
An invalid option was specified for the statement "CREATE/ALTER PROCEDURE".
How to solve this?One more thing will this query retrieveTop 20 records and then sort by desc or retrieve all records, sort by desc and then display top 20 records?
Regards,
Poornima
November 1, 2016 at 3:49 am
poornima.s_pdi (11/1/2016)
Hi,Thanks for your reply.
But I am getting the below error while executing the stored procedure.
Msg 487, Level 16, State 1, Procedure GetTop20MessageByRoomIdOrderByTimeStamp, Line 6
An invalid option was specified for the statement "CREATE/ALTER PROCEDURE".
How to solve this?One more thing will this query retrieveTop 20 records and then sort by desc or retrieve all records, sort by desc and then display top 20 records?
Regards,
Poornima
Sorry, my bad, used a wrong template for the procedure, corrected it and this should be fine.
😎
November 1, 2016 at 11:52 pm
Hi Eirikur Eiriksson,
Thank you very much.
The Stored Procedure worked now.
Interested in learning these stored procedure and this forum is really helpful.
Tks for your immediate reply.:-)
Regards,
Poornima
November 2, 2016 at 3:56 am
poornima.s_pdi (11/1/2016)
Hi Eirikur Eiriksson,Thank you very much.
The Stored Procedure worked now.
Interested in learning these stored procedure and this forum is really helpful.
Tks for your immediate reply.:-)
Regards,
Poornima
You are very welcome.
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply