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.Func…

Accepted Answer

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

how to use openrowset to execute a stored procedure with parameters

Solution

Problem