Accessing Views from Stored Procedures

Hi,

I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?

Thanks.

CREATEPROCEDURE [dbo].[usp_From_A_View]

AS

BEGIN

SETNOCOUNTON;

SELECT*FROM dbo.View_1

--retrieve from a view

END

|||

Thanks for the code.

I know we can't use parameters with Views, so in that case how would I go about implementing the following stored procedure if I had to split it up into a view and a sproc?

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[GetProjectInfo]
( @.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL, @.ProjTypevarchar(20) =NULL,
 @.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)
AS
BEGIN
SET NOCOUNT ON
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, P.ProjectManagerID, M.FName, M.LName, P.RequestedBy, P.DateRequested, P.DueDate, P.ProjectStatusID, S.ProjectStatus, M.FName +' ' + M.LNameAs ProjectManagerName
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE (P.ProjType = @.ProjTypeOR @.ProjTypeISNULL)
AND (P.ProjectTitleLIKE'%' + @.ProjectTitle +'%'OR @.ProjectTitleISNULL)
AND (P.ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND (P.ProjIDLIKECaseWHEN @.DeptcodeISNOT NULLTHEN @.Deptcode +'-' +'%'WHEN @.DeptCodeISNULL AND @.projidISNOT NULLTHEN'%' + @.projid +'%'ELSE ProjIDEND )
AND (P.DateRequested = @.DateRequestedOR @.DateRequestedISNULL)
AND (P.DueDate = @.DueDateOR @.DueDateISNULL)
AND (P.ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)
AND P.ProjectManagerID = M.ProjectManagerIDAND P.ProjectStatusID = S.ProjectStatusID
END

TIA.

|||

Create 3 views as following:

Create View v_Projectasselect *from tbl_Project GO-----------Create View v_ProjectManagerasselect *from tbl_ProjectManagerGO-----------create view v_ProjectStatusasselect *from tbl_ProjectStatus GO-----------

and here is the stored procedure that used those created views:

ALTER PROCEDURE [dbo].[GetProjectInfo]
@.ProjectTitlevarchar(300) ,
@.ProjectManagerIDint ,
@.DeptCodevarchar(20) ,
@.ProjTypevarchar(20) ,
@.ProjIDvarchar(50) ,
@.DateRequesteddatetime ,
@.DueDatedatetime ,
@.ProjectStatusIDint

AS

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, P.ProjectManagerID, M.FName, M.LName, P.RequestedBy, P.DateRequested, P.DueDate, P.ProjectStatusID, S.ProjectStatus, M.FName +' ' + M.LNameAs ProjectManagerName
FROM vProject P, vProjectManager M, vProjectStatus S
WHERE (P.ProjType = @.ProjTypeOR @.ProjTypeISNULL)
AND (P.ProjectTitleLIKE'%' + @.ProjectTitle +'%'OR @.ProjectTitleISNULL)
AND (P.ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)
AND (P.ProjIDLIKECaseWHEN @.DeptcodeISNOT NULLTHEN @.Deptcode +'-' +'%'WHEN @.DeptCodeISNULL AND @.projidISNOT NULLTHEN'%' + @.projid +'%'ELSE ProjID END )
AND (P.DateRequested = @.DateRequestedOR @.DateRequestedISNULL)
AND (P.DueDate = @.DueDateOR @.DueDateISNULL)
AND (P.ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)
AND P.ProjectManagerID = M.ProjectManagerIDAND P.ProjectStatusID = S.ProjectStatusID

Hope this will help you.

Good luck.

|||

That's it!? That's pretty simple.

Ok. Great.

Thank you (both responses).