Running a procedural query ?

I am trying to run a query through ODBC on a Mssql Server 2008 database.
The query runs on the database as expected, however it is support to return
a single row of results which vary depending on the results of the query. I
have tested the sql query directly against the database and it runs as
expected, however with running it using clsql:query it only returns the
number 1 instead of the expected list of results. Is there a step I am
missing with my call? The following is an example of what I am trying to
run:

---------------------------------------------------------------
(clsql:with-database (*default-database* *mssql-connect-writer-spec*
:database-type :odbc
                                         :if-exists :new :make-default nil)
  (clsql:set-autocommit nil)
  (clsql:query "
DECLARE @batchID INT
DECLARE @batchIDNext INT
DECLARE @result INT
DECLARE @errorMessage VARCHAR(255)


-- Start the transaction process here
BEGIN TRANSACTION

-- Try attempts to run the code inside of the try, if it fails it is caught
by the CATCH, the CATCH must immediately follow the TRY
BEGIN TRY
        SELECT @batchID = next_value from autoincrement a where
a.company_id = 'TMS' and a.tablename = 'cash_batch' and a.fieldname = 'id'

        SELECT @batchIDNext = (@batchID + 1)

        update autoincrement set next_value=@batchIDNext where
company_id='TMS' and tablename='cash_batch' and fieldname='id' and
next_value=@batchID

        INSERT INTO cash_batch(company_id, amount, amount_c, amount_d,
amount_n, amount_r, glid, id, receipt_date)
                       values
('TMS','hi','USD',null,50.00,1.0000,'2015000000',@batchID,'2013-03-05
12:31:45')

END TRY
BEGIN CATCH
        -- TRANCOUNT checks how many transaction steps are currently
uncommitted, so if there was an error roll them back which sets @@TRANCOUNT
to zero
        IF @@TRANCOUNT > 0
           BEGIN
              ROLLBACK TRANSACTION;
              SELECT @result = -1;
              SELECT @errorMessage = ERROR_MESSAGE();
           END

END CATCH

-- If there were errors @@TRANCOUNT will be rolled back to zero, Otherwise
commit and return the batch id
IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION;
        SELECT @result = @batchID;
        SELECT @errorMessage = 'ok';
    END

SELECT @result as resultid, @errorMessage as message;"
   :result-types '(:int :string)))

-------------------------------------------------------------

Thanks,

-- 
Brian Sorg
Director and Founder Liberating Insight LLC
[email protected]
Office: 260.918.0490
Mobile: 260.602.1086
www.liberatinginsight.com

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql