我可以在Sql Server 2008中使用存储过程创建视图 [英] Can I Create View Using Stored Procedure In Sql Server 2008

问题描述

大家好，



i有一张工作台，我有，

 -------------------------- 
 Jobid |单位| InsertionDate | 
 -------------------------- 
 1 U1 08/26/2014 
 
 2 U2 08/28/2014 
 
 3 U1 08/29/2014 
 
 4 U2 08/29/2014 

我根据单位将存储过程写入最大（日期）记录。



Stroed程序：

 ALTER Proc [dbo]。[Sp_GetEtsJobs] 
（
 @UnitID int 
）
 
 as 
 
  -  Sp_GetEtsJobs 43 
 
 DECLARE @MaxDate DATE 
 SELECT @MaxDate =来自tbl_etsjob的最大（JobDate）
 
  - 选择*来自tbl_etsworkshop 
 
开始
 
选择
 j.JobID，
 u.UnitID，
 u.UnitName，
 d .UnitDepartmentName，
 j.JobDate，
 j.UnitStatus = 1时的情况然后'活动'
当j.UnitStatus = 2然后'InActive'
结束为'UnitStatus'， 
 r.RigName，
 w.WellName，
 j.JobProcess，
 j.NPSJob，
 j.CrewStatus，
 j.Operator，
 dbo.fn_GetEtsJobStatus（j.Jo bID）as'JobStatus'，
 CASE WHEN dbo.fn_GetEtsJobStatus（j.JobID）= 1那么'即将开始'
 WHEN dbo.fn_GetEtsJobStatus（j.JobID）= 2那么'正在'运行'
 WHEN dbo.fn_GetEtsJobStatus（j.JobID）= 3那么'完成'
当dbo.fn_GetEtsJobStatus（j.JobID）= 4然后'WorkShop'
当dbo.fn_GetEtsJobStatus（j.JobID）= 5然后'返回'
当dbo.fn_GetEtsJobStatus（j.JobID）= 6那么'Yard'
结束为'状态'，
 dbo.fn_GetEtsJobStatusDate（j.JobID）为'JobStatusDate' 
来自tbl_etsjob j 
内部联接UnitSetup u on u.UnitID = j.UnitID 
内部联接UnitDepartment d on d.UnitDepartmentID = j.DepartmentID 
 left join RigSetup r on r。 RigID = j.Rig 
离开加入WellSetup w on w.WellID = j.Well 
其中JobDate = @MaxDate和u.UnitID = @UnitID 
 
 end 

实际上，我没有收到任何错误。



但是，我是只获取一条记录。



我想知道，我可以使用这个存储过程创建视图，

这样，我可以填充视图n从这些视图中获取值。



因为，如果我有100个单位，那么这个存储将运行100次。

我想在这个存储过程的帮助下填充视图。



是可能的。



请建议。



谢谢

解决方案

请浏览以下链接。

使用存储过程创建视图 [ ^ ]



希望它满足您的要求。

我认为通过程序创建视图是个坏主意。如果您可以将UnitID列表传递给过程怎么办？更改下面给出的程序。

  ALTER   Proc  [dbo]。[Sp_GetEtsJobs] 
（
  @ UnitIDList   VARCHAR （ 4000 ）
）
  as  
   -    Sp_GetEtsJobs '43，44,45,46' 
  DECLARE   @ MaxDate   DATE  
  SELECT   @ MaxDate  = Max（JobDate）来自 tbl_etsjob 
   -    select * from tbl_etsworkshop  
 开始 
  DECLARE   @ tmpUnits  表（UnitID  INT ）
  DECLARE   @ tokan   VARCHAR （ 500 ）
  DECLARE   @ index   INT  
 
  SELECT   @ index  =  1 ， @ tokan  = '  ' 
 
  WHILE  LEN（ @ UnitIDList ）> =  @ index  
  BEGIN  
  IF  substring（ @ UnitIDList ， @ index ， 1 ）= ' ，' 
  BEGIN  
  INSERT   INTO  < span class =code-sdkkeyword> @ tmpUnits （UnitID） SELECT   @ tokan  
  SELECT   @ tokan  = ' ' 
  END  
  ELSE  
  BEGIN  
  SELECT   @tokan  =  @ tokan  + substring（ @ UnitIDList ， @ index ， 1 ）
  END  
  SELECT   @ index  =  @index  +  1  
  END  
 
  IF   @ tokan <> ' ' 
  BEGIN  
  INSERT   INTO   @ tmpUnits （ UnitID） SELECT   @ tokan  
  END  
 
 选择 
 j.JobID，
 u.UnitID，
 u.UnitName，
 d.UnitDepartmentName，
 j.JobDate，
  case  何时 j .UnitStatus =  1  然后 ' 活动' 
 当 j.UnitStatus =  2  然后 '  InActive' 
  end   as  '  UnitStatus'，
 r.RigName，
 w.WellName，
 j.JobProcess，
 j.NPSJob， 
 j.CrewStatus，
 j.Operator，
 dbo.fn_GetEtsJobStatus（j.JobID） as  '  JobStatus'，
  CASE   WHEN  dbo.fn_GetEtsJobStatus（j.JobID）=  1  那么 ' 即将开始' 
  WHEN  dbo.fn_GetEtsJobStatus（j.JobID）=  2  那么 '  正在运行' 
  WHEN  dbo.fn_Ge tEtsJobStatus（j.JobID）=  3  那么 ' 完成' 
  WHEN  dbo.fn_GetEtsJobStatus（j.JobID）=  4  那么 '  WorkShop' 
  WHEN  dbo.fn_GetEtsJobStatus（j.JobID）=  5   THEN  ' 返回' 
  WHEN  dbo.fn_GetEtsJobStatus（j.JobID）=  6  那么 '  Yard' 
  END   AS  '  Status'，
 dbo.fn_GetEtsJobStatusDate（j.JobID） as  '  JobStatusDate' 
 来自 tbl_etsjob j 
 内部  join  UnitSetup u  on  u.UnitID = j.UnitID 
  inner   join  UnitDepartment d  on  d。 UnitDepartmentID = j.DepartmentID 
  left   join  RigSetup r  on  r.RigID = j.Rig 
  left   join  WellSetup w < span class =code-keyword> on  w.WellID = j.Well 
 其中 JobDate =  @MaxDate  和 u.UnitID  IN （ SELECT  UnitID  FROM   @ tmpUnits ）
  end  

