1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
= Stored Procedures in MSSQL
This guide documents the workaround implemented to allow executing stored procedures
in MSSQL, as well as getting the value of output variables.
== Simple Execution
The following stored procedure is used as an example:
CREATE PROCEDURE dbo.SequelTest(
@Input varchar(25),
@Output int OUTPUT
)
AS
SET @Output = LEN(@Input)
RETURN 0
Execute it as follows:
DB.call_mssql_sproc(:SequelTest, {args: ['Input String', :output]})
Use the +:output+ symbol to denote an output variable. The result will contain a
hash of the output variables, as well as the result code and number of affected rows:
{:result => 0, :numrows => 1, :var1 => "1"}
Output variables will be strings by default. To specify their type, include the
SQL type:
DB.call_mssql_sproc(:SequelTest, {args: ['Input String', [:output, 'int']]})
Result:
{:result => 0, :numrows => 1, :var1 => 1}
Output variables will be named +var#{n}+ where n is their zero indexed position
in the parameter list. To name the output variable, include their name:
DB.call_mssql_sproc(:SequelTest, {args: ['Input String', [:output, nil, 'Output']]})
Result:
{:result => 0, :numrows => 1, :output => "1"}
|
