I'm using SQL Server 2005, sp 2. My query is below. What I want to see for the results is the average of all of partition 1, the average of partition 2, etc. Does anybody know how I can get this?
SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],
(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code
FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD
ON Shop.Date_Code=DD.Date_Code
INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON
Shop.Location_Code = Loc.Location_Code
WHERE District_Code = (@District)
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library. I get this error when i used into storeprocedure : ROW_NUMBER() function is not recognized in store procedure. i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library. I need to add that function into MS SQL SERVER 2005 database library. Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
Hi, This is my view in Sql2005 SELECT DISTINCT VLAN8, 'CONV' AS VLEDUS, MAX(CAST(VLEDBT AS int)) AS MaxVLEDBT FROM JDE_DEVELOPMENT.TESTDTA.F06116Z1 GROUP BY VLAN8, VLEDUS
2111CONV2111 4803CONV31 7550CONV33
When I add row number function the results become wrong:
SELECT DISTINCT VLAN8, 'CONV' AS VLEDUS, ROW_NUMBER() OVER (ORDER BY VLAN8) AS VLEDLN, MAX(CAST(VLEDBT AS int)) AS MaxVLEDBT FROM JDE_DEVELOPMENT.TESTDTA.F06116Z1 GROUP BY VLAN8, VLEDUS, VLEDLN
I'm trying to create this stored Procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[iumm_PagerData] @PagerIndex INT, @NumRows INT, @PicsCount INT OUTPUT
AS BEGIN SELECT @PicsCount=(SELECT COUNT(*) FROM pics) Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @NumRows) + 1;
With PicEntries as ( SELECT ROW_NUMBER() OVER (ORDER BY picID DESC) AS Row, picID, userID, picFileName, votes, date FROM pics) -- Insert statements for procedure here SELECT picID, userID, picFileName FROM pics WHERE Row between @startRowIndex and @StartRowIndex+@NumRows-1 END GO --- However, I'm always getting this error: Msg 207, Level 16, State 1, Procedure iumm_PagerData, Line 22 Invalid column name 'Row'. Msg 207, Level 16, State 1, Procedure iumm_PagerData, Line 22 Invalid column name 'Row'.
Anyone knows what could be the reason for that? Thanks.
I'm not sure if this is possible and have been having trouble figuring out the code to do this. I am assigning row_number to a gridview. I then want to filter the results with a dropdown. I am able to get the filter to filter the status but it either renumbers the gridview or it leaves the row numbers blank. Is there a way to have the row_numbers stick to the gridview when I filter? Example below. Thanks Normal:IssueNumber(row_number), Status1, Open2, Open3, Closed4, Open5, Closed "Open" Filter:IssueNumber(row_number), Status1, Open2, Open4, Open "Closed" Filter:IssueNumber(row_number), Status3, Closed5, Closed
My application runs this query using a stored proc
SELECT empid1,name1,joindate from emp where empid2=3 union select empid2,name2,joindate from emp where id1=3
Now I want to implement paging for the same using Row_Number so that I can display the results in pages. Can someone please help me write a query for the same. I tried playing with Row_Number but no luck with it.Basically I am not good with SQL and I had programatically implemented paging in asp.net by looping through all records returned by the query.
Hi, I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:<Error messages> These are on the first SELECT Line..Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.CODE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "mort.NAME.CODE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAME.LAST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAME.FIRST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAMETYPE.TYPE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "FUNERAL.NUMBER" could not be bound. </Error Messages><Sotred Procedure> CREATE PROCEDURE proc_NAMEGetPaged @startRowIndex int, @maximumRows intASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER FROM (SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER, ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum FROM Name n) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1ENDGO </Stored Procedure> Any assistance in resolving this would be greatly appreciated.. Regards..Peter.
For some reason this query, when I include this line, does not work. But I comment it out and it works fine - do you see anything wrong that I can't see??
(Comment this out and it works) AND RowNumber >= @RowStart AND RowNumber <= @RowEnd
Query:
ALTER PROCEDURE [dbo].[GetNewsItemAbstracts] -- Add the parameters for the stored procedure here @CategoryID int, @NewsType char(25), -- paging @PageSize int, @PageNumber int
AS
Declare @RowStart int Declare @RowEnd int
if @PageNumber > 0 Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1; SET @RowEnd = @RowStart + @PageSize - 1 ;
SELECT RowNumber,[id],[headline],[publishedDate] FROM (SELECT [id],[headline],[publishedDate],[NewsCategoryID], [NewsType], ROW_NUMBER() OVER(ORDER BY [publishedDate]) as RowNumber FROM NewsItem ni ) as PagedNewsItems
WHERE [NewsCategoryID] = @CategoryID AND [NewsType] = @NewsType AND RowNumber >= @RowStart -AND RowNumber <= @RowEnd END
This is gonna be a quite long explanation of an understanding problem and I seriously hope that anyone can help me out here. Please look at the following example: use tempdb; create table T1 ( C1 varchar(80) not null default 'Empty' ,C2 uniqueidentifier not null default newid() ) go -- Add some rows set nocount on insert T1(C1) values('A') go 5 insert T1(C1) values('X') go 7 insert T1(C1) values('Y') go 9 insert T1(C1) values('Y') go 6 Now run the following two queries and include the actual execution plan: select distinct top 10 row_number() over(order by C1), C1 from T1
select top 10 row_number() over(order by C1), C1 from T1 You€˜ll get two different plans with the first one slightly more expansive than the second. What I do not understand is, why the optimizer does not ignore the DISTINCT in the first case. When I include a ROW_NUMBER() without partitioning (or, to e exact: with only one partition) as in the above query, every row will get a unique number, so all returned rows are already distinct, aren€™t they? But as this optimization possibility is so obvious and simple, I don€™t believe that the optimizer is wrong €“ rather I suppose that I€™m the on, who does not understand what€™s going on here. If you play around with some SELECTs, the difference between DISTINCT included and excluded can be very noticable. Take the following example: select distinct top 10 row_number() over(order by a.C1), a.C1, b.C1 from T1 as a inner join T1 as b on b.C2 = a.C2
select top 10 row_number() over(order by a.C1), a.C1, b.C1 from T1 as a inner join T1 as b on b.C2 = a.C2 where the (unnecessary?) DISTINCT in the first query holds responsible for 34% oft he total query costs, making the cost for the first query over twice as much as for the second. I€™ve tried to find at least one example where DISTINCT makes sense €“ but without success. In all my experiments (included the above, of course), always the same resultset is returned, regardless of DISTINCT or not. The problem has been detected using an OR-mapper (nHibernate), where the SQL code is automatically generated. Inside the code generation process, a ROW_NUMBER() columnn without partitioning is always added, as well as in many cases also DISTINCT. I€™d simply like to remove the DISTINCT keyword from the code generation, because it increases the performance dramatically in many cases. But fort he reasons mentioned above, I€™m not sure whether I can do this without risk. Any ideas are greatly appreciated.
I am running a row_number fucntion but i do not have the correct sequence when i am including isnull expression, but maybe the problem is when i am trying to join the tables my script is something like this:
SELECT TOP (100) PERCENT row_number() over( partition by a.Blasthole_Name order by a.DEPTH) as seq,
a.Blasthole_Name, a.ACTUAL_NAME, b.loaded_diameter, isnull(( select max(Z.DEPTH) from dbo.Drillability Z
where Z.Blasthole_Name = a.Blasthole_Name and Z.DEPTH < a.DEPTH), 0) as [from], a.DEPTH,
FROM dbo.Drillability a FULL OUTER JOIN
PD_Data.dbo.db_drill_hole_base b ON a.Blasthole_Name = b.drill_hole_id
GROUP BY a.Blasthole_Name, a.DEPTH, b.loaded_diameter
Hi, I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement. I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'." The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int) AS BEGIN WITH SearchResults AS( UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price ) SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize END
WITH OrderedResults AS (SELECT some_table.*, ROW_NUMBER() OVER (ORDER BY some_field) as RowNumber FROM some_table) SELECT TOP 10 * FROM OrderedResults WHERE RowNumber > 10;
which works well for returning "paginated" recordsets. But when it comes to displaying "page" links and next and previous links, I need a total count of records found... something along the lines of the MySQL CALC_FOUND_ROWS feature...
Is there some built-in MSSQL feature I can use for this, or do I have to do a SELECT count(*) FROM some_table to get this data?
I would like to use an equivalent of ROW_NUMBER() T-SQL function in a SSIS package.
I could create a temporary table and use a SQL request with ROW_NUMBER() function but I would like to avoid that and generate this column in the dataflow.
I am wondering if somone could provide me with a sample SELECT for an auto number query. For example I have a table called People with two columns first_name, and last_name. There isn't a unique id to correspond with the table but would like to dynamically make one during the return of the query.
I know this is possible in the full version of sql 2005 using
SELECT ROW_NUMBER() OVER (ORDER BY last_name), first_name, last_name from People
But within sql mobile is there a way? So if there was 5 rows in the table it would return
1 John Alpha 2 John Beta 3 John Cat 4 John Delta 5 John Echo
I am trying to limit a result set by ROW_NUMBER. However, I am having problems getting it working.
The following query works fine, and I get a result set with PollID, AddedDate and RowNum columns.
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM
( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) AS Results
However, as soon as I add a WHERE condition:
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM
( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) AS Results
WHERE RowNum BETWEEN 1 AND 10
The query fails with an ' Invalid column name 'RowNum' ' error.
I have tried using 'Results.RowNum' but I get the same problem.
I don't understand what the issue is. The result set has a column headed 'RowNum' so why can't I apply a WHERE clause to this column? I can apply WHERE to the PollID column, for example, with no problem.
The row_number functions doesn't seem to be operable in our version of SQL Server 2005 SP2.
We have completely tried everything, including using the sample database, and Query posted in the 2005 SQL Server 2005 Book, which is as follows:
SELECT empid, qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownum
FROM dbo.Sales
ORDER BY qty;
I get the following error:
Msg 195, Level 15, State 10, Line 2
'ROW_NUMBER' is not a recognized function name.
Below you will find the versions of SQL we are using of 2005.
Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.11 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
This example is working: Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'. I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
I want to select all usernames from tlbUsers which practice a certain sport.I also have a table UsersAndSports:UserID intSportID intI therefore need an inner join statement:select username from(SELECT ROW_NUMBER() OVER (ORDER BY au.LastActivityDate DESC) as RowNum,ud.username from tblusers usinner join tblUsersAndSports uas on uas.usercode=us.usercode and (uas.sportID=3 or uas.sportID=4)WHERE us.UserName <>'' )as MemberInfo WHERE RowNum between (@startRowIndex+1) AND (@startRowIndex+@maximumRows)The problem lies in the usage of the ROW_NUMBER command. I need that in order to effectively page through my records, but it also makes thatI cant make a distinct selection as each RowNum is unique....:SIn case the user would practice 2 sports, the query would return 2 rows...if I place a distinct in front of the username as such:select distinct username fromThe query would return each user only once...BUTBUTBUT: my startrowindex and maximumrows apply to the results that can be found in the MemberInfo selection..so lets say my startrowindex would be 0 and maximumrow 5if my 1st query (without distinct) was to return:johnjohnjohnjohnmikemikerobmy 2nd query (with distinct) would return:johnmikewhereas I want it to return:johnmikerobWhat can I do?
Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change. CREATE PROCEDURE GetProductsOnCatalogPromotion(@DescriptionLength INT,@PageNumber INT,@ProductsPerPage INT,@HowManyProducts INT OUTPUT)AS-- declare a new TABLE variableDECLARE @Products TABLE(RowNumber INT,ProductID INT,Name VARCHAR(50),Description VARCHAR(5000),Price MONEY,Image1FileName VARCHAR(50),Image2FileName VARCHAR(50),OnDepartmentPromotion bit,OnCatalogPromotion bit)-- populate the table variable with the complete list of productsINSERT INTO @ProductsSELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),ProductID, Name,SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotionFROM ProductWHERE OnCatalogPromotion = 1-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(ProductID) FROM @Products-- extract the requested page of productsSELECT ProductID, Name, Description, Price, Image1FileName,Image2FileName, OnDepartmentPromotion, OnCatalogPromotionFROM @ProductsWHERE RowNumber > (@PageNumber - 1) * @ProductsPerPageAND RowNumber <= @PageNumber * @ProductsPerPage
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as ( select 'row' = row_number() over (order by insertdate desc), myValue from ( select table1Id As myValue from myTable1 union select table2Id As myValue from myTable2 ) )
select * from myTableWithRowNum Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.
Code Snippet INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9 SELECT Episode_Key, SecDX1 = [1], SecDX2 = [2], SecDX3 = [3], SecDX4 = [4], SecDX5 = [5], SecDX6 = [6], SecDX7 = [7], SecDX8 = [8], SecDX9 = [9], SecDX10 = [10], SecDX11 = [11], SecDX12 = [12], SecDX13 = [13], SecDX14 = [14], SecDX15 = [15] FROM (SELECT Episode_Key, DX_Key, ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code FROM srm.cdmab_dx_other WHERE Episode_key is not null ) data PIVOT ( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt ORDER BY Episode_Key
The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?
Code Snippet SELECT e.episode_key, e.medrec_no, e.account_number, Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'') + Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'') + Isnull(ltrim(rtrim(p.patient_mname)) + ' ','') + Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName, CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date, CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date, e.episode_type as VisitTypeCode, d.VisitTypeName, convert(int, pm.PatientAge) as PatientAge, pm.PatientAgeGroup, pm.patientsex, p.race FROM srm.episodes e inner join srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join srm.item_header ih on ih.item_key = e.episode_key inner join srm.patients p on p.patient_key = ih.logical_parent_key inner join ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join dbo.PtMstr pm on pm.AccountNumber = e.Account_Number
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS ( Select col1 ,col2 ,col3 ,row_number() over (order by case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc when @COLTOSORTBY='col2' then col2 asc else col3 asc end else
case when @COLTOSORTBY='col1' then col1 desc when @COLTOSORTBY='col2' then col2 desc else col3 desc end end from myTable )
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
I have the following stored procedure below, it gives the following error when I am trying to run it: Must declare the scalar variable "@row_number"
. Other stored procedures I have run fine.
STORED PROCEDURE set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [sp_find_patients] @AccessCode varchar(500), @patientHospNo varchar(255), @patientFirstName varchar(255), @patientLastName varchar(255), @irow_number int As declare @sql varchar(8000) declare @row_number int SET @row_number = @irow_number
set @AccessCode = "'" + replace(@AccessCode,'-',"','") + "'"
SELECT @sql = 'SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY category.category,sub_category.sub_category, patientFirstName,patientLastName) AS RowNumber, * FROM patient INNER JOIN sub_category ON patient.sub_categoryID = sub_category.sub_categoryID INNER JOIN category ON sub_category.categoryID = category.categoryID) _myResults WHERE patient.sub_categoryID in (' + @AccessCode + ') ' IF @patientHospNo <> '' SELECT @sql = @sql + 'AND patientHospNo = '''+@patientHospNo+''' ' IF @patientFirstName <> '' SELECT @sql = @sql + 'AND patientFirstName = '''+@patientFirstName+''' ' IF @patientLastName <> '' SELECT @sql = @sql + 'AND patientLastName = '''+@patientLastName+''' '
