?
Solved

MS SQL store procedure to calculate and return result

Posted on 2016-11-13
6
Medium Priority
?
86 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 29

Accepted Solution

by:
Pawan Kumar earned 2000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

764 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