Geek asks and answers

How to use openrowset to execute a stored procedure with parameters

I create a stored procedure that receives some parameters, and in turn these parameters are sent to another stored procedure that I call from openrowset, but I get some syntax errors.

CREATE PROCEDURE UpdatePrevFYConfigData 
    -- Add the parameters for the stored procedure here

        @startDate datetime,
        @endDate datetime,
        @productGroup varchar(8000) = 'All',
        @projectType varchar(500) = 'All',
        @businessUnit nvarchar(50) = 'All',
        @developmentLocation nvarchar(100) = 'All'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @start varchar(50)
declare @end varchar(50) 

set @start = cast(@startDate as varchar(40))
set @end = cast(@endDate as varchar(40))

    -- Insert statements for procedure here
select round(avg(a.DeviationDeadline),2) as DeviationDeadline, 
       round(avg(a.DeviationDefinition),2) as DeviationDefinition,
       round(avg(a.DeviationRDCosts),2) as DeviationRDCosts,
       round(avg(a.FunctionsAdded) + avg(a.FunctionsDeleted),2) as NotRealizedFuncs, 
       round(avg(a.DeviationPM2000Aufwand),2) as DeviationPM200Aufwand,
       round(avg(b.Defect),2) as Defect
       into #tempTable 
from openrowset('SQLNCLI', 
                'Server=.\sqlexpress;Trusted_Connection=yes;',  
                'SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.SelectScorecardGraphData
                    '''+@start+''',
                    '''+@end+''',
                    '''+@productGroup+''',
                    '''+@projectType+''',
                    ''1'',
                    ''0'',
                    ''All'',
                    ''Current'',
                    '''+@businessUnit+''',
                    '''+@developmentLocation+'''
                ') as a,

    openrowset('SQLNCLI', 'Server=.\sqlexpress;Trusted_Connection=yes;',  'SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.GetSPCDefectDistributionData
'''+cast(@startDate as varchar(40))+''',
'''+cast(@endDate as varchar(40))+''',
''Defect'',
'''+@projectType+''',
'''+@productGroup+''',
'''+@businessUnit+''',
'''+@developmentLocation+'''') as b


update dbo.EA_ProcessScorecard_Config_Tbl
set EPC_Deviation = case EPC_Metric
    when 'PM200' then (select  DeviationDefinition from #tempTable)
    when 'PM300' then (select  DeviationDeadline from #tempTable)
    when 'Cost'  then (select  DeviationRDCosts from #tempTable)
    when 'PM150' then (select  DeviationPM200Aufwand from #tempTable)
    when 'Defect' then (select Defect from #tempTable)
    when 'Funcs' then (select NotRealizedFuncs from #tempTable)
END
where EPC_Description = 'PrevFY' and EPC_FYYear = '0'

drop table #tempTable

END 

GO

I cannot create it, and I get an error:

Msg 102, Level 15, State 1, Procedure UpdatePrevFYConfigData, 
Line 38 Incorrect syntax near '+'.

... but if I use hard-coded values ​​for the parameters that it works!

Please, help!

+3
source share
2 answers

OPENROWSET OPENDATASOURCE , , , . , . OPENROWSET OPENDATASOURCE . . , , , EXEC. ( )

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT *
FROM OPENROWSET(''SQLNCLI'',''server=.\sqlexpress;Trusted_Connection=yes'',''SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.SelectScorecardGraphData ''''' + cast(@param1 as varchar(10)) + ''''',''' + cast(@param2 as varchar(n)) ''')'
EXEC @sqlCommand

... , . ,

+7
    -- FOR USING OPENROWSETS
    EXEC sp_configure 'Ad Hoc Distributed Queries'
        ,1

    RECONFIGURE

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'INSERT INTO #TABLESIZESYEAR SELECT NULL AS [TABLE NAME], * FROM OPENROWSET 
                    (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',''set fmtonly off EXEC one.[dbo].[InvestigateDataGrowthByYearAndClient] @pDATECOLUMN =' + @YEARCOLUMN + ' ,
                        @pTABLENAME = ' + @TABLENAME + ' WITH RESULT SETS(
                        ([YEAR NAME] NVARCHAR(5) NULL
                                , [NUMBER OF ROWS] CHAR(11)    
                                , [RESERVED SPACE] VARCHAR(18) 
                                , [DATA SPACE] VARCHAR(18)    
                                , [INDEX SIZE] VARCHAR(18)    
                                , [UNUSED SPACE] VARCHAR(18) )
                    )
                        ;'') '

    DECLARE @ParmDefinition NVARCHAR(500) = '@pDATECOLUMN NVARCHAR(20)
                            ,@YEARCOLUMN NVARCHAR(20)
                            ,@pTABLENAME NVARCHAR(60)';

    EXECUTE sp_executesql @sql
        ,@ParmDefinition
        ,@YEARCOLUMN = @YEARCOLUMN
        ,@pDATECOLUMN = @YEARCOLUMN
        ,@pTABLENAME = @TABLENAME
+1

More articles:


All Articles