How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
below is a Stored Procedure that i have wrote in creating a PO:
CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)
AS
BEGIN
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?
CREATE TRIGGER trgInsertPOItem
ON PurchaseOrderItem
FOR INSERT
AS
BEGIN
'What do i entered???'
END
RETURN
help is needed asap! thanks!
View Complete Forum Thread with Replies
Related Forum Messages:
I Wanna Query A Purchase Order Table!...but Can Not:(
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()
View Replies !
Purchase Order Query: Very Funny And Challenge ^_^
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? ^_^
View Replies !
Two Order By's In Same Stored Procedure?
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?
View Replies !
Conditional Order By Stored Procedure
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. Fuzzy
View Replies !
Stored Procedure With Order By Variable
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. Any Ideas
View Replies !
Dynamic ORDER BY Within Stored Procedure
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.
View Replies !
Stored Procedure -order By Question
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). If someone know how to do this, please tel me. Thanks.
View Replies !
ORDER BY Command In SQL Stored Procedure
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
View Replies !
Order Of Recursion In Stored Procedure
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.
View Replies !
Order BY Problem In Stored Procedure
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 ) Any help is appreciated !!!
View Replies !
Dynamic ORDER BY In Stored Procedure
Hello, 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. Any ideas? Thank you
View Replies !
Dynamic Sql Where And Order By Clauses In Stored Procedure
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 :)
View Replies !
Inconsistent Order By Using Insert Into In A Stored Procedure
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
View Replies !
Stored Procedure To Update A Display Order Field.
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
View Replies !
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
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 Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
Find Order By Date Range Or Order Id
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
View Replies !
Recordset's Order And Database's Physical Order?
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!
View Replies !
Default Sort Order When Order By Column Value Are All The Same
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?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
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?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); 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)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
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? Thanks,
View Replies !
Use Trigger To Number Each Order Line
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. OrderID LineNumber 1 - 1 1 - 2 1 - 3 2 - 1 2 - 2 3 - 1 3 - 2 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. Thanking you in advance.
View Replies !
Trigger Vs Uniqueness Constraint Order
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. Regards, Jon Reade.
View Replies !
Order Of Records In The INSERTED/DELETED Tables In A Trigger
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. Thanks! CB
View Replies !
Create Trigger That Prevent Order Being Processed If The Amount Is 0 In Storage
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 this doesn´t work...
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Order ID For Latest Order For Every Customer
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID. Any ideas? Thanks, Assaf
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? Thanks.
View Replies !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Stored Procs - Specifying ORDER BY As A Parameter
hi all, hope someone can help....i'm having trouble calling an SP where the ORDER BY operator is specified asa parameter when the SP is calledmy SP is.....CREATE PROCEDURE dbo.sp_CustSearch (@SearchFor VARCHAR(80) , @SortOrderVARCHAR(50))ASBEGINSELECT first_name, last_name, postcode , address, townFROM customer WITH (nolock)WHERE (UPPER (last_name) LIKE '%' + @SearchFor + '%'OR UPPER(address.postcode) = @SearchFor )ORDER BY @SortOrderENDGOthe line causing the problem is ORDER BY @SortOrderthe error i get on checking the syntax is:"Error 1008: The SELECT item identified by the ORDER BY number 1 contains avariable as part of the expression identifying a column position. Variablesare only allowed when ordering by an expression referencing a column name"anyone know how to solve this? i'm guessing it's something simple.enclosing @SortOrder in single quotes gives, unsuprisinlgy, unsortedresults.trying ORDER BY '[' + @SortOrder + ']' gives the same error asabovethe only way i've managed to get it working so far is to dynamically buildthe SQL statement and then execute that at the end. it's a little messywhich was why i was trying to get the above working.thanks in advance.
View Replies !
ORDER BY Clause In A Stored Proc?
Can you put an ORDER BY clause in a stored procedure? What I'd like to do is have a stored procedure where the proc could be called, with an ORDER BY clause passed on as a variable, as in: CREATE PROCEDURE dbo.select_all_from_users @order_by varchar(100) AS SELECT * from USERS ORDER BY @order_by; This doesn't work, I get the following nastygram thrown in my face "Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression itentifying a column position. Variables are only allowed when ordering by an expression refrencing a column name." That's where I'm stuck. The variable @order_by WILL be refrencing a column name, at least it will in my opinion, but the SQL Server doesn't think so... Any ideas or workarounds? Alan McCollough
View Replies !
Referencing One Item's Hidden Property In Order To Set Another's Hidden Property
Hello, I have a group I'll call G4. The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group. When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time. At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown. When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point... =IIF(reportitems!textbox66. When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value. If I complete the IIF statement manually so that it spells out ..... =IIF(ReportItems!Textbox66.Hidden = False, True, False) ...the report chokes on it. So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group? Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
View Replies !
Can't Add New Stored Procedure Item In Database Project
I'm trying to create a CLR stored procedure and went ahead and created a Database Project. But when I click on New Item... it doesn't contain "Stored Procedure" as an item, just script items. How do I add this template? At first I was thinking well maybe it knows my SQL Server doesn't have CLR turned on, so I went ahead and turned that on but still doesn't show up. Any ideas? Thanks, Craig
View Replies !
Validation Of Returned Order Of Fields From A Stored Proc
Hi, Can we validate the returned order of fields from a stored procedure? Infact, i am taking a query as user input and extracts the results based on the query but for that order of fields specified in a query is important. Can i check the order after the query is run i.e if this is entered "select field1,field2,field3 from table" then i need to check the order of the resultset generated. I can't check the query before the resultset is generated because a user can enter bunch of queries. Any way will work, tsql or .net app. Thanks,
View Replies !
Sysdepends And Compiling Stored Procedures In The Correct Order
Hello: We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2. As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as: "CREATE PROCEDURE: ep_invoiceheaderformat_spv0101 Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay" And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts. I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me. Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not My question is: 1) Is there a way I can read and understand what the data means in sysdepends? 2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order? 3) Any other suggestions? Any information which can be proveded will be greatly appreciated. Thanks. David Spaisman
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I noticed the StockDate is not sorted in proper order, like ascending order... Code: select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd... The sample data is MM/DD/YYYY... So, how do I get it ordered propery by Year, Month then Day??
View Replies !
Unable To Execute Stored Procedure When New Item Is Selected In Dropdown.
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
View Replies !
Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View Replies !
Stored Procedure Or Trigger Or Both Or ???
HiThis problem involves 2 columns in my Product table - ReleaseDate(varchar) and ReleaseClass(varchar)When a new product is Entered, the current date is inserted into ReleaseDate & ReleaseClass = NewWhen product is a month old, I want ReleaseClass to = RecentWhen product is a month 3 mths old, I want ReleaseClass to = NormalWhen the product is a month old, is there a method to automatically run a Stored procedure say to change the ReleaseClass field??any code or links on how i go about this would be appreciated + i've never used a triggerCheers!!
View Replies !
Trigger Vs Stored Procedure
Hi, Please help me to find this answer. We know Trigger are a type of stored procedure,and can be activated whether by a insert ,update or delete event of a table. We also know that stored procedure are quick due to their execution plan which are already stored in the memory once complied. But what about triggers? what is the mechanism of triggers?How they work? And how fast they are from Tsql queries?Is there any mechanism to calculate or measure the efficiency of triggers? Please comment if anybody knows the answers. Thanks!! Joydeep
View Replies !
Get Return Value From Stored Procedure And Trigger
Hello there, I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter? Thank you for your help.
View Replies !
How To Approach (Trigger-Stored Procedure )
Hi AllI need opinions on how to approach my task.I currently have 3 tables: the master table, the archive and a temptable.MASTER: has 3 fields ProductID and ProductNo and ReleasedARCHIVE: Has 3 ProductID, ProductNo, SoldDateTEMP: ProductID, ProductNo, SoldDateI have a trigger on the master table upon deletion to archive. This istriggered from a seperate routine from a vb app to delete a recordreal time.CREATE Trigger Archive_Proc On dbo.MASTERFor DeleteAsDeclare @iDate As DateTimeSet @iDate = GetDate()If @@RowCount = 0 Returnset Nocount onInsert Into ARCHIVE(ProductID, ProductNo, SoldDate)Select ProductID, ProductNo, @iDate from deletedMy problem is that I have a temp table that gets filled from aseperate transaction.It needs to be matched against the master tablethen deleted at both master and temp. but the issue is that the temptable contains its own SoldDate value that needs to be archived.Q 1: if I use a stored proc. how do i pass the SoldDate value to thetrigger as Triggers dont use GVs.Q 2: How do I set up the stored procedure to delete with multipletables. I can get it to UPDATE but not delete....CREATE PROCEDURE COMPARESOLD@Pool SmallintASSet NoCount onUpdate MASTERSet Released = 2From TEMP, MASTERWhere TEMP.ProductNo = MASTER.ProductNoAND TEMP.ProductID = MASTER.ProductIDAND INVENTORY.Released = 1hopefully someone can lead me to the right direction...Thanks
View Replies !
Trigger Executing A Stored Procedure
I am having a issue with executing a stored procedure from a trigger. I have two tables, a staging table, and a live table. The staging table has a FOR INSERT trigger on in that executes a stored procedure, and also uses some error handling. However, I have placed a primary key on the live table, and I'm inserting data into the table that will violate that primary key. This is to test that the transaction gets rollback correctly. However, the data never gets inserted into the staging table, nor will any raiserror kick off. The stored procedure gets called, and errors out with out calling my if @@ERROR <> 0 statement, or inserting data into my staging table. How can I still insert data into the staging table and call the @@ERROR statement? Can I check the constraints of the table called by the stored procedure before actually calling the stored procedure? I have also tried the INSTEAD OF INSERT as well, that doesn't work either. Thanks.
View Replies !
Trigger Or Stored Procedure Question
Persons Table PersonID int NOT NULL PRIMARY KEY PersonFatherID int NULL FOREIGN KEY Persons(PersonID) PersonMotherID int NULL FOREIGN KEY Persons(PersonID) PersonGeneration int NULL PersonFirstName nchar(20) NOT NULL PersonLastName nchar(20) NOT NULL Spouses Table SpouseID int NOT NULL PRIMARY KEY HusbandID int NOT NULL FOREIGN KEY Persons(PersonID) WifeID int NOT NULL FOREIGN KEY Persons(PersonID) Persons Table Data PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName 1 1 1 1 Adam Smith 2 2 2 1 Evelyn Smith 3 1 2 2 Caleb Smith 4 NULL NULL 0 Sara Jones Spouses Table Data SpouseID HusbandID WifeID 1 1 2 2 3 4 I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated. Thanks, Mohan John
View Replies !
Calling Stored Procedure In Trigger
Hi I have a problem calling stored procedure in trigger.. When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value.. I have handled exception by returning values in case if any.. Here is the stored procedure CREATE PROCEDURE BidAllDestinations ( @ITSPID int, @DestinationID int, @BidAmount decimal (18,4), @BidTime datetime, @intErrorCode int out ) AS DECLARE @GatewayID int DECLARE @GatewayExist int SET @GatewayID = 0 SET @GatewayExist = 0 SET @intErrorCode = 0 UPDATE BID FOR CORRESPONDING GATEWAY DECLARE GatewayList CURSOR FOR SELECT Gateways.GatewayID FROM Gateways INNER JOIN GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN ITSPs ON Gateways.ITSPID = ITSPs.ITSPID Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID OPEN GatewayList FETCH NEXT FROM GatewayList INTO @GatewayID IF (@GatewayID = 0) SET @intErrorCode = 1 ELSE BEGIN -- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH WHILE @@FETCH_STATUS = 0 BEGIN SELECT@GatewayExist = Gatewayid FROMTerminationBids WHEREGatewayid = @Gatewayid AND DestinationID = @DestinationID IF @GatewayExist > 0 UPDATE TerminationBids SET BidAmount = @BidAmount, BidTime = getdate() WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID ELSE INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount) VALUES (@GatewayID,@DestinationID,@BidAmount) IF @@ERROR <> 0 BEGIN GOTO PROBLEM CLOSE GatewayList DEALLOCATE GatewayList END FETCH NEXT FROM GatewayList INTO @GatewayID END CLOSE GatewayList DEALLOCATE GatewayList END PROBLEM: BEGIN SET @intErrorCode = 100 END RETURN @intErrorCode GO TRIGGER CODE::: CREATE TRIGGER TR_TerminationBid ON dbo.TerminatorBidHistory FOR INSERT AS DECLARE @ITSPID int DECLARE @DestinationID int DECLARE @BidAmount decimal (18,4) DECLARE @BidTime datetime DECLARE @intErrorCode INT DECLARE @DistinationList varchar (8000) DECLARE @DestinationLevel varchar (100) SET @intErrorCode = 0 SET @ITSPID = 0 SET @DistinationList = '' -- CHECK ITPSID' S VALIDITY SELECT@ITSPID = i.ITSPID, @DestinationID= i.DestinationID, @BidAmount = i.BidAmount, @BidTime = i.BidTime FROM Inserted i INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output SELECT @intErrorCode Following should return value for @intErrorCode if any exception occures Any one can help what is wrong with it? Thanks
View Replies !
|
TRACKER
