I have a stored procedure that uses a dynamic order by statement. This statement works okay until I try to select ticket's by userEmail which is passed in to my stored procedure as a parameter. Here is the code that works for my dynamic sort order:
LEFT OUTER JOIN Status f ON f.statusID = a.statusID
ORDER BY ' + @SortOrder)
I modied this procedure to create one in which I select tickets based on the userEmail as a criteria as well.. this one fails due to Incorrect syntax near the keyword 'BY'
AS
SET NOCOUNT ON
Exec('SELECT a.TicketID, a.TicketDate, a.TicketName, a.TicketDescription, a.statusID, a.resolutionID, a.userID,
b.typeID, b.typeName,
c.userID, c.UserFirstName, c.userEmail,
f.statusID, f.statusName
FROM Tickets a
LEFT OUTER JOIN Type b ON b.typeID = a.typeID
LEFT OUTER JOIN Users c ON c.userID = a.userID
LEFT OUTER JOIN Status f ON f.statusID = a.statusID
WHERE a.statusID <> 40 AND c.userEmail = ' + @userEmail +
'ORDER BY ' + @SortOrder)
Any ideas on what syntax I should be using? Thanks!
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user.
declare @OrderBy as varchar(20) if(@DateType = 1) set @OrderBy = (select 'lastchange') --lastchange is a column name select * from xtableorder by [@OrderBy] ascI am getting an error saying that column name @OrderBy does not exist.How can I sort the search using the @OrderBy variable?Thanx
I am using a dynamic order by statement;ORDER BY CASE @sortWHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK,0 ) AS CHAR( 5 ) )WHEN 1 THEN C.titleWHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance]( @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) ASCHAR( 9 ) )WHEN 3 THEN ( C.locality + ' ' + C.state )WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASCThe problem is with the numeric values, I have to cast them as astring, but in the results 114kmobviously is not between 1137km and 1144km.Anyone any ideas on this?Thanks in advance.
I have a stored procedure with dynamic ORDER BY. I would like to use the DISTINCT too. Is it somehow possible? Thank you
Here is the stored procedure:
SELECT identifier_company + cast(identifier_number as nvarchar(3)) as identifier, CASE WHEN canceled = 'True' THEN 'canceledPO' ELSE '' END AS style, staff.staff_name, purchase.purchase_id, purchase.traveller_name, nominal_department.department_name, purchase.canceled, purchase.travel_date, convert(nvarchar(20), purchase.date_raised, 103) as dated, supplier FROM purchase INNER JOIN purchase_project ON purchase.purchase_id = purchase_project.purchase_id INNER JOIN staff ON purchase.raised = staff.staff_id INNER JOIN nominal_department ON purchase.department = nominal_department.nominal_dep_id WHERE (raised in (SELECT staff_id FROM staff WHERE department like @FromDepartment) or purchase.raised = @raisedBy) and purchase_project.project_number like '%' + @Query + '%' ORDER BY CASE @SortDir WHEN 'ASC' THEN CASE @OrderBy WHEN 'staff_name' THEN cast(staff_name as nvarchar(100)) WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100)) WHEN 'department_name' THEN cast(department_name as nvarchar(100)) WHEN 'supplier' THEN cast(supplier as nvarchar(100)) WHEN 'canceled' THEN cast(canceled as nvarchar(10)) END END ASC, CASE @SortDir WHEN 'DESC' THEN CASE @OrderBy WHEN 'staff_name' THEN cast(staff_name as nvarchar(100)) WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100)) WHEN 'department_name' THEN cast(department_name as nvarchar(100)) WHEN 'supplier' THEN cast(supplier as nvarchar(100)) WHEN 'canceled' THEN cast(canceled as nvarchar(10)) END END DESC
I am trying to use dynamic ORDER BY with UNION in a stored procedure but I keep getting this error message:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The ORDER BY works fine without the UNION, and the UNION works fine without the dynamic ORDER BY (just putting an ORDER BY works fine)
Here is the whole query in case this helps, thank you
SELECT leave_id, leave_type, annualLeave.from_date, convert(nvarchar(10), annualLeave.request_date, 103) as dated, convert(nvarchar(10), annualLeave.from_date, 103) as from_date, convert(nvarchar(10), annualLeave.to_date, 103) as to_date, annualLeave.canceled, annualLeave.working_days, staff.staff_name, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' ELSE '' END AS pendingStyle, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' ELSE '' END AS historyStyle FROM annualLeave INNER JOIN staff ON annualLeave.staff = staff.staff_id WHERE staff.department like @Department and (@staffID is Null or annualLeave.staff = @StaffID) and (@Canceled is Null or annualLeave.canceled = @Canceled) and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null) and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null) and (@StartingDate is Null or annualLeave.from_date > @Startingdate) and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave) and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)
UNION
SELECT leave_id, leave_type, annualLeave.from_date, convert(nvarchar(10), annualLeave.request_date, 103) as dated, convert(nvarchar(10), annualLeave.from_date, 103) as from_date, convert(nvarchar(10), annualLeave.to_date, 103) as to_date, annualLeave.canceled, annualLeave.working_days, staff.staff_name, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' ELSE '' END AS pendingStyle, Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO' ELSE '' END AS historyStyle FROM annualLeave INNER JOIN staff ON annualLeave.staff = staff.staff_id WHERE staff.department like @Department and (@staffID is Null or annualLeave.staff = @StaffID) and (@Canceled is Null or annualLeave.canceled = @Canceled) and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null) and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null) and annualLeave.leave_type like 'Leave in lieu' ORDER BY CASE @OrderDir WHEN 'ASC' THENannualLeave.from_date END ASC, CASE @OrderDir WHEN 'DESC' THEN annualLeave.from_date END DESC
I have a need to perform pagination while using dynamic sorting. Asan exmaple -SELECT TOP(10) * FROM (SELECTTextColumn,DecimalColumn,ROW_NUMER() OVER (ORDER BYCASE @xWHEN 1 THEN TextColumnWHEN 2 THEN DecimalColumnENDDESC) AS SortOrderFROM Table1) AS Results WHERE SortOrder ( 10 ) ORDER BY SortOrderThis is obviously just some sample but an error is given because thedata type of the 2 columns used in the order by are different. Itworks if I cast DecimalColumn to match the textcolumn but then thesorting is wrong. Is there a way to do this in a single query with 2different data types?Thanks for your help.
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.
In the query I want to set the Order By clause based on the user selection. eg.:
select * from dbo.customers where name = @CustomerName order by @SortBy
However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work
="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy
select * from dbo.customers where name = @CustomerName order by + @SortBy
I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.
I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.
CREATE PROCEDURE procedure_name @regNum varchar(14), @sortOrder tinyint = 1 AS SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register', Obs_Date As 'Observation Date' FROM tblSPG_Header WHERE REG = @regNum UNION SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register', Obs_Date As 'Observation Date' FROM tblRCH_Header WHERE REG = @regNum ORDER BY Obs_Date DESC GO
Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:
ORDER BY CASE WHEN @sortOrder = 1 THEN Obs_Date END DESC
but I frequently get the following error when I try to do so:
"ORDER BY items must appear in the select list if the statements contain a UNION operator"
If anyone can offer any suggestions, I would appreciate it. Thanks.
I can't figure out why this won't work. I want to use a variable for the 'ORDER BY' in my Stored Procedure.
I use this to order my results by product price or alphabetically from a dropdown menu. I have tried the following, but get the error below.
Code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[SPResults]
[Code] ....
Code: Msg 1008, Level 16, State 1, Procedure SPResults, Line 21
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Is this possible to do?
I have this stored procedure: SELECT * from purchase ORDER BY CASE @OrderBy WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100)) WHEN 'canceled' THEN cast(canceled as bit) END
the @OrderBy is a nvarchar(100) parameter traveller_name is an nvarchar(100) field canceled is a bit field
When I execute the stored procedure it works fine until I execute it sorting by canceled field. After that, I cannot sort it again using the traveller_name field. I get this eror: Conversion failed when converting the nvarchar value 'Jason' to data type bit. (Jason is a record in the traveller_name) Removing the castings and sorting by just the column name does not help.
Hi, Can anyone help as to how to get this to work? dbo.parseInt is a Function which sorts Alpha Numerically. If I removed the top part of the UNION, the Procedure works fine.
ALTER PROCEDURE dbo.ByJobNoAlphaNumeric as SELECT 0 AS JobID, '<All Jobs>' AS JobNo UNION SELECT JobID, JobNo FROM tbl ORDER BY dbo.parseInt(JobNo)
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 :)
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.
Hi all,I have a SQL statement that allows paging and dynamic sorting of thecolumns, but what I can't figure out without making the SQL a dynamicstring and executing it, or duplicating the SQL statement between anIF and ELSE statement.Following is the SQL statement;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_search]@search VARCHAR( 80 ), @startRow INT = 1, @endRow INT = NULL, @postcode AS CHAR( 4 ) = NULL, @suburb AS VARCHAR( 40 ) = NULL, @stateIdentity AS TINYINT = NULL, @fromLatitude AS REAL = NULL -- latitude the user is located in, @fromLongitude AS REAL = NULL -- longitude the user is located in, @sort TINYINT = 1ASBEGINSET NOCOUNT ON;DECLARE @calculateDistance BIT;SET @calculateDistance = 0;-- get the longitude and latitude if requiredIF ( NOT @postcode IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_postalcodeWHERE(postalcode = @postcode)SET @calculateDistance = 1ENDELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_localityWHERE(locality = @suburb)AND(stateIdentity = @stateIdentity)SET @calculateDistance = 1END/*ELSE IF ( @fromLatitude IS NULL AND @fromLongitude IS NULL )BEGINRAISERROR( 'You need to pass a valid combination to this storedprocedure, example: postcode or suburb and state identity or longitudeand latitude', 18, 1 );END*/SELECT D1.[row], D1.[totalRecordCount], D1.[classifiedIdentity], D1.[title], D1.[summary], D1.[price], D1.[locality], D1.[state], D1.[postcode], D1.[addedLast24], D1.[dateStamp], D1.[t2Rank], D1.[t3Rank], D1.[tRank], D1.[distance], F.[originalName], F.[extension], F.[uniqueName]FROM(-- derived tableSELECT ROW_NUMBER() OVER ( ORDER BY CASE @sort WHEN 0 THENCAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row, COUNT( * ) OVER() AS totalRecordCount, C.[classifiedIdentity], C.[title], C.[summary], C.[price], C.[locality], C.[state], C.[postcode], CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )THEN 1 ELSE 0 END AS addedLast24, C.[dateStamp]/* , t1.RANK AS t1Rank */, t2.RANK AS t2Rank, t3.RANK AS t3Rank, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS tRank, CASE @calculateDistance WHEN 1 THEN CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) ELSE 0 END AS distanceFROM [tbl_classified] AS CINNER JOINtbl_locality LONC.localityIdentity = L.localityIdentity/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @keyword ) ASt1ON FT_TBL.categoryIdentity = t1.[KEY] */LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @search ) ASt2ON C.classifiedIdentity = t2.[KEY]LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,@search ) AS t3ON C.classifiedIdentity = t3.[KEY]WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) ) != 0) AS D1LEFT OUTER JOINtbl_classified_file CFOND1.classifiedIdentity = CF.classifiedIdentityLEFT OUTER JOINtbl_file FONF.fileIdentity = CF.fileIdentityWHERE( row >= @startRow )AND( @endRow IS NULL OR row <= @endRow )ENDThe part I'm having trouble with is making the sort order in thefollowing line dynamicORDER BY CASE @sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THENCAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude,@fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price ASCHAR( 10 ) ) END ASCany help would be greatly apprecaited.Thanks
I am looking for assistance coming up with a function (or maybe not a function if there is a better way) to make the Status column in my order table dynamic. The default value for new records will always be "1" to designate a quote. At this point the field is not dynamic. Once the customer confirms the order, the user needs to execute a command to change the status to "3" to designate a Confirmed order. At this point the field needs to be dynamic based on the shipping records. There are two order details tables. One for sales items and one for rental items. Each of these details tables has their own shipping record. the CheckInOut Tables are for rental while the Ship tables are for sales. So, if some (but not all) of the items in either of these order details tables has a shipping record associated with it, then the status should be changed to "5". If everything has been shipping, the status is changed to "4". If everything has been shipping but some items have been returned, the status is "6" if everything has been shipping and all of the RentalDetail items have been returned then the status is "7" and if there is any other combination of a variety of ships and returns, the status is "8". Also, at any time, the user needs to be able to execute a command to change the value to "2". once the value is changed to "2" the field stops being dynamic again.
Below are my tables creation commands.
CREATE TABLE OrderHeader ( OrderID int identity primary key, Status int, StartDate datetime, EndDate datetime )--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed" CREATE TABLE OrderRentalDetail ( OrderRentalDetailID int identity primary key, OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID), ItemName varchar(30), Qty int, SiteID int, ) CREATE TABLE CheckInOutHeader ( CheckInOutID int identity primary key, Type int, SiteID int, ActionDate datetime )--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost" CREATE TABLE CheckInOutDetail ( CheckInOutDetailID int identity primary key, CheckInOutID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID), OrderRentalDetailID int, Qty int ) CREATE TABLE OrderSalesDetail ( OrderSalesDetailID int identity primary key, OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID), ItemName varchar(30), Qty int, SiteID int, ) CREATE TABLE ShipHeader ( ShippingID int identity primary key, Type int, SiteID int, ActionDate datetime )--Use Type 1 = "Ship" Type 2 = "Return" CREATE TABLE ShipDetail ( ShipDetailID int identity primary key, ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID), OrderSalesDetailID int, Qty int )
I have a report that i want to beable to do some dynamic paramaters on it. I have a start and end date of when they want to run the report but i also have a mulivalue list box for users filtering for users. I want to add a supervisor filter on form also. But I have a problem.
I want to allow the users to select a supervisor and filter for all employees under that, or allow them to select a set of users and allow them to filter for that also. Also i would like when they select no supervisor or user it just filters for the dates and picks all up all the users.
I thought i could do something like in access like this Like UserName & "*" but it doesnt work.
Here is my dataset code i am using currently: SELECT U.Name, A.row_date, A.split, A.SumOfti_stafftime, A.AHT, A.AvgACW, A.AvgACD, A.AvailTime, A.SplitSkill_Incalls, A.SplitSkill_Outcalls, A.SumOfacdtime, A.SumOfti_othertime, A.SumOfacwtime, A.SumOfti_auxtime0, A.SumOfti_auxtime1, A.SumOfti_auxtime2, A.SumOfti_auxtime3, A.SumOfti_auxtime4, A.SumOfti_auxtime5, A.SumOfti_auxtime6, A.SumOfti_auxtime7, A.SumOfti_auxtime8, A.SumOfti_auxtime9, U.Sup FROM tblAvayaDaily AS A RIGHT OUTER JOIN tblUsers AS U ON A.logid = U.[Avaya ID] WHERE (A.split = 1651) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR (A.split = 1655) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR (A.split = 1653) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd)
I am currently working on a project that involves creating dynamic insert and update statements for dynamically created tables. The tables I am creating the statements for could have up to 1000 columns.
Obviously building the entire insert/update SQL string dynamically in VB.Net and sending it to the Sql server leaves the system vulnerable to injection attack or error caused by single quotes in strings.
I am using the Patterns and Practices Data Application block for my data access.
What I am considering is creating the dynamic sql as a statement with the columns names and values in as parameters (@XXXXX) and then looping through my list of columns and values and adding them as by AddInParameters on the DbCommand.
- Can it handle such large amount of parameters at all? - What kind of structure does ADO.net send to the SQL server? i.e. Will using so many parameters mean that the data communicated to the SQL server from the webserver by the parameter method would be much larger than the substituted dynamic sql string? - How does ADO.NET/SQL Server 2005 perform with such large amounts of parameters on a DBCommand?
Any advice as to whether this is a viable way to proceed would be much appreciated.
Oh yeah... I am using VB.NET in VIsual Studio 2005 with a SQL Server 2005 DB.
I have 13 parameters, ordered correctly within the Report Parameter screen.
When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly. The remainder are ordered correctly.
I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.
Please can someone suggest how I can get the parameters to appear in the correct order within the application?
* We have a DAL that generates all SQL dynamically out of a nobject model. Standard very powerfull O/R mapper. * In the DAL, for CRUD operations, we generate the statements dynamically. As an example, let's take INSERT. * The insert is generated ONCE, with parameters, and cached. For every reuse, the parameters are replaced (in value), and the whole thing commited.
I see hte following negative: I can not easily batch multiple inserts. Parameters have to be unique per batch. So, if I want to batch two inserts, I need two sets of parameters.
Alternative:
Instead of generating the SQL with parameters, we generate the SQL as a string ready to be inserted for / with a String.Format, and then I encode the parameters and make one SQL String out of this. Now, please - don't say "sql injection", we are not that stupid, the layer handles this already, properly encoding all dangerous values.
With this approach, the SQL statement would be a string and not use any parameter. As a result, I could batch them up as much as I want (ok, up to a certain string size). I need to keep parameters around anyway (for blobs etc.), but most objects do not have blobs, and the SQL is prettty small. This small SQL could be batched significantly (100 statements per batch, propably mode) and be submitted to the database. As a result, the round trips to the databae would go down.
Now, my question is - which of the two approaches is more advisable, from a performance point of view? Again, stuff like SQL injection and ease of handling are totally irrelevant - the SQL never leaves the DAL and is generated in there, and we will go through a lot of complexitiy for higher performance.
Normally I would say batching should be better. SQL Server can auto-parameterize the statements (reusing the query plan), and / but the network round trips are the larger issue here.
I'm trying to write a formula (actually, four) which will be used in Panorama NovaView to allow a user to see Revenue (on rows) by Previous Month (Current Year), Previous Month (Previous Year), Current YTD (ending at month in question), Previous YTD (ending at month in question) (on columns -- not necessarily in that order).
Basically, when run anytime during November 2005, the output would look like:
2004 Total | Oct 2004 | 2005 Total | Oct 2005 $ 100,000 | 15,000 | 120,000 | 17,500
The Dimension I'm working with is [Calendar Year], with levels: (All), [Calendar Year], [Calendar Month], [Calendar Week].
I've been focusing on the formula for the first column, since it is the most complex (I think). Here's the jist of what I am trying to do:
SELECT Measures.Revenue ON ROWS, <<Last Year>>.January : <<Last Year>>.<<LastMonth>> ON COLUMNS FROM SalesCube
I believe some combination of PeriodsToDate(), LastPeriod() and/or <<Current Year>>.PrevMember & <<Current Month>>.PrevMember is what I need, but I just can't get the syntax right.
I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together)
1 2 3 4 / x x x x / x x x x / x where x = not supplied / = supplied a value
(and so the list continues) Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements.
I have a winform application, where in we get the parameters from the reporting service and we display them in a panel for user's to input their criteria. So far everything worked good, but now we have a requirement wherein dynamic functionality is needed.
For Example:
Based on a selection of a combobox value, we want the other control to be enabled or disabled. Also we want default value of a control to be calculated based on another control's value like .. if user enters value "1" in textbox1 then texbox 2 should have default value of "6". (Textbox1 + 5).........and mix and match of such capabilities.
The Question is........IS IT POSSIBLE??? and if yes...HOW!!!
Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select €œselect all€? option from the parameter list all 20 parameters value should be false.
The dynamic SQL statements with output parameters, unfortunately, in the documentation are not described. À) Simple example of the dynamic SQL statement with output parameters
-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'. declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000) -- the third parameter passed in the dynamic statement as by output, returns the length of -- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle declare @nHypotenuse float select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))', @ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out' -- we call the dynamic statement in such a way exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse out -- or in such a way exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out select @nHypotenuse -- Displays 5.0
B) Example of usage of the dynamic statement with output parameter and the function GETALLWORDS. The following stored procedure get all words from a field of the type text or ntext, the word length should not exceed 4000 characters.
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT @TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname, @cDelimiters nvarchar(256) = NULL AS -- this Stored procedure inserts the words from a text field into the table. -- WORDNUM int – Sequence number of a word -- WORD nvarchar(4000) – the word -- STARTOFWORD int – position in the text field, with which the word starts -- LENGTHOFWORD smallint – length of the word -- Parameters -- @TableName name of the table with the text or ntext field -- @FieldIdName name of Id field -- @FieldIdValue value of Id field -- @FieldTextName name of field text or ntext -- @cDelimiters Specifies one or more optional characters used to separate words in the text field begin set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit, @RetTable bit, @cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000), @ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))), @FieldIdName = lower(ltrim(rtrim(@FieldIdName))), @FieldTextName = lower(ltrim(rtrim(@FieldTextName))), @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words. @nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- If the temporary table is not created in the calling procedure, we create the temporary table if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null begin create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint) select @RetTable = 1 end -- we use the dynamic SQL statement to receive the exact name of text field -- as we can write names of fields by a call of the given stored procedure in the arbitrary register -- in the string of parameters definition @ParmDefinition we use a keyword output -- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output -- Also we use a keyword output for definite before parameter select @SQLString = 'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+'''' select @ParmDefinition = '@FieldTextName sysname output' exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- we use the dynamic SQL statement to receive the exact name of Id field select @SQLString = 'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+'''' select @ParmDefinition = '@FieldIdName sysname output' exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
-- we use the dynamic SQL statement to receive the type of field (text or ntext) select @SQLString = 'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')' select @ParmDefinition = '@TypeField varchar(13) output' exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
-- we use the dynamic SQL statement to receive a length of the text field select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50)) select @ParmDefinition = '@nEndString int output' exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle while 1 > 0 begin -- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field select @SQLString = 'select @cString = substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' + cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50)) select @ParmDefinition = '@cString nvarchar(4000) output' exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip the character not in word, if any select @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip the character in word, if any select @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString begin select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc select @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1) -- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000) -- we add outcome result in the temporary table insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1, @nEndSubString = @nEndSubString + 4000 end else select @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
if @flag = 1 break if @nEndString <= @nEndSubString select @flag = 1, @nEndSubString = @nEndString end
-- If in a calling procedure the table was not created, we show the result if @RetTable = 1 select * from #GETALLWORDSFROMTEXT
end GO
Example of the call Stored procedure SP_GETALLWORDSFROMTEXT
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null drop table #GETALLWORDSFROMTEXT create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint) exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name', 'Your Id field name', Value of Id field, ' text or ntext field name', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null select * from #GETALLWORDSFROMTEXT
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table. -- GETALLWORDS(@cString[, @cDelimiters]) -- Parameters -- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS. -- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString. -- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter. -- Return Value table -- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character. -- Example -- declare @cString nvarchar(4000) -- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.' -- select * from dbo.GETALLWORDS(@cString, default) -- select * from dbo.GETALLWORDS(@cString, ' ,.') -- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions CREATE function GETALLWORDS (@cString nvarchar(4000), @cDelimiters nvarchar(256)) returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint) begin declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 1, @BegOfWord = 1, @flag = 0, @cString = isnull(@cString, ''), @cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words. @nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while 1 > 0 begin if @k - @BegOfWord > 0 begin insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word select @wordcount = @wordcount + 1, @BegOfWord = @k end if @flag = 1 break
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndString > @k -- skip break characters, if any select @k = @k + 1, @BegOfWord = @BegOfWord + 1 while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndString > @k -- skip the character in the word select @k = @k + 1 if @k >= @nEndString select @flag = 1 end return end
For more information about string UDFs Transact-SQL please visit the
I Hope someone can help me out with this problem.. its pretty NB.
I have a pair of multivalued parameters. So, parent and child... the child needs to get its selection based on the selection made by the user on the parent parameter ( the child parameter is hidden).
Fictional values: Parent Parameter: SelectAll, ALL , 1 , 2 Child Parameter: Home, Work, Play
If user selects "ALL" from the parent parameter I need Home and Play selected.
My idea was this: =iif(Parameters!Parent.Value(1) = "All","Home,Play") <-- in Available values.
However, the dataset that reads this value does not like the coma delimited string. This dataset is a cube created dataset, reading the child parameter.
Any help is greatly appreciated. Kind Regards, Neil