Menu
Can anyone give code to call a SQL server Stored Procedure with Parameter in R?
This is all I have and it is not working:
dbhandle <- odbcDriverConnect('driver={SQL Server};server=SWD-CHISSIS01;database=GlobalRiskManagement;trusted_connection=true')
data <-sqlQuery(dbhandle, "EXEC my_Stored_Proc @jyear=2013, @ddate=9/25/2013")
odbcCloseAll()
Thank you.
558
Use executeStoredProcedure to execute a stored procedure defined as an StoredProcedure object. Call this function only when executing a stored procedure from R code.
Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.
An R stored procedure is basically an R function that can be used inside SQL Server. In this tip we will use RStudio to create and test our R code and deploy it to SQL Server as a stored procedure. In order to create a stored procedure inside RStudio, we need to install and use the sqlutils R package.
Creating a SQL Stored Procedure with Parameters. To create a stored procedure with parameters using the following syntax: CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) AS; See details and examples below; SQL Server Query to Turn into a Stored Procedure. Below is the query we want to use to create the stored procedure.
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on ...
SQL Server Stored Procedure using NULL as Default Parameter In most cases it is always a good practice to pass in all parameter values, but sometimes it is not possible. So in this example we use the NULL option to allow you to not pass in a parameter value.
Just to clarify, the in the code above, the **'s should not be included in the code so it should say: data <-sqlQuery(dbhandle, "set nocount on\nEXEC my_Stored_Proc @jyear=2013, @ddate=9/25/2013")
This works for me. Thanks to all who replied.
63
dbhandle <- odbcDriverConnect('driver={SQL Server};server=SWD-CHISSIS01;database=GlobalRiskManagement;trusted_connection=true')
data <-sqlQuery(dbhandle, "**set nocount on\n**EXEC my_Stored_Proc @jyear=2013, @ddate=9/25/2013")
odbcCloseAll()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
