ExecuteNonQuery Not Returning Rows Affected
Mar 13, 2006
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.
View 2 Replies
ADVERTISEMENT
Jul 23, 2007
When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @@RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance -- With if/else ExecuteNonQuery returns -1ALTER PROCEDURE [dbo].[SP_AddObjectContribution] @ObjectId int, @FanId int, @DateContributed DateTime, @Notes nvarchar(512), @ObjectType intASBEGIN BEGIN TRAN IF @ObjectType = 2 BEGIN INSERT INTO FighterContributions (FighterId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 3 BEGIN INSERT INTO FighterPhotoContributions (FighterPhotoId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 4 BEGIN INSERT INTO OrganizationContributions (OrganizationId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 5 BEGIN INSERT INTO EventContributions (EventId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 6 BEGIN INSERT INTO FightContributions (FightId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END ELSE IF @ObjectType = 7 BEGIN INSERT INTO FightPhotoContributions (FightPhotoId, FanId, DateContributed, Notes) VALUES (@ObjectId, @FanId, @DateContributed, @Notes) END IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END COMMIT TRANEND -- Without if/else ExecuteNonQuery returns 1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@ObjectId int,
@FanId int,
@DateContributed DateTime,
@Notes nvarchar(512),
@ObjectType int
AS
BEGIN
BEGIN TRAN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@ObjectId, @FanId, @DateContributed, @Notes)
IF @@ERROR <> 0
BEGIN
ROLLBACK RETURN
END
COMMIT TRAN
END
View 7 Replies
View Related
Apr 27, 2006
Hi,
I'm using .net 2.0 and I have created a stored procedure (sql server DB) which has one insert statement with 2 input parameters. When I execute this stored procedure using ExecuteNonQuery(), it always returning -1. I want to know how many records are effected. I want to know if there is any error. Help appreciated.
Thanks,
Brett
code looks like this:
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = "myString...";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "usp_InsertApplicationInfo";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = cmd.Parameters.Add("@vchrApplicationName", SqlDbType.VarChar);
param1.Direction = ParameterDirection.Input;
SqlParameter param2 = cmd.Parameters.Add("@vchrApplicationDescription", SqlDbType.VarChar);
param2.Direction = ParameterDirection.Input;
param1.Value = "prama1";
param2.Value = "test";
cmd.Connection = sqlConn;
sqlConn.Open();
int rows = cmd.ExecuteNonQuery();
View 2 Replies
View Related
Apr 11, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 1 Replies
View Related
Apr 5, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 5 Replies
View Related
Dec 5, 2006
I use business logic layer, TableAdapter(middle tier) and StoredProcedure(backend). In my stored procedure,I set as follows:set nocount offWhat and where should I do in order to get the rows affected count in the business logic layer(C#/VB code)?Thank you.
View 2 Replies
View Related
Dec 20, 2006
I'm doing an executenonquery() and the value I'm getting back is -1 This is confusing me, as sometimes the row has updated when I look at it in sql manager and then I look at it again and it seems to have reverted.Would it be possible to point me in the right direction for the cause and how to rectify / debug this issue. I'm using a stored procedure and I'm slightly uncertain how to check exactly what sql server has been told to insert ;) Thanks!
View 1 Replies
View Related
May 6, 2008
After executing a restore command. I got the following result.
Anyone know why is displaying rows affected. I saw the executiong plan and it shows several execution plans? Its the first time i see it. Can anyone explain what is going on?
restore database test_GG_ATRECORDING_QAT2
from disk = 'f:a.bak'
with move 'a' to 'f:MSSQL$INAQATdataa2.mdf',
move 'a_log' to 'f:MSSQL$INAQATdataa2_log.LDF'
Processed 11016 pages for database 'test_GG_ATRECORDING_QAT2', file 'a' on file 1.
Processed 1 pages for database 'test_GG_ATRECORDING_QAT2', file 'a_log' on file 1.
(1 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(11 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
RESTORE DATABASE successfully processed 11017 pages in 2.330 seconds (38.732 MB/sec).
View 3 Replies
View Related
May 28, 2004
Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?
Any help would be appreciated!
Brian
View 1 Replies
View Related
Dec 29, 2004
Hello,
I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.
I declared a dynamic cursor but it does not work.
Does anyone know how I can do this??
Thanks :)
View 7 Replies
View Related
May 20, 2007
Hi,
I used with Execute SQL Task for update a table in Oracle DB.
I saw that when the command has no rows for updeting, the task fails.
Here is my command:
update tableName set fieldA=sysdate where fieldB is Null
and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.
I tried to play with the ResultSet with no success.
Please your advice.
Thank you in advance
Noam
View 4 Replies
View Related
Aug 31, 2015
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies
View Related
Sep 5, 2007
My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned.
Does this also work when calling stored procedures and passing parameters?
I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1.
What should I do?Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))
myCommand.Connection = con
con.Open()
Dim rowCount As Integer = myCommand.ExecuteNonQuery()
numberParts.Text = rowCount.ToString
con.Close() Thank you.
View 6 Replies
View Related
Mar 15, 2004
I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:
USE DATABASE
--DROP PROC sp_EditMember
GO
--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)
AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st
WHERE member_id = @member_id
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------
on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).
any troubleshooting on this?
thanks in advance,
sudeep.
View 1 Replies
View Related
Jun 1, 2004
Hi,
I've added multiple records with same info during practice. Now I"m trying to delete those records from SQL Server DB. but it says
"Key column information is insufficient or incorrect. To many rows were affected by update."
What to do, to delete these records?
Appreciated..
View 2 Replies
View Related
May 15, 2015
I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected.
BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt'
WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=' ',ROWTERMINATOR='
',FIRSTROW=172)
The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option.
View 7 Replies
View Related
Nov 7, 2007
Hi,
Using VS.NET 2008 Beta2, and SQL Server 2005.
I have a gridview bound to a linq data source, and when trying to update a row, I get an exception that no rows were modified.
The query generated is:
UPDATE [dbo].[package]
SET [owner_id] = @p5
WHERE ([package_id] = @p0) AND ([title] = @p1) AND ([directory] = @p2) AND ([owner_id] = @p3) AND ([creation_date] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [20006]
-- @p1: Input String (Size = 22; Prec = 0; Scale = 0) [Visual Studio.NET 2005]
-- @p2: Input String (Size = 26; Prec = 0; Scale = 0) [MSI_Visual_Studio.NET_2005]
-- @p3: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10000]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/07/2007 12:00:00 a.m.]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10001]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
If I run it manually on sql server, it fails until the directory column is removed. The type is varchar(50), with a uniqueness constraint. However, this is same type as the title column, which doesn't have this problem.
Thanks,
Jessica
View 5 Replies
View Related
Feb 8, 2000
I am using SQLDMO to implement a tool to do basic management/script running for a MSDE database. The problem i am having is getting the number of rows affected from insert,update,delete statements. Help and MSDN seem to imply that this info is returned in a QueryResults object, but it is always empty unless the query is a select statement. I tried using ExecuteImmediate, ExecuteWithResults, and ExecuteWithResultsAndMessages methods and the info is not in any resultset, message, or property.
mikem
View 2 Replies
View Related
Apr 11, 2004
Hi,
i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?
Below is the normal way i did in vb.net, but how to check for the return value. Please help.
========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========
Thank you.
View 12 Replies
View Related
Nov 29, 2013
I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>
[code]....
View 2 Replies
View Related
May 6, 2015
I need to remove "rows affected" text from results as shown below from posted Sp. I am using set nocount on but its not working as expected.
Create Procedure DailyCheckList
As
SET NOCOUNT ON
Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)
set @dt= convert(varchar(20),GETDATE(),107)
[Code].....
View 1 Replies
View Related
Jun 13, 2007
Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce
View 4 Replies
View Related
Oct 22, 2001
Greetings!
I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.
Thanks a million
zachary
View 1 Replies
View Related
Mar 7, 2008
Using MS SQLServer 2000
Is there a way to create a query that will return only 1 row within a join for example:
select a,b,c,d
from tbl1
inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here...
I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause.
thanks
Kevin
View 1 Replies
View Related
Jun 30, 2006
I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help
View 2 Replies
View Related
Mar 23, 2007
I have a stored procedure below that returns a table of coaches. It worked before now it does not. It returns nothing, in vistual studio 2005 and on my asp.net webpage. But when I execute it in query analyzer with just SELECT * FROM Coaches it returns the rows. There is no error just will not return what I need. I recreated the database and stored procedure still doing the same thing. Any ideas? Would this be on my server hosting side? ALTER PROCEDURE [dbo].[GetCo]ASSELECT * FROM Coaches
View 2 Replies
View Related
Aug 9, 2007
Hi All:
My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering. Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
con.Open()Dim empID As String = txtEmpID.Text
Dim FName As String = txtFName.Text
Dim LName As String = txtLName.TextDim str As String = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1 As New SqlDataAdapter(str, con)
da1.Fill(ds, "Emp_Details")
dgEmp.DataSource = ds
dgEmp.DataMember = "Emp_Details"
dgEmp.DataBind()
Finally
con.Close()
End Try
End Sub
Thanks in Advance for your quick help.
Regards,
Brandy
View 2 Replies
View Related
Oct 10, 2007
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas?
Thanks Andy,
View 5 Replies
View Related
Mar 16, 2004
Hi,
I created a temporary table inside a stored procedure called TmpCursor and the last time I include this..
Select * from #TmpSummary
GO
Inside my web page, I have the following code...
QrySummary = "Exec TmpCursor"
Set rsSummary = Server.CreateObject("ADODB.RecordSet")
rsSummary.Open QrySummary, cnCentral
cnCentral is my sqlconnection string..
This is the error I got when viewing the page
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
BTW, the stored procedure works fine in Query Analyzer.
TIA
View 2 Replies
View Related
Apr 28, 2005
I would like to return the the Primary Key of the row altered from this query - what changes do I need to make?
UPDATE Members SET LastLog = @time WHERE UserName=@user AND Password=@pass;
Thanks in advance,
View 6 Replies
View Related
Apr 19, 2001
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
Thanks,
Jim
View 1 Replies
View Related
Aug 29, 2005
I have two tables and I want to return data from both. Currently my select statement is returning just 1 child record for each parent record and I want to return all child records that match the parent record.
Here's a sample of my tables/data/etc.
t1
------------
speciesid | species
1 | Mammals
2 | Rodents
3 | Reptiles
t2
---------
animalid | animal
3 | Skink
3 | Iguana
3 | Rattlesnake
2 | Meerkat
1 | Hippo
1 | Elk
What I want to do is pull up a list of all the species and under each list all the animals currently listed under that species.
So the result I want should look like:
Mammals (Hippo, Elk)
Reptiles (Skink, Iguana, Rattlesnake)
Rodents (Meerkat)
so currently I have:
SELECT A.animalid, S.speciesid, A.animal, S.species from t2 as A, t1 as S where S.speciesid=A.animalid order by species
this works great, it's just that it only returns one animal instead of all of the animals. Any help would be appreciated.
View 4 Replies
View Related
Jun 13, 2002
Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.
Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?
I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...
~Mike
View 2 Replies
View Related
