Order BY Problem In Stored Procedure
Jun 9, 2004
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 2 Replies
ADVERTISEMENT
Jan 4, 2008
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 14 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Mar 5, 2008
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 10 Replies
View Related
Nov 5, 2003
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 5 Replies
View Related
Jul 7, 2004
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 1 Replies
View Related
Jul 7, 2004
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 1 Replies
View Related
Nov 4, 2005
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 8 Replies
View Related
Sep 28, 2012
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?
View 3 Replies
View Related
Apr 28, 2008
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 7 Replies
View Related
Jul 20, 2005
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 4 Replies
View Related
Feb 17, 2008
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 2 Replies
View Related
Mar 24, 2004
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?
Thanks for your help!!
View 6 Replies
View Related
Jul 20, 2005
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 7 Replies
View Related
Dec 20, 2006
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 1 Replies
View Related
Jul 27, 2007
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?
View 2 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Sep 19, 2006
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
View 1 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Jun 25, 2014
I have a query using the sys.dm_exec_query_stats that returns the last exec of all proc calls for the last day and execution times.
Is there a way to get all the executions and not just the last w/o using a db trace?
View 1 Replies
View Related
Apr 6, 2000
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 1 Replies
View Related
Jul 23, 2005
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 4 Replies
View Related
Oct 10, 2006
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies
View Related
Nov 15, 2006
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
Thanks,
Kevin
View 3 Replies
View Related
May 18, 2007
This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/2/2007 4:52:19 PM ******/
CREATE PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
IF (@i_WildCardFlag=0)BEGIN
SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
Please help me regarding the same.
Thanks in advance.
Sandeep Kumar
View 2 Replies
View Related
