UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Passing Variables To Stored Procedure, SQL Server 2012    
 
   
AnthonyRose
post Mar 17 2017, 05:20 PM
Post#1



Posts: 6
Joined: 17-March 17



Hi,

I am in the process of building a programme with MS Access FE and MS SQL BE. I have written a stored procedure which allows me to ADD/UPDATE. I have also included the ability to return a specific field back to the user when a new record is added. I am now in the process of writing the VBA in the FE to pass the necessary variables to the stored procedure.

My Stored Procedure is this:

CODE
ALTER PROCEDURE [dbo].[p_CreateWO]

(

        @WOID         INTEGER,
    @CustomerID  INTEGER,
    @DeptID    INTEGER,
    @SiteID INTEGER,
    @WODate DATE,
    @WODueDate DATE,
    @WONumber nvarchar(10),
    @WORequestedByID INTEGER,
    @WOPreparedByID INTEGER,
    @WONotes nvarchar(300),
    @WOTotalVAT INTEGER,
    @WOTotalPrice INTEGER,
    @WOSignedForID INTEGER,
    @WODeliveryDate Date,
    @WOCompletedDate Date,
    @StatementType nvarchar(20) = ''
    
  )

AS
DECLARE    @return_value int,
        @NextWO int

EXEC    @return_value = [dbo].[p_GetNewWONumber]
        @NextWO = @NextWO OUTPUT

SELECT    @NextWO as N'@NextWO'
SELECT    'Return Value' = @return_value

    IF @StatementType =  'Insert'
    
    BEGIN

insert into tblWorkOrderHead (CustomerID, DeptID, SiteID, WODate, WODueDate, WONumber, WORequestedByID, WOPreparedByID, WONotes, WOTotalVAT, WOTotalPrice, WOSignedForID, WODeliveryDate, WOCompletedDate) values( @CustomerID, @DeptID, @SiteID, @WODate, @WODueDate, @NextWO, @WORequestedByID, @WOPreparedByID, @WONotes, @WOTotalVAT, @WOTotalPrice, @WOSignedForID, @WODeliveryDate, @WOCompletedDate)    

RETURN @NextWO
END

IF @StatementType =  'Select'

BEGIN

select * from tblWorkOrderHead WHERE WOID = @WOID

END  

IF @StatementType =  'Update'

BEGIN

UPDATE tblWorkOrderHead SET

            DeptID = @DeptID, SiteID = @SiteID, WODate = @WODate, WODueDate = @WODueDate, WORequestedByID = @WORequestedByID, WOPreparedByID = @WOPreparedByID, WONotes = @WONotes, WOTotalVAT = @WOTotalVAT, WOTotalPrice = @WOTotalPrice, WOSignedForID = @WOSignedForID, WODeliveryDate = @WODeliveryDate, WOCompletedDate = @WOCompletedDate

      WHERE WOID = @WOID

END

else IF @StatementType = 'Delete'

BEGIN
DELETE FROM tblWorkOrderHead WHERE WOID = @WOID
END


The VBA code I have written to pass the variables is:

CODE
  Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Dim returnWO As String
    Dim str As String


        cmd.ActiveConnection = "Driver={SQL Server};Server=SERVER;Database=DATABASE;Trusted_Connection=yes;"
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "p_CreateWO"
            'Add the Parameters to the Stored Procedure

