MSSQL 2005 Stored Procedure?

[Alexander Tretjakov]

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).

 

[Alexander Tretjakov]

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

 

[Damjan Rems]

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

 

[Alexander Tretjakov]

Damjan Rems
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

 

[Damjan Rems]

Damjan Rems
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",
assword => "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[wd]}',
@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