Stored Procedure + Out Parameter

javid · **Joined:** Thu Feb 15, 2007 12:48 pm **Posts:** 11

Hi,
I just upgraded to NHIbernate 1.2, just for the Stored procedure capability. I am in need to execute a stored procedure with one input and one output parameter. I tried to look for examples but really was not able to find a good answer. If anyone could help me, how to invoke a stored procedure from nHibernate, it would be very helpful.

All I need is I pass an int input parameter and a string out parameter to the SP.
Thanks for any help,
Javid

Hibernate version:
1.2
Mapping documents:

Code:

<sql-query name="spCreateTemplateClause">
    <return-scalar column="where_clause" type="string"></return-scalar>
    call spCreateTemplateClause (:template_id, where_clause)
  </sql-query>

Code between sessionFactory.openSession() and session.close():

Code:

    IQuery query = Db.Session.GetNamedQuery("spCreateTemplateClause");
     query.SetInt32("template_id", 10);
     query.List();

Full stack trace of any exception that occurs:

Name and version of the database you are using:
SQL server 2005
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

focus · **Posted:** Wed Mar 07, 2007 7:36 pm

There was a whole bunch of stuff missing from your posting - the SQL, the error messages.... however, I did read stuff about this yesterday.

You mention that the procedure generates one output value (but we don't see the SQL so I'm not sure what this is, however I'm assuming something like:

Code:

SELECT COUNT(*) FROM MYTABLE

In this instance your Java call is then going to look something like (and I'm not talking specifics here):

Code:

    IQuery query = Db.Session.GetNamedQuery("spCreateTemplateClause");
     query.SetInt32("template_id", 10);
     query.uniqueResult(); 

Let me know if this works for you.

Cheers
mc

roping.zong · **Joined:** Wed Mar 07, 2007 10:59 pm **Posts:** 6

Hi,javid
Can you give me a detail Demo?
My Email is roping.zong@erdc.com
I from china!

javid · **Joined:** Thu Feb 15, 2007 12:48 pm **Posts:** 11

Focus,
Thanks for your reply. Sorry for not posting my SQL. Here it is:

Code:

ALTER PROCEDURE [dbo].[spCreateTemplateClause] 
   @template_id int, 
   @where_clause nvarchar(MAX) OUTPUT
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

DECLARE @clause AS nvarchar(MAX)
SET @clause = ''
DECLARE @type AS varchar(50)
DECLARE @value AS varchar(255)
DECLARE @combo_type AS varchar(50)
DECLARE @combo_value AS varchar(255)
DECLARE @current_type AS varchar(50)
SET @current_type = ''
DECLARE @current_combo_type AS varchar(50)
SET @current_combo_type = ''
DECLARE @join AS varchar(5)
SET @join = ''

-- Loop through all the values in the filter values table to create
-- a 'WHERE' clause for which items to include in a query.
DECLARE temp_cursor CURSOR FORWARD_ONLY STATIC FOR 
  SELECT [TYPE], [VALUE], COMBO_TYPE, COMBO_VALUE 
  FROM SCENARIO_TEMPLATE_VALUES 
  WHERE TEMPLATE_ID = @template_id
  ORDER BY [TYPE], COMBO_TYPE 

OPEN temp_cursor

-- Get the first value from the cursor
FETCH NEXT FROM temp_cursor 
INTO @type, @value, @combo_type, @combo_value

-- Need to keep track of when the filter type changes. For the 
-- filter, similar types should be OR'd and type changes 
-- should be AND'd.
SET @current_type = @type

IF @@FETCH_STATUS = 0
    SET @clause = ' WHERE '

WHILE @@FETCH_STATUS = 0
BEGIN

-- eg. "(WAREHOUSE_NAME = 'Warehouse 1')"
SET @clause = @clause + '(' + @type + ' = ''' + @value + ''')'

-- Combo values are dependant criteria... they should be AND'd with
-- the main clause from above.
IF @combo_type IS NOT NULL
    SET @clause = @clause + ' AND (' + @combo_type + ' = ''' + @combo_value + ''')'

FETCH NEXT FROM temp_cursor 
INTO @type, @value, @combo_type, @combo_value

IF @@FETCH_STATUS <> 0
   BREAK

-- Set the clause join, depending on whether the type changes or not.
IF @type = @current_type 
    SET @join = ' OR ' 
ELSE
    SET @join = ' AND '

SET @clause = @clause + @join
SET @current_type = @type

END

CLOSE temp_cursor
DEALLOCATE temp_cursor

SET @where_clause = @clause

END

The stored procedure takes in an int ID and builds the where clause I am supposed to be using for another select statement.
I am not sure if this would work with hibernate as I read in the docs it needs a resultset as output to work.

Thanks for your help,
Javid