cmd.Parameters.Append cmd.CreateParameter("@WOID", adInteger, adParamInput, , Null)
            cmd.Parameters.Append cmd.CreateParameter("@CustomerID", adInteger, adParamInput, , Me.cmbCustomerID)
            cmd.Parameters.Append cmd.CreateParameter("@DeptID", adInteger, adParamInput, , Me.cmbDeptID)
            cmd.Parameters.Append cmd.CreateParameter("@SiteID", adInteger, adParamInput, , Me.cmbSiteID)
            cmd.Parameters.Append cmd.CreateParameter("@WODate", adDBTimeStamp, adParamInput, , Me.txtWODate)
            cmd.Parameters.Append cmd.CreateParameter("@WODueDate", adDBTimeStamp, adParamInput, , Me.txtWODueDate)
            cmd.Parameters.Append cmd.CreateParameter("@WONumber", adInteger, adParamInput, 15, Null)
            cmd.Parameters.Append cmd.CreateParameter("@WORequestedByID", adInteger, adParamInput, , Me.cmbWORequestedByID)
            cmd.Parameters.Append cmd.CreateParameter("@WOPreparedByID", adInteger, adParamInput, , Me.cmbWOPreparedByID)
            cmd.Parameters.Append cmd.CreateParameter("@WONotes", adChar, adParamInput, 50, Me.txtWONotes)
            cmd.Parameters.Append cmd.CreateParameter("@WOTotalVAT", adInteger, adParamInput, , Null)
            cmd.Parameters.Append cmd.CreateParameter("@WOTotalPrice", adInteger, adParamInput, , Null)
            cmd.Parameters.Append cmd.CreateParameter("@WOSignedFOrID", adInteger, adParamInput, , Null)
            cmd.Parameters.Append cmd.CreateParameter("@WODeliveryDate", adDBTimeStamp, adParamInput, , Null)
            cmd.Parameters.Append cmd.CreateParameter("@WOCompletedDate", adDBTimeStamp, adParamInput, , Null)
          
            cmd.Parameters.Append cmd.CreateParameter("@StatmentType", adChar, adParamInput, 20, "Insert")
          
            cmd.Execute
            
            'Capture the @NextWO returned from the stored procedure and return it to a string
            
            returnWO = (cmd.Parameters("@NextWO").Value)


I seem to be passing the variables correctly and that part of the Stored Procedure is working (a new record is added to my table), however, I can't get @NextWO to pass back to my code so that I can display it to the user.

returnWO = (cmd.Parameters("@NextWO").Value)

I then use:

me.txtBox = returnWO

Any advice/help would be very much appreciated.

Thanks
This post has been edited by AnthonyRose: Mar 17 2017, 05:21 PM
Go to the top of the page
 
cheekybuddha
post Mar 17 2017, 06:44 PM
Post#2


UtterAccess VIP
Posts: 9,051
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

I think you have to add the parameter before executing:
CODE
' ...
            cmd.Parameters.Append cmd.CreateParameter("@StatmentType", adChar, adParamInput, 20, "Insert")
            cmd.Parameters.Append cmd.CreateParameter("@NextWO", adVarChar, adParamOutput, 20, Null)
            cmd.Execute
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 17 2017, 06:50 PM
Post#3


UtterAccess VIP
Posts: 9,051
Joined: 6-December 03
From: Telegraph Hill


See here

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AnthonyRose
post Mar 18 2017, 05:11 AM
Post#4



Posts: 6
Joined: 17-March 17



Hi,

I have added the parameter, as you suggested, before the .execute command and now I get the following error:

Procedure or Function has too many arguments specified.

Go to the top of the page
 
AnthonyRose
post Mar 18 2017, 05:19 AM
Post#5



Posts: 6
Joined: 17-March 17



As my @NextWO parameter is coming from another stored procedure [p_GetNewWONumber] which is being called from within the [p_createWO] procedure do I have to also declare the @NextWO as an OUTPUT parameter in my initial parameter list of the [p_createWO] SP?
Go to the top of the page
 
cheekybuddha
post Mar 18 2017, 05:20 AM
Post#6


UtterAccess VIP
Posts: 9,051
Joined: 6-December 03
From: Telegraph Hill


Yes, you probably need to add it to the definition of the Stored Proc.

I don't have much experience of SQLServer SP's, but this is commonly how it's done in other flavours of SP.
CODE
ALTER PROCEDURE [dbo].[p_CreateWO]
(
    @WOID         INTEGER,
    @CustomerID  INTEGER,
    @DeptID    INTEGER,
    @SiteID INTEGER,
    @WODate DATE,
    @WODueDate DATE,
    @WONumber nvarchar(10),
    @WORequestedByID INTEGER,
    @WOPreparedByID INTEGER,
    @WONotes nvarchar(300),
    @WOTotalVAT INTEGER,
    @WOTotalPrice INTEGER,
    @WOSignedForID INTEGER,
    @WODeliveryDate Date,
    @WOCompletedDate Date,
    @StatementType nvarchar(20) = ''
    @NextWO int OUTPUT
)
...

Then remove @NextWO from within the SP defifiniton.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AnthonyRose
post Mar 18 2017, 05:24 AM
Post#7



