Can you please write storedprocedure for this query

Posted by Srinivasulu.enugu under Sql Server on 5/28/2011 | Points: 10 | Views : 1204 | Status : [Member] | Replies : 3
Hi friends ,

This is srinivasulu , i want small help . i want stored procedure for this query .





SELECT x.resourceName, x.resourceFirstName, x.resourceLastName, x.resourceID, x.assignedTeamID,
x.extension, t.eventType, x.datetime FROM (SELECT t1.resourceID, t1.resourceName, t1.resourceFirstName,
t1.resourceLastName,t1.assignedTeamID, t1.extension, MAX(t2.eventDateTime)
AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID WHERE
t1.active = 't' GROUP BY t1.resourceID, t1.resourceName, t1.resourceFirstName, t1.resourceLastName,
t1.assignedTeamID, t1.extension) AS x INNER JOIN AgentStateDetail AS
t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime

Srinivasulu Enugu


Responses

Posted by: SheoNarayan on: 5/28/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Simply wrap your SQL statement with the Create Procedure command like below.

CREATE PROCEDURE YourStoredProcedure

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 x.resourceName, x.resourceFirstName, x.resourceLastName, x.resourceID, x.assignedTeamID,
x.extension, t.eventType, x.datetime FROM (SELECT t1.resourceID, t1.resourceName, t1.resourceFirstName,
t1.resourceLastName,t1.assignedTeamID, t1.extension, MAX(t2.eventDateTime)
AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID WHERE
t1.active = 't' GROUP BY t1.resourceID, t1.resourceName, t1.resourceFirstName, t1.resourceLastName,
t1.assignedTeamID, t1.extension) AS x INNER JOIN AgentStateDetail AS
t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime

END

Looks like you are not passing any parameter here, in case you are passing any input parameter, add something like this

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 

-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 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
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END


If you right click your database > programmability > stored procedure and click on New Stored procedure, you will get a template like above. You need to modify it as per your requirement.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Srinivasulu.enugu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 12/8/2011 [Member] Silver | Points: 25

Up
0
Down
create procedure spResourceDetails
as
Begin
SELECT x.resourceName, x.resourceFirstName, x.resourceLastName, x.resourceID, x.assignedTeamID,
x.extension, t.eventType, x.datetime FROM

(
SELECT t1.resourceID, t1.resourceName, t1.resourceFirstName,
t1.resourceLastName,t1.assignedTeamID, t1.extension, MAX(t2.eventDateTime)
AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID WHERE
t1.active = 't' GROUP BY t1.resourceID, t1.resourceName, t1.resourceFirstName, t1.resourceLastName,
t1.assignedTeamID, t1.extension
) AS x

INNER JOIN AgentStateDetail AS t
ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime

End

Thanks,
Sanjay

Srinivasulu.enugu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rrana on: 12/8/2011 [Member] Starter | Points: 25

Up
0
Down
Create procedure Sp_Procedurename

as

Begin


SELECT x.resourceName, x.resourceFirstName, x.resourceLastName, x.resourceID, x.assignedTeamID,
x.extension, t.eventType, x.datetime FROM (SELECT t1.resourceID, t1.resourceName, t1.resourceFirstName,
t1.resourceLastName,t1.assignedTeamID, t1.extension, MAX(t2.eventDateTime)
AS datetime FROM Resource AS t1 INNER JOIN AgentStateDetail AS t2 ON t2.agentID = t1.resourceID WHERE
t1.active = 't' GROUP BY t1.resourceID, t1.resourceName, t1.resourceFirstName, t1.resourceLastName,
t1.assignedTeamID, t1.extension) AS x INNER JOIN AgentStateDetail AS
t ON t.agentID = x.resourceID AND t.eventDateTime = x.datetime

End

Sucharitha Goud
Bank Of America..

Srinivasulu.enugu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response