# MSSQL 2005 Stored Procedure?

**URL:** <https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930>\
**Category:** Ruby\
**Created:** [March 14, 2008, 2:38pm UTC](https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930 "2008-03-14T14:38:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![talex83](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/54ee81/32.png) [@talex83](https://www.ruby-forum.com/u/talex83)\
**Post date:** [March 14, 2008, 2:38pm UTC](https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930/1 "2008-03-14T14:38:47Z")

</div>

I want to programmig in Ruby… but have some problems.

1. Can anyone help me with the syntax for retriving an ‘out’  
variable from an MSSQL2005 Stored Procedure?

2. Can anyone help me with the syntax for retriving 3 recordset  
from an MSSQL2005 Stored Procedure?

Example code TSQL  
create procedure us\_test @coden int out  
as  
select \* from table1  
select \*,0 as id from table2  
select 0,‘ffff’ as namefield from table3  
select @coden=@coden+100  
GO  
declare @i int  
select @i=1  
exec us\_test @i out  
select @i

We return 3 recordset and @i=101

I want to run us\_test from Ruby (on Windows system).

---

<div class="post-metadata">

**Author:** ![talex83](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/54ee81/32.png) [@talex83](https://www.ruby-forum.com/u/talex83)\
**Post date:** [March 15, 2008, 8:38pm UTC](https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930/2 "2008-03-15T20:38:54Z")

</div>

Sorry for my bad English.  
Can somebody help me with MSSQL 2005 and stored procedure?

---

<div class="post-metadata">

**Author:** ![talex83](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/54ee81/32.png) [@talex83](https://www.ruby-forum.com/u/talex83)\
**Post date:** [March 22, 2008, 5:02pm UTC](https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930/3 "2008-03-22T17:02:23Z")

</div>

Damjan R.  
thank you  
Sorry for my Bad English.

Can you help me with this problem? I want programming Ruby.  
Can I get out parameter @i1 from this code?

create procedure us\_test @i int,@i1 int out  
AS  
select @i as i  
select @i1 as i1  
select @i as i,@i1 as i1  
select @i1=@i1+1  
GO

declare @i int,@i1 int  
select @i=1,@i1=2  
exec us\_test @i,@i1 out  
select @i1  
go

---

<div class="post-metadata">

**Author:** ![talex83](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/54ee81/32.png) [@talex83](https://www.ruby-forum.com/u/talex83)\
**Post date:** [March 17, 2008, 11:54am UTC](https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930/4 "2008-03-17T11:54:30Z")

</div>

Alexander Tretjakov wrote:

> Sorry for my bad English.  
> Can somebody help me with MSSQL 2005 and stored procedure?

Something to start from.

require ‘rubygems’  
require ‘dbi’

dbs = DBI.connect(‘DBI:ODBC:MYDB’, ‘usr’, ‘pwd’)  
dbs.execute("{call pr\_appLogin(2, ‘myid’, ‘mypwd’)}")

I have to use this on an database where second login is required to  
update database. pr\_appLogin is the name of stored procedure on MSSQL  
2005 server.

by  
TheR

---

<div class="post-metadata">

**Author:** ![talex83](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/54ee81/32.png) [@talex83](https://www.ruby-forum.com/u/talex83)\
**Post date:** [March 25, 2008, 7:43am UTC](https://www.ruby-forum.com/t/mssql-2005-stored-procedure/131930/5 "2008-03-25T07:43:23Z")

</div>

Alexander Tretjakov wrote:

> Damjan R.  
> thank you  
> Sorry for my Bad English.
> 
> Can you help me with this problem? I want programming Ruby.  
> Can I get out parameter @i1 from this code?
> 
> create procedure us\_test @i int,@i1 int out  
> AS  
> select @i as i  
> select @i1 as i1  
> select @i as i,@i1 as i1  
> select @i1=@i1+1  
> GO
> 
> declare @i int,@i1 int  
> select @i=1,@i1=2  
> exec us\_test @i,@i1 out  
> select @i1  
> go

This example is using ActiveRecord to call stored procedure. As you can  
see last result of SELECT is returned as ar record.

class MyLoginTable \< ActiveRecord::Base  
establish\_connection(  
:adapter =\> “mssqlclient”,  
:host =\> “MYHOST”,  
:username =\> “myusr”,  
:password =\> “mypwd”,  
:database =\> “mydb”  
)  
set\_table\_name “mytable”

end

sql = \<\<EOTXT  
SET nocount ON  
DECLARE @oozidx int  
SET @oozidx = -2  
EXEC pr\_wlogon @usr = ‘#{params[:usr]}’, @pwd = ‘#{params[:pwd]}’,  
@oozid=@oozidx OUTPUT  
SELECT @oozidx AS result  
EOTXT

record = MyLoginTable.find\_by\_sql(sql)  
if ( login= record[0][:result]) == -2  
flash[:error] = ‘Login failed!’  
redirect\_to :action =\> ‘login’  
else  
…

by  
TheR