Hi guys,

i have a table for job, where i have,

--------------------------
Jobid| Unit| InsertionDate|
--------------------------
1       U1    08/26/2014

2       U2    08/28/2014

3       U1    08/29/2014

4       U2    08/29/2014

I wrote the stored procedure to the max(date) record based on unit.

Stroed Procedure:

ALTER Proc [dbo].[Sp_GetEtsJobs] 
(
@UnitID int
)

as 

-- Sp_GetEtsJobs 43

DECLARE @MaxDate DATE
SELECT @MaxDate = Max(JobDate) from tbl_etsjob

-- select * from tbl_etsworkshop

Begin

	Select 
		j.JobID,
		u.UnitID,
		u.UnitName,
		d.UnitDepartmentName,
		j.JobDate,
		case when j.UnitStatus = 1 then 'Active'
			 when j.UnitStatus = 2 then 'InActive'
			 end as 'UnitStatus',
		r.RigName,
		w.WellName,
		j.JobProcess,
		j.NPSJob,
		j.CrewStatus,
		j.Operator,
		dbo.fn_GetEtsJobStatus(j.JobID) as 'JobStatus',
		CASE WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 1 THEN 'About to Start'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 2 THEN 'Running'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 3 THEN 'Done'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 4 THEN 'WorkShop'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 5 THEN 'Returning'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 6 THEN 'Yard'
			 END AS 'Status',
		dbo.fn_GetEtsJobStatusDate(j.JobID) as 'JobStatusDate'
	from tbl_etsjob j
	inner join UnitSetup u on u.UnitID = j.UnitID
	inner join UnitDepartment d on d.UnitDepartmentID = j.DepartmentID
	left join RigSetup r on r.RigID = j.Rig
	left join WellSetup w on w.WellID = j.Well
	where JobDate = @MaxDate and u.UnitID = @UnitID
	
end

Actually, i'm not getting any error.

but, i'm fetching only one record.

I want to know, can i create view using this stored procedure,
so that, i can fill the view n fetch values from this views.

Because, if i have 100 units, then this stored will run 100 times.
I want to fill the view with the help of this stored procedure.

is it possible.

Please, suggest.

Thanks

解决方案

Please go through the below link.
Creating a view using stored procedure[^]

Hope it satisfies your requirement.

I think creating view via procedure is a bad idea. What if you can pass list of UnitID to procedure. Change your procedure given below.

ALTER Proc [dbo].[Sp_GetEtsJobs] 
(
@UnitIDList VARCHAR(4000)
)
as 
-- Sp_GetEtsJobs '43,44,45,46'
DECLARE @MaxDate DATE
SELECT @MaxDate = Max(JobDate) from tbl_etsjob
-- select * from tbl_etsworkshop
Begin
	DECLARE @tmpUnits TABLE (UnitID INT)
	DECLARE @tokan VARCHAR(500)
	DECLARE @index INT

	SELECT @index = 1, @tokan = ''

	WHILE LEN(@UnitIDList) >= @index
	BEGIN
		IF substring(@UnitIDList, @index, 1) = ','
			BEGIN
				INSERT INTO @tmpUnits(UnitID) SELECT @tokan
				SELECT @tokan = ''
			END
		ELSE
			BEGIN
				SELECT @tokan = @tokan + substring(@UnitIDList, @index, 1)
			END
		SELECT @index = @index + 1
	END

	IF @tokan <> ''
		BEGIN
			INSERT INTO @tmpUnits(UnitID) SELECT @tokan
		END

	Select 
		j.JobID,
		u.UnitID,
		u.UnitName,
		d.UnitDepartmentName,
		j.JobDate,
		case when j.UnitStatus = 1 then 'Active'
			 when j.UnitStatus = 2 then 'InActive'
			 end as 'UnitStatus',
		r.RigName,
		w.WellName,
		j.JobProcess,
		j.NPSJob,
		j.CrewStatus,
		j.Operator,
		dbo.fn_GetEtsJobStatus(j.JobID) as 'JobStatus',
		CASE WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 1 THEN 'About to Start'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 2 THEN 'Running'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 3 THEN 'Done'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 4 THEN 'WorkShop'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 5 THEN 'Returning'
			 WHEN dbo.fn_GetEtsJobStatus(j.JobID) = 6 THEN 'Yard'
			 END AS 'Status',
		dbo.fn_GetEtsJobStatusDate(j.JobID) as 'JobStatusDate'
	from tbl_etsjob j
	inner join UnitSetup u on u.UnitID = j.UnitID
	inner join UnitDepartment d on d.UnitDepartmentID = j.DepartmentID
	left join RigSetup r on r.RigID = j.Rig
	left join WellSetup w on w.WellID = j.Well
	where JobDate = @MaxDate and u.UnitID IN (SELECT UnitID FROM @tmpUnits)
end

这篇关于我可以在Sql Server 2008中使用存储过程创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！