Solved

MS SQL store procedure to calculate and return result

Posted on 2016-11-13
6
56 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
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 69

Expert Comment

by:Qlemo
ID: 41885397
Do you know how to use SQL from VBA?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 65

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Statements 11 34
Dlookup MSACCESS 5 24
Query Syntax 17 32
Excel VBA Find Lowest Row number in any selection 8 21
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

785 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