.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

Stored procedure returns two select results

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

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


View Complete Post

More Related Resource Links

INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Insert the results of a stored procedure into specific columns

Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

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: select and update

Is it possible to use a stored procedure to get a value of the database, change it and save it back at ones? Let me show a simple example: In the databse I have an integer value (20). Then I get another number (5), and I would like to subtract it of the  value from database (20 - 5). That means I have to get the value 20 out of the database, of it subtract value 5, and insert (update) new value (15) back to the database. Or is it not even necessary to get the value 20 out of the databse, but only directly subtract value 5 of the value in the database? Is this possible to do with stored procedure?

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    

Its posible to do a select without the table and the column names into a stored procedure?



I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

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

Two results from two different tables by Stored Procedure and put them in variables and send email.


Hi All, first i had like 7-8 steps just to execute stored proc and send email. now i have put everything in one stored proc as follows :

Alter procedure PlanFinder.InsertInvalidRecords

Truncate table [PlanFinder].[InvalidAwps] 
INSERT INTO [PlanFinder].[InvalidAwps](Ndc, AwpUnitCost) 

SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost 
    PlanFinder.PlanFinder.HpmsFormulary P 
    LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices  
               WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A 
ON P.Ndc = A.Ndc  
WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL) 

DELETE FROM PlanFinder.NdcAwp
       Ndc IN
              SELECT Ndc
              FROM  PlanFinder.InvalidAwps                     &nb

JDBC - calling stored procedure always returns 1 result set


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?

Linq and stored procedure - cannot get results


Hi to all,

i try call a stored procedure from my linq code in order to validate a user credentials. I have tried via linq to sql and sql commands and everything works as expected. But when comes to calling a stored procedure via linq, i cannot get the results i expect.

This is my code:

stored procedure:

ALTER PROCEDURE [dbo].[ValidateUser] (@UserName varchar(50), @UserCode varchar(4))

	SELECT ISNULL(FirstName, ''), ISNULL(LastName, '')
	FROM Users
	WHERE (UserName =@UserName AND UserCode=@UserCode)


                dbContext = new MyDBDataContext();
                var result = dbContext.ValidateUser("Jim", "1234");

                if(result != null)
                    //true, so user is validated so set response status to OK
                    response.Status = ResponseStatus.OK;

                    //set returned params to response
                    var paramsReturned = result.FirstOrDefault();

                    response.FirstName = paramsReturned.Column1;
                    response.LastName = paramsReturned.Column2;
                    //false, so set respone to the corresponding error

Stored Procedure Metadata failure- Dummy select not working


I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -


IF 1=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.




Select within Stored Procedure



What the correct syntax for this, im  doing this in a stored procedure.

	select @useridHigh, @userBalHigh = UserID,MAX(Balance)
		from [User]
		where LastName = 'Tom'
		group by UserID



Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.
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