INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
i use microaccess create table, there is a filed call"Complete_PO", value"yes/no" i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc" PoNum_SqlDataAdapter.Fill(PO_DataSet1) TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()
i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004 i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC" i checked my database, last record is LP-0545381-0300 in my debuging process, surprisingly found that selected record is LP-0545381-301 ! any one hav any suggestion? ^_^
Is it possible to have to ORDER BY statements in the same storedprocedure?I am trying to use the same stored procedure for two different pagesbut the data returned needs to sorted DESC on one page and ASC onanother. Below is my SP:CREATE procedure sp_getLeads@p_SortType int,@p_PropID intASSELECT ID, PropID, Name, StatusFROM LeadsWHERE PropID = @p_PropIDIF @p_SortType = '1'(ORDER BY DateCreated DESC)ELSEIF @p_SortType = '2'(ORDER BY DateCreated ASC)ENDRETURN 1GOCan someone tell me what I am doing wrong?
I am trying to create a stored procedure that is sent a column name and orders by the column name. conn = New SqlConnection(SQLserver)SQL = New SqlCommand("SearchECN", conn) SQL.CommandType = CommandType.StoredProcedure SQL.Parameters.Add("@Search", SqlDbType.Variant)SQL.Parameters.Add("@Sort", SqlDbType.Variant) SQL.Parameters(0).Value = Search.Text SQL.Parameters(1).Value = "ECN.ECN" SQL.Connection.Open() GVECN.DataSource = SQL.ExecuteReader() GVECN.DataBind() SQL.Connection.Close() SQL.Connection.Dispose()
Stored Procedure @Search NVARCHAR(MAX), @Sort NVARCHAR(MAX) SELECT ECN.ECN, ECN.A, ECN.B FROM ECN WHERE ECN.ECN LIKE @Search OR ECN.A LIKE @Search ORDER BY @Sort I get the following error Msg 1008, Level 16, State 1, Line 10 The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How do you use a variable in the ORDER BY command in a sql statement. I currently have: **************************************** CREATE Procedure SS_POList ( @CompanyID nvarchar(10), @PO varchar (20) = '%' ) As
SELECT SS_Sendback.EndUserPO, SS_Sendback.PO, COUNT(SS_Sendback.EndUserPO) as pocount, SUM(SS_Sendback.Customerprice) as totcost
FROM SS_Sendback
WHERE SS_Sendback.EndusercustomerNumber = @CompanyID AND SS_Sendback.EnduserPO Like @PO
GROUP BY SS_Sendback.EndUserPO, SS_Sendback.PO
ORDER BY SS_Sendback.PO GO ************************************* I changed it to ************************************* CREATE Procedure SS_POList ( @CompanyID nvarchar(10), @PO varchar (20) = '%', @Order varchar(20) ) As
SELECT SS_Sendback.EndUserPO, SS_Sendback.PO, COUNT(SS_Sendback.EndUserPO) as pocount, SUM(SS_Sendback.Customerprice) as totcost
FROM SS_Sendback
WHERE SS_Sendback.EndusercustomerNumber = @CompanyID AND SS_Sendback.EnduserPO Like @PO
GROUP BY SS_Sendback.EndUserPO, SS_Sendback.PO
ORDER BY @Order GO
and I receive the following error.. error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help. Thanks
Hi I´m newbie using SP, so excuse me if my doubt is stupid!!!
I´m trying to do a SP with like this
create procedure Test @arg char(10) as
select * from table
order by @arg
go
when i try to create this i got this error:
The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
This is possible to do?What i want is the query sorted by the variable i´ve passed(in this case arg).
I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.
CREATE PROCEDURE procedure_name @regNum varchar(14), @sortOrder tinyint = 1 AS SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register', Obs_Date As 'Observation Date' FROM tblSPG_Header WHERE REG = @regNum UNION SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register', Obs_Date As 'Observation Date' FROM tblRCH_Header WHERE REG = @regNum ORDER BY Obs_Date DESC GO
Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:
ORDER BY CASE WHEN @sortOrder = 1 THEN Obs_Date END DESC
but I frequently get the following error when I try to do so:
"ORDER BY items must appear in the select list if the statements contain a UNION operator"
If anyone can offer any suggestions, I would appreciate it. Thanks.
I need to create a conditional if or case statement in SQL Server 2000 for a stored procedure. Basically if the value passed in is 1,2 or 3 then it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
i am creating a Stored Procedure and trying to set the value for the sort column value as a variable which is passed from my c# program. But i get an error..is there a way, where i can specify a dynamic value for the sort column in the order by clause . My Procedure is as follows :-
ALTER PROCEDURE PhoneBook_search @startdate varchar(30), @enddate varchar(30), @sortType varchar(15),
AS DECLARE @starttime AS varchar(30) DECLARE @endtime AS varchar(30) SET @starttime = @startdate + " 00:00" SET @endtime = @enddate + " 23:59"
SELECT * FROM phonebook WHERE (exp_date BETWEEN @starttime AND @endtime order by @sortType )
I can't figure out why this won't work. I want to use a variable for the 'ORDER BY' in my Stored Procedure.
I use this to order my results by product price or alphabetically from a dropdown menu. I have tried the following, but get the error below.
Code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[SPResults]
[Code] ....
Code: Msg 1008, Level 16, State 1, Procedure SPResults, Line 21
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Is this possible to do?
I have this stored procedure: SELECT * from purchase ORDER BY CASE @OrderBy WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100)) WHEN 'canceled' THEN cast(canceled as bit) END
the @OrderBy is a nvarchar(100) parameter traveller_name is an nvarchar(100) field canceled is a bit field
When I execute the stored procedure it works fine until I execute it sorting by canceled field. After that, I cannot sort it again using the traveller_name field. I get this eror: Conversion failed when converting the nvarchar value 'Jason' to data type bit. (Jason is a record in the traveller_name) Removing the castings and sorting by just the column name does not help.
I've encountered some strange behavior in a recursive procedure I'mwriting for a bill of materials. First let me ask directly if what Ithink is happening is even possible:It seems like the procedure is not following the recursion in serialorder, but in parallel. In other words, after one instance of theprocedure calls itself, it continues executing lines below therecursion before the recursion is done. Is that possible? I lookedfor SQL Server Options that might deal with recursion or threading butI couldn't find anything.Now let me explain what's happening in terms of the BoM. All the rowsI expect are returned, but not in the correct order. Let's assume thefollowing tree:1|-2| |-5| | |-7| | -8| -6| -9|-3| |-10| |-11| | |-13| | -14| | |-15| | |-16| | -17| -12| -18| -19| -20| |-21| -22-4-23|-24-25-26This is stored in table P using MemberID and ParentID fields. Forexample,MemberID ParentID-------- --------1 NULL2 13 14 15 26 2(etc...)Based on how I wrote the recursion (I will provide the procedurebelow), I would expect output when starting from MemberID of 1 to looklike this:MemberID Depth Sort-------- ----- ----2 1 15 2 27 3 38 3 46 2 59 3 6(etc... basically, the line order of the graphical tree above, or acounter-clockwise traverse around the tree)Instead, I get this (I'll provide the whole thing because I don't seea pattern):MemberID Depth Sort-------- ----- ----2 1 15 2 23 1 210 2 37 3 34 1 36 2 39 3 423 2 48 3 411 2 413 3 512 2 524 3 525 3 618 3 614 3 615 4 719 4 726 4 720 5 816 4 817 4 921 6 922 6 10Call me crazy, but it looks like my tree was parsed in the same orderthat a set of dominos arranged in the same shape would topple. Theonly way I could see that happening is if the recursion is non-linear,allowing both children and siblings to be parsed simultaneously. Itwould also explain why my sort counter didn't increment properly, butthe depth counter is always correct.Now here are the procedures. There's also a Qty column, since this isa BoM after all, but I didn't need to mention it for my illustrationof the problem above.CREATE PROC makebom @root bigint---- This would be called by the client to find all the parts andquantities-- under a specific part (@root)--ASSET NOCOUNT ONCREATE TABLE #result (MemberID bigint, Qty bigint, Depth bigint, sortbigint)EXEC bomrecurse @root, 1, 0SET NOCOUNT OFFSELECT MemberID, Qty, Depth, sort FROM #result ORDER BY sortGOCREATE PROC bomrecurse @root bigint, @depthcounter bigint,@sortcounter bigint---- This is the recursive procedure, called once by makebom, butrecalling-- itself until the whole tree is parsed, filling the #result table--ASDECLARE @memberid bigint, @qty bigint, @nextdepth bigintDECLARE children_cursor CURSOR LOCAL FORSELECT MemberID, Qty FROM PWHERE ParentID = @rootORDER BY MemberIDOPEN children_cursorFETCH NEXT FROM children_cursorINTO @memberid, @qtyWHILE @@FETCH_STATUS = 0BEGINSET @sortcounter = @sortcounter + 1INSERT INTO #result VALUES (@memberid, @qty, @depthcounter,@sortcounter)SET @nextdepth = @depthcounter + 1EXEC bomrecurse @memberid, @nextdepth, @sortcounterFETCH NEXT FROM children_cursorINTO @memberid, @qtyENDCLOSE children_cursorDEALLOCATE children_cursorGOI'm surprised this even worked as well as it did because I'm a newbiewhen it comes to stored procedures and I put this together fromexamples I found around this group, online and in the T-SQL Help. Sofeel free to comment on other aspects of my code or approach, but I'mmost interested in understanding the behavior of this recursion.
Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate] -- Add the parameters for the stored procedure here @finfileid int, @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId, SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId) AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId) AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName, MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName, MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId, MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM ProjectFinFileItems INNER JOIN ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)
Hi, I need to include two input variables in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me to do so and gives an Error 1008. How can i solve this problem?
hi there,i am using sql server 7. below is the stored procedure that is givingme grief. its purpose it two-fold, depending on how it is called:either to return a pageset (based on page number and page size), or toreturn IDs of previous and next records (based on current record id).the problem is, that the order in which records are inserted into thetemp table is inconsistent, even though the calling statement and theorder by is always the same: sometimes records are ordered correctly,by project_number, and sometimes the order is broken starting at somerecord (which is always the same).i have no idea what is wrong here, i would appreciate any help!thanks so much.here is the calling statement:EXECUTE spProjects 2,null,'project_number','asc','',6,50here is the proc:CREATE PROCEDURE spProjects@action int,@currID int,@sortBy varchar(50),@sortDir varchar(4),@searchBy varchar(255),@Page int,@RecsPerPage intASSET NOCOUNT ONDECLARE @nextID intDECLARE @prevID intDECLARE @currRow intDECLARE @rowCount intDECLARE @firstRec intDECLARE @lastRec intDECLARE @total intDECLARE @more intDECLARE @sortBy2 varchar(50)-- setup temp tableSELECT r.id as row, r.*,a.name agr_type,pu.name purpose,sp.name sponsor,pr.name prime,p.lname p_lname, p.fname p_fname, p.mname p_mi, p.emailp_email,o.name org,convert(varchar(10), r.created_date, 101) adddate_c,convert(varchar(10), r.updated_date, 101) upddate_cINTO #project_temp_tableFROM spm_projects r, spm_agreement_types a, spm_purpose_typespu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs oWHERE 1 = 0IF @sortBy IS NULL SELECT @sortBy = 'project_number'IF @sortBy = '' SELECT @sortBy = 'project_number'SELECT @sortBy2 = @sortBy + ' ' + @sortDirIF @sortBy NOT LIKE '%project_number%' SELECT @sortBy2 = @sortBy2 +', project_number'-- get projectsEXEC ('INSERT INTO #project_temp_tableSELECT r.id as row, r.*,a.name agr_type,pu.name purpose,sp.name sponsor,pr.name prime,p.lname p_lname, p.fname p_fname, p.mname p_mi, p.emailp_email,o.name org,convert(varchar(10), r.created_date, 101) adddate_c,convert(varchar(10), r.updated_date, 101) upddate_cFROM spm_projects r, spm_agreement_types a, spm_purpose_typespu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs oWHERE r.agreement_type_id = a.idAND r.purpose_type_id = pu.idAND r.sponsor_id = sp.idAND r.prime_id *= pr.idAND r.pi_id = p.idAND r.org_id = o.id' + @searchBy + 'ORDER BY ' + @sortBy2)SET @rowCount = 0-- number recordsUPDATE #project_temp_table SET @rowCount = row = @rowCount + 1-- prev/nextSELECT @currRow = row FROM #project_temp_table WHERE id = @currIDSELECT @prevID = id FROM #project_temp_table WHERE row = @currRow -1SELECT @nextID = id FROM #project_temp_table WHERE row = @currRow +1-- pagingSELECT @firstRec = (@Page - 1) * @RecsPerPageSELECT @lastRec = (@Page * @RecsPerPage + 1)SELECT @more = COUNT(*) FROM #project_temp_table WHERE row >=@LastRecSELECT @total = COUNT(*) FROM #project_temp_tableSET NOCOUNT OFF-- prev/nextIF @action = 1 SELECT @prevID as prevID, @nextID as nextID--pagingIF @action = 2SELECT *, @more as more, @total as totalFROM #project_temp_tableWHERE row > @firstRec AND row < @lastRecDROP TABLE #project_temp_table
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
can i pass the name of the table and the "order by" column name to stored procedure? i tried the simple way (@tablename varchar and then "select * from @tablename) but i get error massesges. the same for order by... what is the right syntex for this task?
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
Hi, Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderDetails table for OrderID.
I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders grouped by OrderID that I insert into the table eg.
I have just started using SQL so am not sure how I go about writing the code for the trigger ? It would seem like I will need to use the Max function in the code.
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons');
then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75));
select * from test_sort order by cast( description as nvarchar(75));
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
When i process a order in table Orders_t i would like to check in storage_t if we defenetly have it in storage. .... if we have it in storage, i decrease the "amount" by 1 ..(amount -1), and process the order. Otherwise it will return nothing.
This is what i´ve come up with so far:
CREATE TRIGGER checkInStorage ON orders_t FOR INSERT, UPDATE
AS DECLARE @tOrderId char(3), BEGIN SET @tOrderId = (SELECT orderId FROM INSERTED)
--check if the amount in storage
IF EXIST(SELECT amount FROM storage_t WHERE orderId = @tOrderId and amount >= 0) BEGIN --if it return true, i update the storage by decrease the amount with one UPDATE storage_t SET (amount = amount - 1) WHERE orderId = @tOrderId END
