我正在开发一个复杂的SQL Server参数化存储过程。它需要一个参数
@StudyID
作为输入。我需要将此参数的值传递给
dplyr filter
子句,以便只返回选定studyID的数据。我的存储过程如下:
ALTER PROCEDURE [dbo].[spCodeMeans]
-- Add the parameters for the stored procedure here
@StudyID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @sStudy varchar(50)
Set @sStudy = Convert(Varchar(50),@StudyID)
Declare @inquery nvarchar(max) = N'Select
c.StudyID, c.RespID, c.ProductNumber, c.ProductSequence, c.BottomScaleValue,
c.BottomScaleAnchor, c.TopScaleValue, c.TopScaleAnchor, c.StudyDate,
c.DayOfWeek, c.A, c.B, c.C, c.D, c.E, c.F,
c.DependentVarYN, c.VariableAttributeID, c.VarAttributeName, c.[1] as c1,
c.[2] as c2, c.[3] as c3, c.[4] as c4, c.[5] as c5, c.[6] as c6, c.[7] as c7, c.[8] as c8
from ClosedStudyResponses c
--Sensory Value Attributes only for mean and standard deviation analytics.
where VariableAttributeID = 1
and c.StudyID =' +@sStudy ;
BEGIN TRY
--Insert into CodeMeans
exec sp_execute_external_script
@language = N'R',
@script = N'
library(dplyr)
OutputDataSet <- data.frame(InputDataSet) %>%
group_by (StudyID, ProductNumber) %>%
summarise_all(.funs=c(mean, sd)) %>%
filter(StudyID == @StudyID) %>%
setNames(c("StudyID","ProductNumber",
paste0("c",1:8, "_mean"),
paste0("c",1:8, "_sd")))
',
@params = N'@StudyID int',
@input_data_1 = @inquery,
@output_data_1 = N'OutputDataSet'
WITH RESULT SETS(("StudyID" int, "ProductNumber" int, "c1_mean" int,
"c2_mean" int, "c3_mean" int,
"c4_mean" int, "c5_mean" int, "c6_mean" int, "c7_mean" int, "c8_mean" int,
"c1_sd" int, "c2_sd" int,
"c3_sd" int, "c4_sd" int, "c5_sd" int, "c6_sd" int, "c7_sd" int, "c8_sd" int
));
END TRY
BEGIN CATCH
THROW;
END CATCH
Select * from CodeMeans;
END
如何确保参数正确地传递到filter子句中?如果另一个R包更适合这个用例,那么建议使用哪一个?
