CODEWITHSUNDEEP
sqlsql-serverrrodbc
Jon
Jon

Reputation: 89

Fetching Results from Stored Procedure R

I'm wondering how one would fetch results from a stored procedure in R. I'm currently using RStudio with the RODBC package and am using 

sqlQuery(conn, 'exec sp_name vars')

as my query. The stored procedure is supposed to return back an integer representing the new row number the data is generated on. How would I go about this? At the moment I get -2 or character(0) back on my executions.

Just trying to return a number and store it R side.

Stored Procedure

USE [Internship]
GO

/****** Object:  StoredProcedure [dbo].[pCreateKMeanTest]    Script Date: 7/20/2017 12:51:28 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER procedure [dbo].[pCreateKMeanTest] 
    (@DataSetName varchar(255)
    ,@SQLStatement varchar(5000) 
    ,@ColumnUsed varchar(2900)
    ,@TotClusters int )

as

/*
exec pCreateKMeanTest 
     @DataSetName = '[dbo].[vAccountMemberProductDetailWithBehavior]'
    ,@SQLStatement = 'select [demo_age], Profit_PnlAmt, Census_HomeNearestBranchDistance from [dbo].[vAccountMemberProductDetailWithBehavior]'
    ,@ColumnUsed = 3
    ,@TotClusters = 5
*/


begin transaction

insert into dbo.KMean
    (ExecutedDateTime
    ,DataSetName
    ,SQLStatement
    ,ColumnUsed
    ,TotClusters)
select 
     getdate()
    ,@DataSetName
    ,@SQLStatement
    ,@ColumnUsed
    ,@TotClusters

select max(TestID) from dbo.KMean (nolock)

commit transaction



GO

Upvotes: 1

Views: 1925

Answers (1)

Parfait
Parfait

Reputation: 107567

As commented and since it may help future readers:

For SQL Server stored procedures, add a SET NOCOUNT ON to avoid the row results output that may interfere with RODBC's sqlQuery which expects table/query data to be returned.

And generally for most stored procedures, move any SELECT statement outside a transaction block that is executing an action query (i.e., UPDATE, INSERT INTO) if needing to return actual data.

Upvotes: 4

Related Questions