Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006

    Unanswered: SQL Stored Procedure not returning result set

    This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so:
    SELECT region_code,
    ' ' as right_type
    INTO #tmpTShell FROM TShell WHERE 1=0

    But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.

    Has anyone ever experienced anthying like this? Any help would be greatly appreciated!


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    This is a known problem in WebSphere. It seems to me that you need to use SET NOCOUNT ON to correct the problem.


  3. #3
    Join Date
    May 2006
    Thanks Pat!!

    It seems to be working now. I've spent an entire day on trying different things to get this to work. I think it's resolved now.

    Thanks again!

  4. #4
    Join Date
    Dec 2010

    calling a sql server stored procedure from WMB

    I have to write a stored procedure in sql server which takes input parameter, uses the parameter to execute a statement (for example : select * from table where column1 in @inputvariable).
    I want to return the resultset back to the calling environment which is WMB.
    How should my stored procedure in sql server look?

    create procedure proc1
    @v_input varchar(max)
    declare @SQL varchar(max)
    declare @SQL2 varchar(max)
    select @SQL = 'select * from employees where employeeid in '
    select @SQL2 = ' and employeestatus = ''A'''
    select @SQL = @SQL + @v_input + @SQL2
    exec (@SQL)

    This is what I have written. will this return the resultset to WMB or should I add anything else to my procedure?

  5. #5
    Join Date
    Jul 2003
    San Antonio, TX
    There are better ways of how to handle comma-separated list of values (replace VARCHAR(1000) with VARCHAR(max), if you're using SQL2K5 or above).
    This way you can avoid dynamic SQL, along with other hidden issues that you haven't encountered yet.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Quote Originally Posted by Pat Phelan View Post
    This is a known problem in WebSphere. It seems to me that you need to use SET NOCOUNT ON to correct the problem.

    Well, and that's just a "best practices" approach when coding ALL Sprocs


    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts