Dan Guzman
3/20/2007 1:10:00 PM
> Could anyone convert my
> code into something that would work in a stored proc?
Paste the SELECT statement after a CREATE PROCEDURE statement and execute
via Query Analyzer or SSMS:
CREATE PROCEDURE dbo.MyStoredProcedure
AS
SELECT
IP.InboundPostId,
Memb.FirstNames,
Memb.LastNames,
IP.DateReceived,
IPM.Media,
IP.DateAdded,
IPR.Reason,
IP.Description,
IPRH.InboundPostResolutionHistoryId,
IPRH.UserName,
IPRH.Action,
IPRH.CommentDate,
IPRH.Comment,
IPRH.Status
FROM dbo.tblInboundPost AS IP
INNER JOIN dbo.tblMembers AS Memb
ON IP.MemberId =Memb.MemberID
INNER JOIN dbo.tblInboundPostReason AS IPR ON
IP.InboundPostReasonId = IPR.InboundPostReasonId
INNER JOIN dbo.tblInboundPostMedia AS IPM ON
IP.InboundPostMediaId = IPM.InboundPostMediaId
LEFT OUTER JOIN
(SELECT
LastComment.InboundPostResolutionHistoryId,
LastComment.InboundPostId,
LastComment.Comment,
LastComment.UserName,
LastComment.CommentDate,
IPA.Action,
IPS.Status
FROM dbo.tblInboundPostResolutionHistory AS LastComment
INNER JOIN
(SELECT
MAX(CommentDate) AS MaxDate,
InboundPostId
FROM dbo.tblInboundPostResolutionHistory
GROUP BY
InboundPostId
) AS MaxPosts ON
LastComment.CommentDate = MaxPosts.MaxDate AND
LastComment.InboundPostId = MaxPosts.InboundPostId
INNER JOIN dbo.tblInboundPostAction AS IPA ON
LastComment.InboundPostActionId = IPA.InboundPostActionId
INNER JOIN dbo.tblInboundPostStatus AS IPS ON
LastComment.InboundPostStatusId = IPS.InboundPostStatusId
) AS IPRH ON
IP.InboundPostId = IPRH.InboundPostId
WHERE (IP.MemberId = '123')
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
<robert.bath@hamiltonfraser.co.uk> wrote in message
news:1174390431.142788.128480@y80g2000hsf.googlegroups.com...
> Hi,
>
> I have created a View that works the way I want it to; but when I try
> to paste it into a Stored Procedure I get the following error message
> "Msg 102, Level 15, State 1, Procedure procInboundPostSelect, Line 33
> Incorrect syntax near ')'." Now as far as I can tell SQL is
> complaining about my Subquery or alias'... Could anyone convert my
> code into something that would work in a stored proc?
>
> Thanks in advance,
>
> Rob
>
> -- The code so far:
>
> SELECT IP.InboundPostId, Memb.FirstNames, Memb.LastNames,
> IP.DateReceived, IPM.Media, IP.DateAdded, IPR.Reason, IP.Description,
> IPRH.InboundPostResolutionHistoryId,
> IPRH.UserName, IPRH.Action, IPRH.CommentDate, IPRH.Comment,
> IPRH.Status
> FROM dbo.tblInboundPost AS IP INNER JOIN
> dbo.tblMembers AS Memb ON IP.MemberId =
> Memb.MemberID INNER JOIN
> dbo.tblInboundPostReason AS IPR ON
> IP.InboundPostReasonId = IPR.InboundPostReasonId INNER JOIN
> dbo.tblInboundPostMedia AS IPM ON
> IP.InboundPostMediaId = IPM.InboundPostMediaId LEFT OUTER JOIN
> (SELECT
> LastComment.InboundPostResolutionHistoryId, LastComment.InboundPostId,
> LastComment.Comment, LastComment.UserName,
>
> LastComment.CommentDate, IPA.Action, IPS.Status
> FROM
> dbo.tblInboundPostResolutionHistory AS LastComment INNER JOIN
> (SELECT
> MAX(CommentDate) AS MaxDate, InboundPostId
> FROM
> dbo.tblInboundPostResolutionHistory
> GROUP BY
> InboundPostId) AS MaxPosts ON LastComment.CommentDate =
> MaxPosts.MaxDate AND
>
> LastComment.InboundPostId = MaxPosts.InboundPostId INNER JOIN
>
> dbo.tblInboundPostAction AS IPA ON LastComment.InboundPostActionId =
> IPA.InboundPostActionId INNER JOIN
>
> dbo.tblInboundPostStatus AS IPS ON LastComment.InboundPostStatusId =
> IPS.InboundPostStatusId) AS IPRH ON
> IP.InboundPostId = IPRH.InboundPostId
> WHERE (IP.MemberId = '123')
>