Hi All
Im generally a vb programmer and am used to referencing multiple records
returned from a query performed on an sql database and im trying to move
some functions of my software into sql stored procedures. So far ive been
able to move the functions relatively easily but im unsure about how to
output multiple values from an sql stored procedure. By this i mean for
example one of the stored procedures may take your username and return the
contents of a single field in a record of one of the tables, but i would
like to be able to return for arguement sake the contents of a single field
from two records if possible. Under VB im used to referencing the recordset
with a (1) after it to reference the corresponding record from the query. I
was wondering if there is a way to do something similar to this with stored
procedures if possible ?
Thanks for any help
Jul 20 '05
15 6015
Jarrod Morrison (ja*****@ihug.c om.au) writes: Thanks for the post, thats exactly what i wanted. But i have one further question about the second part of the post, when i execute the select statement inside the stored procedure it is actually part of a loop and thus each select statement is being treated seperately by VB, so if i use query analyzer i see for example 3 seperate returns which is ok because it is what is meant to be returned, however vb is only reading the first one of these returns and then closing the recordset. Im sure there is an easy way around this but im unsure of what to do. Any help is greatly appreciated
As Ellen K said, you can use .NextRecordset to access the remaining
recordsets.
However, you should probably redesign your stored procedure. First of
all, maybe you don't need that loop. There are situations when you
need to run loops in T-SQL, but programmers who are used to VB, C and
the like but are not well versed in SQL, tend to use loops far more
often than they need. There can be a huge performance gain in replacing
a loop with set-based processing.
But even if you cannot replace the loop, you should probably not return
data for each turn in the loop. Not only does it make you VB processing
more complicated, but it is also ineffecient in regards to network
resources. Rather than returning data to the client, you can insert
the data into a table variable or a temp table, and then have a SELECT
at the end of the loop that returns data from the temp table.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>... Jarrod Morrison (ja*****@ihug.c om.au) writes: Thanks for the post, thats exactly what i wanted. But i have one further question about the second part of the post, when i execute the select statement inside the stored procedure it is actually part of a loop and thus each select statement is being treated seperately by VB, so if i use query analyzer i see for example 3 seperate returns which is ok because it is what is meant to be returned, however vb is only reading the first one of these returns and then closing the recordset. Im sure there is an easy way around this but im unsure of what to do. Any help is greatly appreciated
As Ellen K said, you can use .NextRecordset to access the remaining recordsets.
However, you should probably redesign your stored procedure. First of all, maybe you don't need that loop. There are situations when you need to run loops in T-SQL, but programmers who are used to VB, C and the like but are not well versed in SQL, tend to use loops far more often than they need. There can be a huge performance gain in replacing a loop with set-based processing.
But even if you cannot replace the loop, you should probably not return data for each turn in the loop. Not only does it make you VB processing more complicated, but it is also ineffecient in regards to network resources. Rather than returning data to the client, you can insert the data into a table variable or a temp table, and then have a SELECT at the end of the loop that returns data from the temp table.
I've never found a need to use .NextRecordset yet. As Erland says,
better stored procs are the way forward. Have you got a sample loop
that you can post?
Samuel Hon (no*****@samuel hon.co.uk) writes: I've never found a need to use .NextRecordset yet. As Erland says, better stored procs are the way forward. Have you got a sample loop that you can post?
You haven't? For me whose favourite client library i DB-Library, it
seems obvious that you should use .NextRecordset. There may not be
any more recordsets to pick up, if there is and you don't get them,
you can run into surprises. (With DB-Lib and ODBC you will get an error
on next access. ADO will open a new connection, but that may still lead
to unexpected behaviour.)
A presumption for ditching .NextRecordset completely is that you use
SET NOCOUNT ON consistently.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
I've actually never used it either... but I know it exists. :)
On 15 Dec 2003 01:53:29 -0800, no*****@samuelh on.co.uk (Samuel Hon)
wrote: I've never found a need to use .NextRecordset yet.
Hi Erland
Well i did at first attempt to insert all the data into a temporary table in
sql, and at the end say select * from #tablename, but when i try to access
the data from the vb program i recieve the error
Runtime error 3265
Item cannot be found in the collection corresponding to the requested name
or ordinal
I am using the example that samuel posted previously as the basis for my
coding and it returns data correctly if i do select statements on non
temporary sql tables fine but when it is on a temporary table i recieve the
above error. Below is the stored procedure i am working on
CREATE PROCEDURE [dbo].[TestProcedure01]
@EntityLocation ID VarChar(300)
AS
DECLARE @SrchCount VarChar(3) /* Search Loop Count */
DECLARE @SrchInt VarChar (3) /* Current Search Position */
SET @SrchInt = 1
SET @SrchCount = (LEN(@EntityLoc ationID) - 1) / 3
CREATE TABLE #AvailPrint (Path VarChar(60), Name VarChar(30), Priority
Char(1), LocationID VarChar(300))
WHILE @SrchInt <= @SrchCount
BEGIN
INSERT INTO #AvailPrint(Pat h,Name,Priority ,LocationID) SELECT *
FROM Printers WHERE LocationID LIKE RIGHT(LEFT(@Ent ityLocationID,
@SrchInt * 3 + 1),3)
/*SELECT * FROM Printers WHERE LocationID LIKE
RIGHT(LEFT(@Ent ityLocationID, @SrchInt * 3 + 1),3)*/
SET @SrchInt = @SrchInt + 1
END
SELECT * FROM #AvailPrint
GO
What this procedure does at the moment is to pull out 3 records from a table
and place them into the temporary table (The number of records is not fixed
and is dependant on the input variable @EntityLocation ID), nd it outputs a
table in query analyzer fine. If i change the procedure so that the INSERT
INTO line is remarked out (Put a /* */ around it) and remove the remarks on
the line below the query analyzer returns the 3 records but they are
seperated, so when i use the vb program i am only able to access the first
record of data. Im not sure how to structure the procedure without the loop
as im used to using vb loops so any help with this is greatly appreciated
Thanks
Jarrod Morrison (ja*****@ihug.c om.au) writes: Well i did at first attempt to insert all the data into a temporary table in sql, and at the end say select * from #tablename, but when i try to access the data from the vb program i recieve the error
Runtime error 3265
Item cannot be found in the collection corresponding to the requested name or ordinal
I am using the example that samuel posted previously as the basis for my coding and it returns data correctly if i do select statements on non temporary sql tables fine but when it is on a temporary table i recieve the above error. Below is the stored procedure i am working on
The error message is from Visual Basic, and means that the index you
are using to address a collection is not valid.
I would guess tha the reason is that one or more of the columns in
the temp table, does have the same name as the colunms in the
Printers table, but you have failed to amend the VB code for this.
Permit me to note, that I would not have to resort to guessing, if
you also had included the VB code you are using, and you had replace
SELECT * FROM Printers with the actual columns. In my opinion,
"SELECT *" from permanent tables should not be used in stored
procedures, because the procedure could break if someone adds a new
column to the table. (From this follows that SELECT * from the temp
table is OK.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: sedefo |
last post by:
I ran into this Microsoft Patterns & Practices Enterprise Library while i
was researching how i can write a database independent data access layer. In
my company we already use Data Access Application Block (DAAB) in our .Net
projects. We use SqlHelper in SQL based projects, and OracleHelper in Oracle
based ones. OracleHelper was not published officially by Microsoft as part of
the DAAB but it was given as a helper code in a sample .Net...
|
by: pmud |
last post by:
Hi,
I need to display columns in a data grid based on 7 different queries. Now I
have 32 questions:
1. Is it possble to have 1 single data adapter with 7 queries & 1 data set
or do I need to have a separate data adapter & a separate data set for each
select query?
If thats possible then how?
|
by: kjphipps_377 |
last post by:
Hi all!
I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way?...
|
by: Josh Strickland |
last post by:
I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.
tCetecM1CUST (SQL Table that contains the Customer Information)
tAccountingDetail (SQL Table that contains the information in the
form)
frmAccountingEntry (Access form used to enter data)
spGetCustomerInformation (Stored Procedure which returns data using...
|
by: Wojciech Wendrychowicz |
last post by:
Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG
program, and finally some VBA code to call the stored procedure and retrieve
data from AS/400.
The problem is, that when I finally run my VB code, it just hangs.
But when I call the same stored procedure from "pure" SQL - it works
perfect. (I evaluate Aqua Data Studio 3.7)
What I...
| |
by: David Lozzi |
last post by:
Hello,
I have a proc as displayed below. When I try to run it from ASP.NET I get an
error.
CREATE PROCEDURE .
@SID as int,
@EID as int,
@DID as int,
@StartTime as varchar(10),
|
by: deepdata |
last post by:
Hi,
I am trying to fetch data from db2 (express version) database by
calling stored procedure. I have tried to use both cursor and for loop
but still i am getting error.
--======Start procedure=============
Create PROCEDURE get_timedout_scripts
(
|
by: Roger |
last post by:
I have a stored procedure running on DB2 V7 Z/os calling a COBOL
program to do some inserts. The stored procedure have 3 input columns
and one column is of varchar(32648)
The stored procedure is being called from a V7 DB2 connect client.
The stored procedure is giving SQL0104N An unexpected token was found
if my varchar data goes beyond 1024 bytes. Anything under 1025 bytes
on that column is working perfectly.
Does anybody know of...
|
by: preejith |
last post by:
Error Code : 1329, No data - zero rows fetched, selected, or processed. MYSQL
I am getting the following error while running a stored procedure in mysql5.0
Error Code : 1329
No data - zero rows fetched, selected, or processed.
I have an stored procedure SP1 which calls stored procedure SP2 and SP2 calls a function F1.
I have run this script from .bat file.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |