MS SQL store procedure to calculate and return result

We want to use or create a store procedure sending a value so it can execute a series of instructions based on that value and return result value.  Can EE give an example of the how can it be done? (Note: we plan to make call of this store procedure thru VBA, VB, VB6)
rayluvsAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
A stored procedure is collection of SQL code that you save and you can reuse the code again and again.

Sample below--If you can provide details we can write the same for you.

  
--


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*

**  -- Author 
**  -- Purpose

*/

CREATE PROCEDURE ProcName 
(	
	 @Param1 INT
	,@Param2 INT
	,@Param3 INT OUTPUT 
	
)
AS
BEGIN
	
	SET NOCOUNT ON;

    SELECT * FROM YourTableName WHERE ColName = @Param1

	SELECT @Param3 = @Param2 + @Param3

	RETURN 

END
GO


--

Open in new window


If you need more details please refer -

http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedure-in-Sql

https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

Hope it helps!!
0
 
rayluvsAuthor Commented:
How would you call it from vba?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Do you know how to use SQL from VBA?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Try..

--

Set ConnectionObject = New ADODB.Connection
ConnectionObject.Open "Connection String"

Set MyCommand = New ADODB.Command

With MyCommand 

    .ActiveConnection = Me.Connection
    .CommandText = "YourProcName"

    .CommandType = adCmdStoredProc
    .CommandTimeout = 0

    .Parameters.Append .CreateParameter("@Param1", adInteger, adParamInput, 5)
    .Execute

End With

--

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@rayluvs - Looks like we've answered this question as you asked it, so if you have a more detailed question please ask it in a separate question.  Thanks.
0
 
rayluvsAuthor Commented:
Thanx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.