限制在SQL视图中使用DECLARE(Restriction to use DECLARE in a SQL view)

我有一个sql查询，我需要放入视图。我把它作为一个存储过程工作，但我的要求是它是一个视图。这意味着我不能像我在存储过程中那样声明动态SQL。

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); select @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.Data_Term) from [UD_FldValues] t inner join Surgery p on t.Ud_Form_Id = p.Ud_Form_Id where t.Data_term!='' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''); set @query = 'SELECT Tran_Id,Section_Id,SubSection_Id,R_Id,Hospital_Id,Surgeon_Id,Procedure_ICD,Procedure_Details,DtSurgery_TimeIn,DtSurgery_TimeOut,Intra_oper_compl_Id, p.Discharge_Date,Discharge_Status_Id,Entered_By,Entered_Date,p.Status,p.Ud_Form_Id,p.Complication_ICD,p.Complication_Name, Procedure_Name, ' + @cols + ' from ( select t.Tran_Id , t.[R_Id] , p.Ud_Form_Id ,p.Procedure_Name ,p.Section_Id ,p.SubSection_Id ,p.Hospital_Id ,p.Surgeon_Id ,p.Procedure_ICD ,p.Procedure_Details ,p.DtSurgery_TimeIn ,p.DtSurgery_TimeOut ,p.Intra_oper_compl_Id ,p.Discharge_Date ,p.Discharge_Status_Id ,p.Entered_By, p.Entered_Date ,p.Status ,p.Complication_ICD ,p.[Complication_Name] , case when (t.Data_Type=''Text'') THEN t.Text_Value Else Convert(varchar(MAx),Num_Value) END as txtvl ,t.Data_term from Surgery p left outer join [UD_FldValues] t on t.Ud_Form_Id = p.Ud_Form_Id and t.Tran_Id=p.Surgery_Id left outer join UD_Formmap fm ON fm.Ud_Form_Id = p.Ud_Form_Id where fm.module_id = 1 ) x pivot ( min(txtvl) for Data_Term in (' + @cols + ') ) p ' exec(@query)

请帮我找个替代方法，但不要存储程序。我需要它

I have a sql query that I need to put into a view. I have it working as a stored procedure but my requirement is for it to be a View. This means I cant declare dynamic SQL as I have done in my stored procedure.

Please help me find an alternate way, but not store procedures. I need it in view

最满意答案

对于一个视图，它需要是一个select语句，

你的@cols语句返回一列列，这在你的主选择语句中使用了两次。因此用填充它的查询替换主查询中的@cols应该可以工作。

设置@query，然后exec（@query）不是必需的，所以你只需要整理并确保正确数目的括号就位。

它应该看起来像这样。

SELECT Tran_Id ,Section_Id ,SubSection_Id ,R_Id,Hospital_Id ,Surgeon_Id,Procedure_ICD ,Procedure_Details ,DtSurgery_TimeIn ,DtSurgery_TimeOut ,Intra_oper_compl_Id ,p.Discharge_Date ,Discharge_Status_Id ,Entered_By,Entered_Date ,p.Status,p.Ud_Form_Id ,p.Complication_ICD ,p.Complication_Name ,Procedure_Name, (select STUFF((SELECT distinct ',' + QUOTENAME(t.Data_Term) from [UD_FldValues] t inner join Surgery p on t.Ud_Form_Id = p.Ud_Form_Id where t.Data_term!='' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'');)) from ( select t.Tran_Id , t.[R_Id] , p.Ud_Form_Id ,p.Procedure_Name ,p.Section_Id ,p.SubSection_Id ,p.Hospital_Id ,p.Surgeon_Id ,p.Procedure_ICD ,p.Procedure_Details ,p.DtSurgery_TimeIn ,p.DtSurgery_TimeOut ,p.Intra_oper_compl_Id ,p.Discharge_Date ,p.Discharge_Status_Id ,p.Entered_By, p.Entered_Date ,p.Status ,p.Complication_ICD ,p.[Complication_Name] , case when (t.Data_Type=''Text'') THEN t.Text_Value Else Convert(varchar(MAx),Num_Value) END as txtvl ,t.Data_term from Surgery p left outer join [UD_FldValues] t on t.Ud_Form_Id = p.Ud_Form_Id and t.Tran_Id=p.Surgery_Id left outer join UD_Formmap fm ON fm.Ud_Form_Id = p.Ud_Form_Id where fm.module_id = 1 ) x pivot ( min(txtvl) for Data_Term in ( select STUFF((SELECT distinct ',' + QUOTENAME(t.Data_Term) from [UD_FldValues] t inner join Surgery p on t.Ud_Form_Id = p.Ud_Form_Id where t.Data_term!='' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'');) ) p

for a view it needs to be a single select statement,

your @cols statement returns a list of columns, this is used twice within your main select statement. so replacing @cols in your main query with the query that populates this should work.

set @query and then exec(@query) isnt necessary either so you just have to tidy up and make sure the right number of brackets are in place.

and it should look something like this.

更多推荐