SQL Server 2012 :: How To Order By Using Parameter Value
Jul 15, 2014
I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.
currently I am ordering it by:
c.CurrentItemNumber
, p.PriorItemNumber
among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).
Is this possible?
This is my stored procedure for reference:
ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)
the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?
In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:
'Document Date (most recent date first)' 'Document Number (highest number first)' 'Document Date (most recent first) and Number' 'Document Number (lowest number first)'
The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.
So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.
The code is:
DECLARE @ls_OrderBy varchar(80) --'Document Number (highest number first)' --'Customer and Document Date (most recent date first)' --'Customer and Document Number (highest number first)' --'Document Date (most recent first) and Number'
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment ( @startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())), @enddate Date = (Convert(Date,@StartDate-90) ) RETURNS TABLE AS RETURN ( SELECT EquipID, EmpName, IssueDate FROM HR.Equipment WHERE IssueDate <=@StartDate and IssueDate >=@EndDate ) GO
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive HallP6309 N Olive <---- grouped with the C record because they have the same address HallE5488 W Catalina <---- back to alphabetical by first name HallJ7222 N Cocopas
Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?
I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.
The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.
USING (select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId from [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID and LKC.seq = 1 ) AS LKC2 (comboID,lockID,seq,combo)
What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes
and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus
can you pass a parameter value to the CTE that I just listed above? If so, would you show me
I have a procedure (a) where i call another procedure (b) passing @message as a parameter to procedure B.
Ex: Create procedure a as Begin declare @prevyear exec b @message = 'avvasdva' + cast(@prevyear as varchar) End
When i execute above procedure, i get error at + sign i.e. @message parameter. how can i concatenate string with another parameter when passing parameters
I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.
See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0
CREATE TABLE #temp ( Id int, Name char(30), YesNo bit ) INSERT INTO #temp (Id, Name,YesNo) Select 1, 'a', 0 INSERT INTO #temp (Id, Name,YesNo)
In t-sql 2012, the followinng sql works fine when I declare @reportID.
IF @reportID <> 0 BEGIN SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue UNION SELECT 'Students report 2', 2 UNION
[code]...
However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:
FUNCTION [dbo].[fn_splitString] ( @listString VARCHAR(MAX) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l CROSS JOIN dbo.sequenceNumbers sn WHERE sn.Num < LEN(l.listString) AND SUBSTRING(l.listString, sn.Num, 1) = ',' )
GO
how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?
I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting.. How to access the values in row by row?
i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....
ORDER BY CASE WHEN @SortOrder = 'Sales' THEN Sum(SALES.Sale) WHEN @SortOrder = 'Profit' THEN Sum(GP) WHEN @SortOrder = 'Product' THEN SALES.Product END
Error:
Warning: Null value is eliminated by an aggregate or other SET operation. Server: Msg 8114, Level 16, State 5, Line 8 Error converting data type varchar to float.
Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
Which is more efficientWhere NonindexedColumn=x and IndexedColumn=yorWhere IndexedColumn=y and NonindexedColumn=xor does matter? Will the optimiser work it out?(I'm building the SQL string on the fly in the fron-end)
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null @Decision varchar(1) Null Select emplid,name, Semester Decision1=(select * from tbldecision where reader=1) Decision2=(select * from tbldecision where reader=2) Where Semester=@Sem And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:
CREATE PROCEDURE GetFromTable ( @SortOn varchar(20) ) AS SELECT * FROM Table ORDER BY @SortOn GO
This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?
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.
Hi all, I've been trying to rack my brains over this one; When I use this select statement in SqlDataAdapter, it runs fine; it takes about 4 to 5 seconds to run. In SqlDataSource, its very erratic; taking between 8 to 71 seconds to run. I've finally narrowed down the issue to something that resolves the symptoms to something in my query; SELECT f.UnitID, f.FacilityName, SUM(CASE WHEN t .sData_Type = 'CLINICAL' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Clinical, SUM(CASE WHEN t .sData_Type = 'OTHERS' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Others, SUM(t.nWrite_Off_Amt) AS Sum_Total, SUM(t.nCashAmount) AS Sum_Cash, t.iYearFROM t LEFT OUTER JOIN d ON t.sUnitID = d.sUnitID AND t.sPat_Num = d.sPat_Num AND t.iMonth = d.iMonth AND t.iYear = d.iYear LEFT OUTER JOIN tblFac AS f ON t.sUnitID = f.UnitID_1WHERE (t.iYear = @year) AND (t.iMonth = @month) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))GROUP BY f.UnitID, t.iYear, f.FacilityName, f.Division If I change my WHERE clause to the following WHERE (@year = t.iYear) AND (@month = t.iMonth) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5))) performance is MUCH better. How could such a little change make such a big difference in speed? Thanks. -e
I use a DataGrid to show the data, and I want it has a sorting and Paging function, so I use dataset to collect the data from middle tier function and stored procedure. I have code in aspx page like BindData(ViewState("SortExpr")).
In the stored procedure I pass SortExpr as parameter as following:
DECLARE @RowCount int SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0 BEGIN SELECT z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance /* The above functions requires the Distance Assistant. */ FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a WHERE z.Latitude <= r.MaxLat AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND z.CityType = 'D' AND z.ZIPCodeType <> 'M' AND z.ZIPCode = a.Zipcode AND a.AdActive = '1' AND a.AdExpiredate >= getdate() AND a.Make = @Make AND a.Model = IsNull(@Model,a.Model) AND a.Condition = IsNull(@Condition, a.Condition) AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles ORDER BY @SortExpr END ELSE SELECT -1 As ZIPCode --ZIP Code not found... GO
but I got the error as "variables are only allowed when ordering by an expression referenceing a column name". How I fix this error? Please help.
