how to use openrowset to execute a stored procedure with parameters

Question

I'm creating a stored procedure which gets some parameters and in turn these parameters are sent to another stored procedure which I'm calling from openrowset but I'm getting 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'm not able to create it and I get the error message:

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

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

Please help!

Sarilda

Answer 1

Both OPENROWSET and OPENDATASOURCE should be used only for accessing external data for, let's say, quick and dirty solutions, or when it is not possible to configure a permanent linked server. These functions do not provide all of the functionality available from a linked server. The arguments of OPENROWSET and OPENDATASOURCE do not support variables. They have to be specified as string-literal. If variables need to be passed in as arguments to these functions, a query string containing these variables can be constructed dynamically and executed using the EXEC statement. Similar to (not syntax checked)

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

And so on... Hope that helps. Kind regards, Stefan

Holler

No use if the point of using OPENROWSET to call a stored proc was to put the results into a SELECT statement. I guess it simply cannot be done and the stored proc being called needs to be changed into a function. This is the solution I ended up settling on. – Azerbaijan

Answer 2

    -- 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

Roseliaroselin