Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View Complete Forum Thread with Replies
Related Forum Messages:
SqlDataSource Set To Stored Procedure, Tests OK In Wizard, Does Not Return Data When Executed
With a Gridview Control, I set the SqlDataSource to be a stored procedure in a Sql Sever database. Using the wizzard to configure the datasource, the test returns lots of rows. After completing the wizzard, the gridview control does not show the column names in the VS2005 designer. For the gridview column headers the values are Databound Col0, Databound Col1, Databound Col2, ....) This tells me I have a problem. I tried the same thing with a simpler stored procedure. This test stored procedure does not call anything else, takes several input parameters, returns rows. The column names show in the gridview control as expected. So I am trying to figure out why the first case of gridview with sqldatasource is not working and the second case works . The stored procedure that is not working with my gridview calls multiple inner stored procedures and has #TEMP tables. My complex stored procedure ends with Select * from #Temp. Could the calling of other inner stored procedures and use of #temp tables contribute to the problem? If yes then what is the strategy for using a gridview and loading it with the data that the complex stored procedure returns?
View Replies !
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it? What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application. However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password. Looking forward for replies from expert here. Thanks in advance. Note: Hope my explaination here clearly describe my current problems.
View Replies !
Rows Unexpectedly Skipped While Loading Flat File
I tried to load a fixed width flat file with around 300,000 rows. However, only the first 8xxxx rows were loaded to the destineation table and the rest row were loading blank records. There was no error message showing during package execution. I've tried to split the file in half and the result was the same. So it wasn't the data file problem. Would there be any buffering issue I need to cater for inside the package? Thanks!
View Replies !
SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task. I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string. When I execute the task, I get the following error message. [Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I also tried mapping the parameter as XML type, but that didn't work either. If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable. Thanks
View Replies !
How Many Rows Will A Query Return?
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data? The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
View Replies !
Store Procedure Does Not Return All Rows Sometimes, Slow Execution...!!!
Dear friends,I have a problem with a simple select statement and I don't know why it is happening.I have 2 tables, Fees and FeesDataRoles. Fees presents all the fees and FeesDataRole is a middle table between Fees and Roles table. So each fee can have multiple Roles and a Role can have many Fees.Now I have a select statement:Select *From Fees Inner Join FeesDataRoles ON Fees.FeeID = FeesDataRoles.FeeIDWhere (FeesDataRoles.DataRoleID = @DataRoleID) AND (FeesDataRoles.RecordStatus = 1 ) AND (FeesDataRoles.ValidFrom >= getdate() ) AND ( FeesDataRoles.ValidTo <= getdate() OR FeesDataRoles.ValidTo is null)Now it shouldn't take that long to execute this procedure but surprisingly sometimes when I insert a value to the table and then execute this store procedure it does now show the data just added. Very strange.....!!!!I ran the procedure 5 times after inserting an item and nearly 1 out of 5 does not return the right result righ. ( It does not include the recently inserted rows)Anyone have any idea....?I used Tuning Advisor, no sugestion. I change the clustered index in FeesDataRoles from FeesDataRoleID(the primary key of the table) to DataRoleID to increase the performance, still it happens sometimes.Is my Where clause so costly that cause this problem.Please help. I really appreciate your help.Regards,Mehdi
View Replies !
SQL 2005 Stored Procedures Return No Rows
I am trying to execute a simple SELECT statement in a stored procedure from within Visual Studio 2005. The procedure executes but returns no rows. (There are rows in the table and a VIEW using the same SELECT statement does return the rows.) I added a PRINT statement to the stored procedure to see if it executed and it did print my text in the output window as expected. The SQL server is running on Windows Server 2003 with latest service packs and patches. I had detached the database while doing maintenance on our system and re-attached it later. I can not find any reason why it refuses to return rows. Larry
View Replies !
Stored Procedures Return Ing Mutiple Rows
hello. I am trying to speed up my asp.net caledar. I need a Stored Procedure that takes a Parameter. the Parameter will be a Date. then I want sql server to return all the classes that will happen on that date in formated string so i can display in the Asp.net caledar. the table looks like this classid classname classdate classtime (there are more than one class happening on the same day) I need the retuen value to look like this "Yoga 9:00am" & "<p>" & "Jazz 11:00am" & <p> can someone help me or point me to a sample. this is really hard. thank you
View Replies !
Query - Return All Rows Is Failing
When querying any of our database tables (returning all rows from within the enterprise manager) we are getting the following error message: "The query cannot be executed because some files are either missing or not registered" Can anyone help me with this....
View Replies !
SQL Query To Return Rows Only When A Max Limit On SUM Has Been Reached.
Hello SQL gurus!I am trying to write a query that will return a set of continguous rowsfrom a table, and limit the number of rows returned when a maximumtotal has been reached by adding a value in one of the columns.For example, the two columns below represent 2 columns in a table.a 2b 2c 2d 3e 4f 5g 5I want to start at, say "c", and return all the rows after it as longas the sum of the numbers in column 2 (starting at "c") don't exceed10. The result I'm after would be thusc 2d 3e 4....because 2 + 3 + 4 = 9 < 10Any ideas? Many thanks.
View Replies !
Query To Return Multiple Date Rows
HI there, Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example: 163610737464753422005-12-30 00:00:00.000 163610737464753592006-03-10 00:00:00.000 This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please? Thanks!!!!! S
View Replies !
LinkedServer Query Tries To Return All Rows Over Network
When running a query with a table from another SQL7 Server that is linked, if only 1 table from each server is specified, the query runs fast and returns only the data rows needed from the linked server. When another table is added on to query (on the server running the query) the query processor tries to run a remote query and return over 400,000 rows over the network. Any suggestions?
View Replies !
SQL Query History Table To Return Only Active Rows
Given the following data how do I make a SQL query that returns only 1 row per product? The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }). The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created. Example Data: HistoryID ProductID Name Color DateEffective RowTimeStamp (auto-number PK) 1 1 Wheel Red 2/1/2008 2/1/2008 2 1 Wheel Blue 3/5/2008 3/1/2008 3 1 Wheel Orange 3/5/2008 3/2/2008 4 1 Wheel Black 1/1/2010 3/3/2008 5 2 Knob Blue 3/2/2008 3/2/2008 6 2 Knob Green 3/3/2008 3/3/2008 Query should return: 3 1 Wheel Orange 3/5/2008 3/2/2008 5 2 Knob Green 3/3/2008 3/3/2008 The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes. Can anyone help?
View Replies !
Return Two Rows From One Rows Data
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View Replies !
Returning All Rows For A Stored Procedure
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 Replies !
Stored Procedure Returning No Rows
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 Replies !
Stored Procedure - Too Many Rows Returned
The following query only brings back too much data in the table - there are duplicates and the lab it shows is wrong. The problem is: AND a.calLab = f.ID in the Where statement. Some equipment does not need calibration, so, a.calDueDate = 0 AND a.calLab would be NULL or 0. tblLabs ID field has 1 - 18 (Labs) - no 0 and of course no null I need to get the rest of the data. Any suggestions? Thanks..... Zath SELECT a.assignedID, b.Manufacturer, a.modelNumber, a.serialNumber, e.equipmentType, a.Description, c.Location, d.Status, a.modifiedDate, a.modifiedBy, a.Notes, a.Picture, f.LabName, a.calibrationRequired, a.calDate, a.CalDueDate, a.assetNumber, a.ID FROM tblEquipments a, tblManufacturers b, tblLocation c, tblStatus d, tblEquipment_Type e, tblLabs f WHERE a.manufacturer = b.manufacturerID AND a.location = c.locationID AND a.Status = d.statusID AND a.EquipmentType = e.ID AND (a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0) ORDER BY a.ID
View Replies !
Stored Procedure Returning Too Few Rows
I can't even think how I would go about beginning to solve this problem, so any tips/pointers would help immensely. I'm running a query that builds a time line of a process using messages in a table called Event. The Event table is joined with a table called Charge_Info based on N_Charge, the charge number. It builds the time line by looking in the Message field for various text strings that signify a specific point has been reached in the process and builds a record for each charge number with data from N_Charge and timestamps from the events table. The problem I have is that there aren't records for every event in the Event table, ie, for every charge that doesn't have a "Heating Complete" event, the returned recordset has no mention of that charge. The system recording these events is pretty much crap, but there's nothing to be done about that right now. What I want to happen is for the query to return every record in N_Charge and a null string or NULL in place of the non-existing events. The stored procedure is pasted below. I pass the dates I want and select from the Event table into a temporary table and then do the join/text search on that table. It helped performance tremendously compared to the text search searching all 400,000+ records in the Event table and then doing the date select. Thanks for any help in advance, TimC SELECT EVENT.Time_Stamp, EVENT.N_CHARGE, EVENT.Message INTO #EVENT_FILTERED FROM EVENT WHERE (EVENT.TIME_STAMP >= @StartDate) AND (EVENT.TIME_STAMP < @EndDate) SELECT CHARGE_INFO.TIME_STAMP, CHARGE_INFO.N_CHARGE, CHARGE_INFO.BASE, CHARGE_INFO.N_RECIPE, CHARGE_INFO.N_FCE, CHARGE_INFO.N_CH, CHARGE_INFO.HEIGHT, CHARGE_INFO.CREW_EOC, CHARGE_INFO.CREW_SOC, CHARGE_INFO.TIME_START, CHARGE_INFO.TIME_FCE_SET, CHARGE_INFO.TIME_FCE_IGNITED, CHARGE_INFO.TIME_FCE_REMOVED, CHARGE_INFO.TIME_CH_SET, CHARGE_INFO.TIME_CH_REMOVED, CHARGE_INFO.WEIGHT, CHARGE_INFO.TIME_POST_PRG_COMPLETE, EVENT.TIME_STAMP AS IC_Set, EVENT_1.TIME_STAMP AS Cycle_Started, EVENT_2.TIME_STAMP AS Leak_Test_Done, EVENT_3.TIME_STAMP AS End_N2_PrePurge, EVENT_4.TIME_STAMP AS Heating_Complete, EVENT_5.TIME_STAMP AS Split_Temp_Met, EVENT_6.TIME_STAMP AS End_N2_Final_Purge, EVENT_7.TIME_STAMP AS Inner_Cover_Removed, EVENT_8.TIME_STAMP AS Cycle_Complete, EVENT_9.TIME_STAMP AS Post_Purge_Time_Met FROM dbo.CHARGE_INFO CHARGE_INFO LEFT OUTER JOIN #EVENT_FILTERED EVENT_9 ON CHARGE_INFO.N_CHARGE = EVENT_9.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_7 ON CHARGE_INFO.N_CHARGE = EVENT_7.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_6 ON CHARGE_INFO.N_CHARGE = EVENT_6.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_8 ON CHARGE_INFO.N_CHARGE = EVENT_8.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_5 ON CHARGE_INFO.N_CHARGE = EVENT_5.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_3 ON CHARGE_INFO.N_CHARGE = EVENT_3.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_1 ON CHARGE_INFO.N_CHARGE = EVENT_1.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_4 ON CHARGE_INFO.N_CHARGE = EVENT_4.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT_2 ON CHARGE_INFO.N_CHARGE = EVENT_2.N_CHARGE LEFT OUTER JOIN #EVENT_FILTERED EVENT ON CHARGE_INFO.N_CHARGE = EVENT.N_CHARGE WHERE (EVENT.MESSAGE LIKE '%Inner Cover Set%') AND (EVENT_1.MESSAGE LIKE '%Cycle Started%') AND (EVENT_2.MESSAGE LIKE '%Leak Test Done%') AND (EVENT_3.MESSAGE LIKE '%End of N2 PrePurge%') AND (EVENT_4.MESSAGE LIKE '%Heating Complete%') AND (EVENT_5.MESSAGE LIKE '%Split Temp Met%') AND (EVENT_6.MESSAGE LIKE '%End N2 Final%') AND (EVENT_7.MESSAGE LIKE '%Inner Cover Removed%') AND (EVENT_8.MESSAGE LIKE '%Cycle Complete%') AND (EVENT_9.MESSAGE LIKE '%Post Purge Time Met%') AND (CHARGE_INFO.TIME_STAMP >= @StartDate) AND (CHARGE_INFO.TIME_STAMP < @EndDate) ORDER BY CHARGE_INFO.BASE, CHARGE_INFO.TIME_STAMP DESC
View Replies !
Stored Procedure - Insert Many Rows From One Table To Another
Searched around for a sample, maybe I missed one?I have an insert statement....INSERT INTO objScores (objID, studentId, courseId) VALUES ( @objID, @userId, @course) This works fine, but problem is it reads only the first value from the tableDECLARE c_oId CURSOR FOR SELECT objID FROM objStructure FOR READ ONLYThe userID and course will always be the same.But, the objID, there will be many in the table objStructure such as Lesson1, Lesson2..... Lesson19I need it to read all of them, 1 - 19 or whatever the count, could be 3 and insert all of them into the table.So, the insert needs to input Lesson1, userID, course ----- Lesson2, userId, course ----- Lesson3, userID, course ---- and so on.It must read the objID from the objStructure table and just tranfer all that is there that = the course.Links? Suggestions?Thanks all,Zath
View Replies !
Returning Multiple Rows From A Stored Procedure
Hi,I have the following stored procedure that does some processing andputs the result in a temporary table. I tried several things thatprocedure to display output that I can access with ADO.Net, but itdoesn't work. It doesn't even display the result in the query analyzerunless I add SELECT @ReturnFullNameAny help?The stored procedure:CREATE PROCEDURE sp_SEARCH_MULTIPLE_NAMES @search4fatherOfvarchar(255), @maximum_fathers int = 100, @ReturnFullName varchar(255)Output....SELECT @ReturnFullName = name FROM #FULLNAME------------------------------------------------To Execute the stored procedure:DECLARE @test varchar(255)EXEC sp_SEARCH_MULTIPLE_NAMES @search4fatherof='Ù
رÙÙ
',@returnfullname=@testPRINT CONVERT(varchar(255), @test)
View Replies !
Insert Multiple Rows Using A Stored Procedure
I'm writing a Intranet web application to allow users to add presentation files to a web site for others to download. The presentations are to be grouped by categories, however I want them to be able to create additional categories if needed. I have created two tables. Table 1 - PresentationCategories Table 1 Fields - ID, Category Table 2 - PresentationFiles Table 2 Fields - ID, Name, Description, Filename, Filesize, CategoryID On my web page I want to call a stored procedure to insert records into the PresentationFiles table. I have check boxes on the web form for all the possible categories that exist. A user can check each category that this presentation applies too. In my stored procedure, how do I accomplish inserting a record for each category that is selected on the web form? I'm guessing that I'll need to pass the categoryID's parameter into the procedure as a delimited string and then process this string for each categoryID and insert records into the PresentationFiles table using a While loop. I'm just not clear on how this is accomplished. Any advice on how to do it differently or other resources that you can point me to is very much appreciated.
View Replies !
Stored Procedure - Delete Some Rows (table)
How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script.. Code: CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw /*************************************************************** ** CREATED: 12/04/2006 **************************************************************** ** DESCRIPTION: Clean the tblSoldRaw by deleting any data that ** is over 90 days old. We need keep the database ** as small as possible so the performance won't ** suffer on the server's and the client's machine. ** **************************************************************** ** NOTES: Hooked up to BookItOut Data Importer Windows Service **************************************************************** ** MODIFICATIONS: ** ** DATE WHO MODIFICATION DESCRIPTION ** ------------------------------------------------------------- ** ***************************************************************/ /*************************************************************** ** Uses: tblSoldRaw ***************************************************************/ AS DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90) RETURN ROWCOUNT ERROR_HANDLER: RETURN -1 SET NOCOUNT OFF Thanks...
View Replies !
Stored Procedure Not Returning Rows In Web Application
Hi, I Have created a stored procedure to use full text search and return the results back, When i run this procedure in the query analyzer it is working fine, but when i run this procedure from asp.net application, it is returning zero rows. I have checked all the parameters and everything in my web application, there is nothing wrong in there. Another stored procedure which almost do the samething with some different parameters is working fine on both ends. I am using Sql server 2005 (express) + VS.NET 2005. and using ASPNET to connect to database. Thanks in advance for any suggestions.
View Replies !
Problem With Stored Procedure Which Updates Rows
Hi dear users, I have created procedure which main function is to update row of the table. Below, there is SQL code of this procedure. Code Block CREATE PROCEDURE Zakl_UpdateRow @zakl_id INT, @zakl_nazwa VARCHAR(25), @zakl_miasto VARCHAR(20), @zakl_ulica VARCHAR(30) AS UPDATE Zaklady SET Zakl_Nazwa=@zakl_nazwa, Zakl_Miasto=@zakl_miasto, Zakl_Ulica=@zakl_ulica WHERE Zakl_ID=@zakl_id When I execute this procedure I have to give all declared variables: "@zakl_id", "@zakl_nazwa" and so on. Sometimes I don't want to update all columns in the row but only one f.ex name of the street ("Zakl_Ulica"). And I want other colums of the row to stay the same ("Zakl_Nazwa","Zakl_Miasto"). There is no point in rewriting values of "Zakl_Nazwa","Zakl_Miasto" as I want only to change value of "Zakl_Ulica". this is execution clause Code Block EXECUTE Zakl_UpdateRow @zakl_id=70, @zakl_ulica='kosciuszki 7' If I don't ascribe value to the variables (@zakl_nazwa, @zakl_miasto ), the columns in the row ("Zakl_Nazwa","Zakl_Miasto") should stay unchanged. So what should I modify in my procedure, to have it working? Best Regards Maciej
View Replies !
Stored Procedure Returning Multiple Rows
I have a stored procedure which return a single value and one which return multiple rows between two colums. In my code for the procedure which returns a single value i use (executescalar) which works fine. I am not sure what command to use in my code when i am calling the stored procedure that returns multiple rows between colums. Any help would be appreciated. Thanks.
View Replies !
Stored Procedure For Deleting Multiple Tables/rows
Hi, I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID]. I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03. I have come up with something like that but it does not seems to be correct. CREATE PROCEDURE [sp_delete_test01_1] (@id [int]) AS DELETE [test01] DELETE [test02] DELETE [test03] WHERE ( [id] = @id)GO Your advise please. Many Thanks.
View Replies !
Stored Procedure Multi Update Problem - Limited Rows
hi need help i have this stored procedure the problem is that i canot update like this not more than 20 - 30 rows (i send it from a web page from check box) it work but it limited rows for update not more than 20 - 30 rows in one time Code Snippet SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [sto_update_snha] @id varchar(1000) as UPDATE Snha SET fld5 = 3 WHERE charindex(','+CONVERT(varchar,[id])+',',','+@id+',') > 0 AND (fld5 = 2) TNX
View Replies !
Need A Query To Return Rows Containing &"ALL&" Matching Fields
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables). I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z". The "All" part is very important. For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete. I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q". I hope I am clear enough. Does anyone have any solutions???
View Replies !
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View Replies !
Stored Procedure That Return Dataset(Select Query)
Hi all, I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario. Thanks and have a nice day to all.
View Replies !
Need Help In Return How Many Rows
HI All, I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that? This is my sp, and when it returns the data i also want it to return the how many rows are the result. CREATE procedure dbo.ph_getphonebookoption @country nvarchar(100), @company nvarchar(100), @office nvarchar(100) as select Ext, FName, LName, Title from phonebook where country =@country and company=@company and office =@office select count (*) GO
View Replies !
How To Return A Specified # Of Rows
Trying to do a paging scheme without using #Temp tables in MS SQL 7.0 Client calls a sp passing 1 and sql returns the first 100 records. Client sends a sp passing 100 and gets the next 100 records. Process continues till @@fetch_status <> 0 or the client can stop sending requests. I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit. What other options do I have? Thanks, John
View Replies !
Return Rows Other Than Top 10
Hi All, I have a question, Select top 10 * from employee the above statement return top 10 row. but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005 Thanks for the help Thanks Bhaskar
View Replies !
Sql Help With Number Of Rows Return Value
hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
View Replies !
What Is Better? Return All Rows Or Nested TOP X?
I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this? SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND... OR SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder
View Replies !
Exec An SP To Return Rows To Another SP
Hi, Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling a SELECT query from another SP?? Something like... CREATE procedure spThisSP(@param varchar(20)) AS SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param) Cheers
View Replies !
How To Return A Range Of Rows?
How can a SQL statement be written to return a specified range ofrows? For example:-- tblContact-- (-- SSN char(9),-- FirstName varchar(50),-- LastName varchar(50)-- )-- This table contains 500 rows.Select * from tblContact -- Return only rows 5 through 10Thanks
View Replies !
|
TRACKER
