# Questions on DBI::ADO stored procedures and inserts mssql se

**URL:** <https://www.ruby-forum.com/t/questions-on-dbi-ado-stored-procedures-and-inserts-mssql-se/79770>\
**Category:** Ruby\
**Created:** [December 18, 2006, 10:16pm UTC](https://www.ruby-forum.com/t/questions-on-dbi-ado-stored-procedures-and-inserts-mssql-se/79770 "2006-12-18T22:16:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 18, 2006, 10:16pm UTC](https://www.ruby-forum.com/t/questions-on-dbi-ado-stored-procedures-and-inserts-mssql-se/79770/1 "2006-12-18T22:16:32Z")

</div>

I am writing a class to deal with a mssql server database. I have a  
couple of questions on using DBI::ADO

1. How does one run a stored procedure with input parameters using  
DBI::ADO? I’ve searched for documentation but have not found nothing  
specific

2. When I insert an item I need to get the primary key of the item  
inserted. In [vb.net](http://vb.net) I run a “Select @@Scope\_Identity” after the insert.  
How would I do this with DBI::ADO?

thanks,

Luis

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 20, 2006, 6:33pm UTC](https://www.ruby-forum.com/t/questions-on-dbi-ado-stored-procedures-and-inserts-mssql-se/79770/2 "2006-12-20T18:33:48Z")

</div>

I figured out how to solve these two issues with Ruby DBI::ADO

Let’s say you have a stored procedure you normally call like this

DECLARE @return\_value int

EXEC @return\_value = [dbo].[sp\_RenamePaths]  
@OldPath = N’C:\ruby’,  
@NewPath = N’C:\rubynew’

SELECT ‘Return Value’ = @return\_value

You could define a method like this to run it

def tmf\_sp\_rename\_paths(old\_path, new\_path)  
sql = “DECLARE @return\_value int exec @return\_value =  
sp\_RenamePaths @OldPath = N’#{old\_path}’, @NewPath = N’#{new\_path}’  
SELECT ‘Return Value’ = @return\_value”  
dbh=DBI.connect(“DBI:ADO:Provider=SQLNCLI; Data  
Source=localhost\sqlexpress;Database=Mydb;uid=MyUser; pwd=MyPass;”)  
dbh.doc(sql)  
dbh.commit()  
end

If you need to get the @@Identity of an insert you can do something  
like this

def tmf\_insert\_new\_file(name, length, creation\_time, directory\_name,  
extension, fullname, is\_read\_only, last\_access\_time, last\_write\_time,  
filetype, parent\_directory)  
strInsert = " SET NOCOUNT ON INSERT INTO tblMasterFiles ([Name],  
[Length], [CreationTime], [DirectoryName], [Extension], [FullName],  
[IsReadOnly], [LastAccessTime], [LastWriteTime], [FileType],  
[ParentDirectory]) VALUES (’#{name}’, ‘#{length}’, ‘#{creation\_time}’,  
‘#{directory\_name}’, ‘#{extension}’, ‘#{fullname}’, ‘#{is\_read\_only}’,  
‘#{last\_access\_time}’, ‘#{last\_write\_time}’, ‘#{filetype}’,  
‘#{parent\_directory}’); SELECT @@IDENTITY As myKey"

myKey = “”

```
dbh=DBI.connect("DBI:ADO:Provider=SQLNCLI; Data

```

Source=localhost\sqlexpress;Database=Mydb;uid=MyUser; pwd=MyPass;")

```
dbh.execute(strInsert) do |sth|
  myKey = sth.fetch
 end

 dbh.commit()

return myKey.to_s

```

end