1

I am trying to execute the following stored procedure

ALTER PROCEDURE [dbo].[sp_zJDEAB_SSID_Update]
    (@BuyerNo INT, @SSCustID INT, @ReturnText VARCHAR(10) OUT)
AS
SET NOCOUNT ON
BEGIN TRY
    UPDATE [dbo].[crp_F0101]
    SET [ABURRF] = CAST(@SSCustID AS CHAR(15))
    WHERE ABAN8 = @BuyerNo

    SELECT 
        @ReturnText = CASE 
                         WHEN CAST(ab.ABURRF AS INT) = @SSCUSTID
                            THEN 'Updated'
                            ELSE 'Update failed'
                      END
    FROM
        dbo.crp_F0101 ab
    WHERE
        ABAN8 = @BuyerNo
END TRY
BEGIN CATCH
    SELECT @ReturnText = 'sp Error'
END CATCH

from the following SSRS function:

Public Shared Dim UpdateText as String
Public Function UpdateF0101(JDEBuyerABNo as Integer, SSCustID as Integer) as String
    If (JDEBuyerABNo>0 and SSCustID>0) 
    then
    sp_zJDEAB_SSID_Update(Fields!JDE_BuyerABNo.Value, Fields!SS_CustID.Value, UpdateText output)    
     Else UpdateText = "No"
     End If
     Return UpdateText
End Function

I have tried several different format but the call to my stored procedure always results in a custom code error BC30451 (not declared).

What is the correct syntax for call a stored procedure that exists in the same database as the data pull for the report's grid?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MickB
  • 41
  • 2
  • 1
    I've never actually tried this but I suspect you would have to create a connection to the database then a command object just as you would if you where writing the function in VB. I don't think you can use the report's connection. However, if you explain at what point the SP should execute, there might be a much simpler way to d it, such as calling it from the report's dataset. – Alan Schofield Nov 13 '18 at 00:04
  • 1
    Hey, it's been a while since I wrote an SSRS report, but I don't think it's possible to do this. Just remember that the report is not directly connected to the database and only operates on data that is pulled through the connector. I might be wrong, but it's kind of philosophically incorrect for a report to update data, much as we'd like to on occasion. I'll see if I can dream up a way of doing it though. Good luck with this, interesting question! – MikeAinOz Nov 13 '18 at 00:04
  • I think the update text part should be part of your dataset as a separate UPDATE query before your data query. Updating SQL server from VB in SSRS through an SSMS SP seems overly complicated. – Hannover Fist Nov 13 '18 at 00:40
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Nov 13 '18 at 05:52
  • Seems it was not possible to call the sp directly in the ssrs custom vb code . From your custom code , obviously , you do not declare the sp_zJDEAB_SSID_Update in your code.(as function name or whatever) You could try to use the sp in the dataset query , call the stored procedure is not recommend in report . check more detailed information at : [SSRS call stored procedure using ReportItems! textbox. Maybe custom report code?](https://stackoverflow.com/questions/14587193/ssrs-call-stored-procedure-using-reportitems-textbox-maybe-custom-report-code) – Mitarai Queen Jan 02 '19 at 02:43

0 Answers0