Solved

MS SQL store procedure to calculate and return result

Posted on 2016-11-13
6
76 Views
Last Modified: 2016-11-14
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
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41885380
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
 

Author Comment

by:rayluvs
ID: 41885383
How would you call it from vba?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41885397
Do you know how to use SQL from VBA?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41885430
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41885457
@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
 

Author Closing Comment

by:rayluvs
ID: 41886316
Thanx!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question