SQL Server stored prcedures with output parameters

[Brian McErlean]

Steve Holden <steve at holdenweb.com> wrote in message news:<oSYmd.10931$nj.6308 at lakeread01>...
> Steve Holden wrote:
> 
> > 
> > Hmmm, following up, added another test to adodbapi to retrieve a value 
> > from a table into an output parameter it works, so this puts the 
> > suspicion on to the stored procedure. Since this was written by the 
> > client I'll take a look at it later (when I'm back in the office) and 
> > see what the problem might be.
> > 
> > happily-talking-away-to-myself-ly y'rs  - steve
> 
> However, the stored procedure below appears to consistently return zero 
> for the link_id when called with:
> 
> columnist_id, url, title, description, link_id = \
> 	curs.callproc("sp_InsertArticle",
> 		(columnist_id, url, title, description, 0))
> 
> so maybe there really *is* something wrong. Or, hopefully, I'm just 
> making a simple mistake in the calls? Whatever value is used as the 
> fifth (output) parameter seems to get returned, despite the apparent 
> change in the stored procedure.
> 
> Create PROCEDURE dbo.sp_InsertArticle
> (
> 	@columnist_id int,
> 	@url varchar(255),
> 	@title varchar(99),
> 	@description text,
>     	@link_id int OUTPUT
> )
> AS
> BEGIN
> 	INSERT INTO link	(columnist_id,url,title,description)
> 	VALUES (@columnist_id, at url, at title, at description)
> 
>      SELECT @link_id = @@IDENTITY
> END
> 
> GO
> 

I had a similar problem doing this once before that I think was
related to the connection string.  Changing to use the SQLOLEDB
provider solved it.  I've just tested the below script here and got
the expected results:

Stored procedure:

  CREATE PROCEDURE TestSP
     @param INTEGER OUTPUT
  AS
  BEGIN
  select @param = 10
  END  

Python test:

import adodbapi

server='localhost'
dbname='brian'
user='sa'
password=''

db=adodbapi.connect('Provider=SQLOLEDB.1;Data Source=%s;Initial
Catalog=%s;User ID=%s;Password=%s;'%(server, dbname,user,password))
cur = db.cursor()
print cur.callproc('TestSP',(999,))
[10]

-- 
Brian