[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

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)
0
rayluvs
Asked:
rayluvs
2 Solutions
 
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
 
QlemoDeveloperCommented:
Do you know how to use SQL from VBA?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now