Posts: 6
Joined: 17-March 17



When you say then remove @NextWO from within the SP definition, what do you mean?
Go to the top of the page
 
cheekybuddha
post Mar 18 2017, 05:30 AM
Post#8


UtterAccess VIP
Posts: 9,051
Joined: 6-December 03
From: Telegraph Hill


Sorry - too many definitions!

I meant to remove it from where it was previously declared in this SP. So you end up with:
CODE
ALTER PROCEDURE [dbo].[p_CreateWO]
(
    @WOID         INTEGER,
    @CustomerID  INTEGER,
    @DeptID    INTEGER,
    @SiteID INTEGER,
    @WODate DATE,
    @WODueDate DATE,
    @WONumber nvarchar(10),
    @WORequestedByID INTEGER,
    @WOPreparedByID INTEGER,
    @WONotes nvarchar(300),
    @WOTotalVAT INTEGER,
    @WOTotalPrice INTEGER,
    @WOSignedForID INTEGER,
    @WODeliveryDate Date,
    @WOCompletedDate Date,
    @StatementType nvarchar(20) = ''
    @NextWO int OUTPUT
)
AS
DECLARE @return_value int   /* ,
        @NextWO int   */

EXEC    @return_value = [dbo].[p_GetNewWONumber]
        @NextWO = @NextWO OUTPUT
...


I just commented it above, so you can see which I mean

--------------------


Regards,

David Marten
Go to the top of the page
 
AnthonyRose
post Mar 18 2017, 05:32 AM
Post#9



Posts: 6
Joined: 17-March 17



Just to clarify things a little.

If i run the following code I get the desired effect (a new record is inserted and it returns to the Query Results/Message window the @NextWO number.

CODE
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[p_CreateWO]
        @WOID = NULL,
        @CustomerID = 2,
        @DeptID = NULL,
        @SiteID = NULL,
        @WODate = NULL,
        @WODueDate = NULL,
        @WONumber = NULL,
        @WORequestedByID = NULL,
        @WOPreparedByID = NULL,
        @WONotes = NULL,
        @WOTotalVAT = NULL,
        @WOTotalPrice = NULL,
        @WOSignedForID = NULL,
        @WODeliveryDate = NULL,
        @WOCompletedDate = NULL,
        @StatementType = N'insert'

SELECT    'Return Value' = @return_value

GO


it also returns 2 @return_value results (all the same value as the @NextWO).
Go to the top of the page
 
AnthonyRose
post Mar 18 2017, 05:46 AM
Post#10



Posts: 6
Joined: 17-March 17



That did it! Thank you.

2 questions...

1) Am I declaring too many return variables? When I now run the SP in MSSQL Management Studio it returns 4 instances of the New WO number 2 [@NextWO] and 2 [Return Value]. Don't know if this is normal or if I'm 'over doing' it in my code.

2) If I need to also return the @WOID back to the user, would I have to declare it as an OUTPUT variable in the definitions just the same? How would this fit into my initial SP?

Thanks for your support smile.gif
Go to the top of the page
 
cheekybuddha
post Mar 18 2017, 05:55 AM
Post#11


UtterAccess VIP
Posts: 9,051
Joined: 6-December 03
From: Telegraph Hill


OK,

I have just read your SP a little more carefully - previously I just assumed it was doing what you wanted.

Just to make sure I understand correctly:

Your SP always returns at least 2 recordsets, and 3 when @StatementType = 'SELECT':
1. SELECT @NextWO as N'@NextWO' (Probably unnecessary to use N prefix here)
2. SELECT 'Return Value' = @return_value
3. select * from tblWorkOrderHead WHERE WOID = @WOID

You have a choice of two methods to return values to your calling code:
Either use OUTPUT parameters,
Or SELECT the values/recordsets.
Parameter, you pass and retrieve via the parameters collection
Selects, you retrieve by iterating through the returned recordsets

{Have just read your latest reply}

Personally, I would use OUTPUT params for @return_value and @NextWO, and just select the query when @StatementType = 'Select'

(Actually, I'm confused as to the difference between @return_value and @NextWO)

>> If I need to also return the @WOID back to the user, would I have to declare it as an OUTPUT variable in the definitions just the same? <<
You use adParamInputOutput instead of adParamInput. (See here)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th June 2017 - 05:24 AM