.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

JDBC - calling stored procedure always returns 1 result set

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server

I am running the following against Northwind on Sql Server 2005. It always returns a result set of 1 row regardless of the parameters passed:

Connection conn;
// not showing conn creation

CallableStatement callStmt = conn.prepareCall("{ call [Sales by Year] (?,?) });
callStmt.setDate(1, new Date (long_Jan_1_1996));
callStmt.setDate(2, new Date (long_Jan_1_1997));
ResultSet rs = callStmt.executeQuery();

Why do I only get 1 row of results back?

thanks - dave

Very funny video - What's your Metaphor?

View Complete Post

More Related Resource Links

Calling Stored Procedure in asp.net

Hi! I am trying to insert a row via button event that should call an Oracle Stored Procedure, but the insert never happens. Please see code below: C#-code {code} using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OracleClient; using System.Data;   public partial class new_user : System.Web.UI.Page {     protected void button1_click(object sender, EventArgs e)     {         OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();           b.UserID = "Test";         b.Password = "xxx";         b.DataSource = "ora11";           OracleConnection con = new OracleConnection(b.ToString());           try         {             con.Open();             OracleCommand spcmd = new OracleCommand();               spcmd.CommandType = CommandType.StoredProcedure;     &nbs

Calling another Stored Procedure within a stored procedure

I want to call a stored procedure within my main stored procedure to return a value. Here is the code snippet:   EXECUTE @ClaimNumber =  [dbo].[uspGetNextNumber] 'BRP_INT' , 'ClaimHeader', 'ClaimNumber', 'dlivelsb' When I run uspGetNextNumber by itself thru debugger, the proc returns the value. When I run the proc inside my stored procedure, it returns zero. Any thoughts on why this is not working?

Calling AS400 Stored Procedure from ASP.net C# Coding

Hi, I have a req in which I have to call an AS400 stored procedure.The stored has an input and output parameter. Can any body help me.I am new to this technology. Please help me its urgent Thanks, A2H  

insert result of stored procedure in new table

hi how can i insert the result of stored procedure in the new table. Best Regards. Morteza

Entity Model- Add Function Import - selected stored procedure returns no columns

I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

stored procedure returns numbers ()

I would like to write a stored procedure which will return some numbers based on query, and it will go something like this: - find an id of a person, if his username exists - if  id is not found retun 0 - else if the id is found, look for person`s password - if password is NOT found return 1 - else if password IS found return 2   I did some coding, but it seems its not working somehow. It always retuns null value: ps: It is not important if I use "IF EXISTS", it can also be used "IF IS NOT NULL". And I tried to use if its null or not, but I got the same result - nothing (only null)   ALTER PROCEDURE myStoredProcedure01 ( @strValue varchar(50) ) AS BEGIN SET NOCOUNT ON IF EXISTS(SELECT PersonID FROM Person WHERE UserName = @strValue) BEGIN DECLARE @password varchar(50) SELECT @password = Password FROM Person WHERE UserName = @strValue IF(@password IS NULL ) RETURN 2 ELSE RETURN 1 END ELSE RETURN 0 END    

call to stored procedure occasionally returns empty recordset



I have a devil of an intermittent problem that I have so far found nothing to help me solve.

I have a web application/module in Dotnetnuke that requires data for a simple form from another SQL server DB on a different server.  The app uses a connection string in the web.config like this:

<add name="CompanyUpdate" connectionString="Data Source=mail.mydatabase.be;Initial Catalog=MA_Online;Persist Security Info=True;User ID=Gus;Password=xxxxxx" providerName="System.Data.SqlClient" />

The application calls a stored proc and most of the time data is returned as expected. There should always be a row returned from the SP.  However, if the form is idle for a while or the module has only just loaded the call to the stored proc returns as empty recordset. A 'table not found' error occurs.

There is no connection issue otherwise there would be a connection error instead of an empty recordset right?. But I cannot work out why sometimes no data is returned. I can go onto the server and run management studio and run the sp over and again and always get data.

I don't have control over the target DB but the person who does thinks it's something to do with connection pooling. I tried turning this off in the connection string and found that I got an empty recordset every time the sp was called wit

calling stored procedure with in select statement



        Any one pls tell me how to call the stored procedure in SQL server

Calling Oracle stored procedure


I am calling oracle procedure in SSIS script task, This stored procedure retrun value as output, I tried OLEDB it not working, Now i am trying using ADO.net connection, while executing task i am getting following error msg


[Execute SQL Task] Error: Executing the query "exec ssisproctes" failed with the following error: "Unable to cast object of type 'System.Data.OracleClient.OracleConnection' to type 'System.Data.SqlClient.SqlConnection'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am able to test connection in connection manager?

any help to resolve this issue, or is there any other method to call oracle procedure and get output value in a SSIS variable




JDBC - stored procedure won't allow setting parameters by name



When I try to call CallableStatement.setString( name, value ) it throws an exception. Is there something I have to do to either change the parameter names or set them up? I use the full "@Beginning_Date" as the name.

thanks - dave

Very funny video - What's your Metaphor?

Stored procedure returns two select results


Hey guys

I am creating a buddy list and I need to select my entire buddy list and then the ones that are online

I have two querries one is your standard


FROM buddies


the other is a bit more complicated


SELECT Buddy as Online
FROM Buddies
FROM online 
WHERE online.Buddy = Buddies.Buddy)

I can't use the union command since I create another column

What I would like is to append the column to my original result so I can access it from some vb script using the sqlclient.datareader


my stored procedure not give me result as per input given by me


ALTER Proc [dbo].[Usp_CheckUserLogin]
    @Username varchar(20),
    @Password varchar(20)

    Declare @RowCount int
    Declare @RowCount1 int
    set @RowCount = (select distinct UsrId from UserMast where username=@username COLLATE SQL_Latin1_General_Cp1_CS_AS )    
    set @RowCount1=(select distinct UsrId from UserMast where [password]=@password  COLLATE SQL_Latin1_General_Cp1_CS_AS)    
    if(@RowCount > '0' and @RowCount1 > '0')
        if (@RowCount=@RowCount1)
--        select U.UsrId,U.FirstName,U.LastName,U.Email,U.companyId,U.role,R.RoleName from UserMast U join rolemast R on U.Role=R.RoleId
--        where usrid=@RowCount
        select 'success'
     if(@RowCount > '0' and @RowCount1 = '0')

MSSQL 2005 JDBC driver multiple select statements in stored procedure



we have a stored procedure with multiple select in it.



Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3


Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.




Calling a stored procedure or function from another stored procedure


Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)


Finished running [dbo].[DeleteSetByTime].

This is my function:






RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:




Capturing data through a stored procedure as a result of a click event (asp.net)



I am having trouble capturing data as a result of a click event. Basically, I don't know what code to put and where for the click event.

I need to record information from a text box, into a table in Microsoft SQL Server. Any help would be great.

My stored procedure is below:

/****** Object:  StoredProcedure [dbo].[SearchConcept_SP]    Script Date: 10/23/2010 12:17:48 ******/
ALTER PROCEDURE [dbo].[SearchConcept_SP]
@Concept_Name nvarchar(50),
@Keywords char(50)

IF EXISTS(SELECT Concept_Name FROM Concept WHERE Concept_Name = @Concept_Name)
PRINT 'This concept already exists'

INSERT INTO Concept VALUES (@Concept_Name, @Keywords)

binding to item template label of a gridview from stored procedure result of common column names


hi  my stored procedure contains a joining result of different tables with common column name to a dataset result can bind to gridview

as follows

CREATE proc SppShowStock 

select p.id, p.ProductId,  p.ProducName,  sh.CompanyName ,  su.CompanyName

TblStock p inner join TblSupplier su on p.SupplierId=su.CompanyCode 
     inner join TblShipper sh on p.ShipperId=sh.shipperid   
           inner join  TblCategory c on p.Category=c.Id 


<asp:TemplateField HeaderText="id">
                    <asp:Label ID="lblid" runat="server" Text='<%# Eval("id") %>'></asp:Label>


i am little bit in confusi

Get same result when running MDX in stored procedure and Analysis Services


I have a simple MDX query as follows:

select non empty {descendants([dim fund].[fund structure].&[1])} on rows,
[measures].[amount] on columns
FROM [xyz]

When it runs against Analysis Services, it returns two columns:

All Funds        123,435,567
Fund1            122,400,090
SubFund1      6,445,435
SubFund2      546,343

When it runs it in a stored procedure with Openquery, it returns multiple columns to represent the structure:

create procedure usp_OLAPtest
declare @strMDX_Sql as nvarchar(max)

set @strMDX_Sql = 'select non empty {descendants([dim fund].[fund structure].&[1])} on rows,
[measures].[amount] on columns FROM [xyz]'

exec (N'select * from openquery(xyz_OLAP, ''' + @strMDX_Sql + '''); '

All Funds  NULL    NULL          123,435,567
All Funds  Fund1   NULL          122,400,090
All Funds  Fund1   SubFund1   6,445,435
ALL Funds  Fund1   SubFund2   546,343

Is there any way for the stored proce

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend