Create a stored proc for the following:

I need to create a stored proc for the following:

SELECT tbl_index_ticket_devices.TicketSolutionComponentIndexID, tbl_index_ticket_devices.DeviceID, tbl_index_ticket_devices.TicketID, tbl_index_ticket_devices.DateAdded, tbl_index_ticket_devices.AgentAdded, tbl_company.Company,
tbl_devices.DeviceID, tbl_devices.FriendlyName, tbl_lookup_type_devices.DeviceVendor, tbl_lookup_type_devices.DeviceModel, tbl_lookup_type_devices.DeviceDescription FROM tbl_devices Join tbl_index_ticket_devices ON tbl_devices.DeviceID
= tbl_index_ticket_devices.DeviceID Join tbl_company ON tbl_devices.EnterpriseID = tbl_company.EnterpriseID Join tbl_lookup_type_devices ON tbl_devices.DeviceTypeID = tbl_lookup_type_devices.DeviceTypeID WHERE
tbl_index_ticket_devices.TicketID = '12' ORDER BY tbl_index_ticket_devices.DateAdded ASC

Any help would be appreciated.

It can be like the following example: 
 
LTER PROCEDURE [dbo].[sprGetSearchTicketsByCompany]
	@strCompanyId varchar(500),
	@startRowIndex int,
	@maximumRows int, 
	@totalRows int OUTPUT
AS
 
BEGIN
	SET NOCOUNT ON;
    
    set @strCompanyId=rtrim(@strCompanyId)
 
    Select * from (
 
		SELECT tbl_company.EnterpriseID AS EnterpriseID, tbl_company.Company AS Company, tbl_tickets.TicketID as TicketID, 
		tbl_tickets.TicketNumber, tbl_tickets.TicketType, tbl_tickets.TicketStatus, tbl_tickets.ShortDescription, 
		tbl_tickets.ExternalTicket, tbl_tickets.RequestType, tbl_tickets.AgentCreated, tbl_tickets.DateCreated ,
		ROW_NUMBER() OVER(ORDER BY tbl_company.EnterpriseID) as RowNum 
		FROM tbl_tickets 
		LEFT JOIN tbl_index_ticket_companies ON tbl_tickets.TicketID = tbl_index_ticket_companies.TicketID 
		LEFT JOIN tbl_locations ON tbl_index_ticket_companies.LocationID = tbl_locations.LocationID 
		LEFT JOIN tbl_company ON tbl_locations.EnterpriseID = tbl_company.EnterpriseID 
		WHERE tbl_company.EnterpriseID = @strCompanyId AND tbl_tickets.DeletedFlag = 0 
	)
    a WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1	        
	ORDER BY DateCreated  DESC

Open in new window

mathieu_cuprykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohan_sekarCommented:
CREATE PROCEDURE [dbo].[test_SP]
(
      @TicketID int
)
AS
BEGIN

      SELECT tbl_index_ticket_devices.TicketSolutionComponentIndexID, tbl_index_ticket_devices.DeviceID, tbl_index_ticket_devices.TicketID, tbl_index_ticket_devices.DateAdded, tbl_index_ticket_devices.AgentAdded, tbl_company.Company,
tbl_devices.DeviceID, tbl_devices.FriendlyName, tbl_lookup_type_devices.DeviceVendor, tbl_lookup_type_devices.DeviceModel, tbl_lookup_type_devices.DeviceDescription FROM tbl_devices Join tbl_index_ticket_devices ON tbl_devices.DeviceID
= tbl_index_ticket_devices.DeviceID Join tbl_company ON tbl_devices.EnterpriseID = tbl_company.EnterpriseID Join tbl_lookup_type_devices ON tbl_devices.DeviceTypeID = tbl_lookup_type_devices.DeviceTypeID WHERE
tbl_index_ticket_devices.TicketID = @TicketID ORDER BY tbl_index_ticket_devices.DateAdded ASC

END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
channa_mCommented:
Here it is
CREATE PROCEDURE sprGetSearchTicketsByTicketId
	-- Add the parameters for the stored procedure here
	@tickedId nvarchar(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
SELECT    tbl_index_ticket_devices.TicketSolutionComponentIndexID, tbl_index_ticket_devices.DeviceID
		, tbl_index_ticket_devices.TicketID, tbl_index_ticket_devices.DateAdded
		, tbl_index_ticket_devices.AgentAdded, tbl_company.Company
		, tbl_devices.DeviceID, tbl_devices.FriendlyName, tbl_lookup_type_devices.DeviceVendor
		, tbl_lookup_type_devices.DeviceModel, tbl_lookup_type_devices.DeviceDescription 
FROM tbl_devices 
		Join tbl_index_ticket_devices ON tbl_devices.DeviceID = tbl_index_ticket_devices.DeviceID 
		Join tbl_company ON tbl_devices.EnterpriseID = tbl_company.EnterpriseID 
		Join tbl_lookup_type_devices ON tbl_devices.DeviceTypeID = tbl_lookup_type_devices.DeviceTypeID 
WHERE tbl_index_ticket_devices.TicketID = @tickedId 
ORDER BY tbl_index_ticket_devices.DateAdded ASC 
 
END
GO

Open in new window

0
mathieu_cuprykAuthor Commented:
Great job.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.