|
CREATE PROCEDURE [dbo].[sp_GetPatientsEvaluation]
@PFirstName as nvarchar(max),
@PLastName as nvarchar(max),
@StartDate as DateTime=null,
@EndDate as DateTime=null
AS
BEGIN
SET NOCOUNT ON;
declare @strSelectQry nvarchar(max)
Declare @where_clause NVARCHAR(1000)
set @where_clause=' where FirstName like ''%'+@PFirstName+'%'' AND LastName like ''%'+@PLastName+'%'''
if @StartDate is not null
begin
set @where_clause=@where_clause + ' and pe.EvaluationDate >= ''' + Convert(varchar(10),@StartDate,101) + ''''
end
if @EndDate is not null
begin
set @where_clause=@where_clause + ' and pe.EvaluationDate <= ''' + Convert(varchar(10),@EndDate,101) + ''''
end
set @strSelectQry = 'SELECT p.patientid,p.FirstName + '' '' + p.LastName as ''PName'',p.DateofBirth,isnull(pe.PatientEvaluationInformationID,0) as ''PatientEvaluationInformationID'',pe.EvaluationDate,
pe.EvaluationStartTime,pe.EvaluationLength,pe.EvaluationEndTime,pe.SourceOfInformation,pe.EvaluatedBy,
(Select top 1 PatientEvaluationInformationID from PatientEvaluationInformation
where PatientEvaluationInformation.Patientid = p.patientid and PatientEvaluationInformation.EvaluationType =1
order by EvaluationDate Desc) as PatientFollowUpEvaluationInformationID,
(Select count(EvaluationDate) from PatientEvaluationInformation where PatientEvaluationInformation.Patientid = p.patientid
and PatientEvaluationInformation.EvaluationType =1) as TCount,
(Select top 1 EvaluationDate from PatientEvaluationInformation
where PatientEvaluationInformation.Patientid = p.patientid and PatientEvaluationInformation.EvaluationType =1
order by EvaluationDate Desc) as LatestDate
FROM patients p
LEFT OUTER JOIN
PatientEvaluationInformation pe on p.patientid =pe.patientID and pe.EvaluationType =0'
exec (@strSelectQry + @where_clause + ' order By p.patientid DESC')
END
please can u help me to change my store procedure.for custom paging
|
|
|
|
|
Thank you for an interesting article.
|
|
|
|
|
Ejecuto lo siguiente:
execute GetProducts 1,5,'ProductName',1,'ProductName','Chai'
Y muestra:
select p_Id as ProductId,
p_Name as ProductName,
p_Description as Description,
p_Quantity as Quantity,
p_Price as Price
from
(
SELECT ROW_NUMBER() OVER ( ORDER BY [p_Name] ASC )
AS [ROW_NUMBER],
[t0].[p_Id], [t0].[p_Name],
[t0].[p_Description],
[t0].[p_Quantity],
[t0].[p_Price]
FROM [dbo].[Products] AS [t0] WHERE p_Name like '%Chai%' ) AS [t1] WHERE [ROW_NUMBER] BETWEEN @StartRowIndex AND @MaxRows ORDER BY [p_Name] ASC
Mens. 207, Nivel 16, Estado 1, Línea 14
El nombre de columna 'p_Name' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 8
El nombre de columna 'p_Name' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 10
El nombre de columna 'p_Id' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 10
El nombre de columna 'p_Name' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 11
El nombre de columna 'p_Description' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 12
El nombre de columna 'p_Quantity' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 13
El nombre de columna 'p_Price' no es válido.
¿Que puede estar pasando?
|
|
|
|
|
The use of dynamic sql, as has been stated, is fraught with the dangers of SQL injection.
Your query can easily be re-written to use no dynamic sql at all.
set rowcount @MaxRows;
select
p_Id as 'ProductId'
,p_Name as 'ProductName'
,p_Description as 'Description'
,p_Quantity as 'Quantity'
,p_Price as 'Price'
from
(
select
row_number() over
(
order by case quotename(@OrderByField )
when '[ProductName]' then p.p_name
when '[Description]' then p.p_Description
end) as RowNumber
,p.p_Id
,p.p_Name
,p.p_Description
,p.p_Quantity
,p.p_Price
from dbo.Products as p
where @SearchValue like case quotename(@SearchField)
when '[ProductName]' then '%' + p.p_Name + '%'
when '[Quantity]' then convert(nvarchar(10), p.p_Quantity)
when '[Price]' then convert(nvarchar(10), p.p_Price)
end
) as w
where RowNumber >= @StartRow;
Adding additional fields to select or order on is simply a matter of adding to the appropriate case statement.
The query above does not allow the ordering by ascending or descending, but I am yet to find any paging solution that requires the paging order to change. If you know the product, set the paging order.
There are ways of changing the order of the results without resorting to dynamic sql, but that is for another exercise.
|
|
|
|
|
Good work....
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
this is an example not source
|
|
|
|
|
What is the minimum version of SQL Server that this will work with?
|
|
|
|
|
you need sql server 2005 for this to run as rownumber function not available in previous versions.
|
|
|
|
|
Hello ,
Thank you very much to showing interest in my article.
Row_Number() is available in SQL SERVER 2005.
For the previous version than SQL SERVER 2005, You can create custom temporary table with Auto Incremented Column.
Then you can apply the same logic to it.
Thanks & Regards,
Mohan
|
|
|
|
|
SQL can be injected in your stored procedure using the searchValue parameter. Try something like the code below and you’ll see that a record is added into the table. (You might have to change the insert statement depending on your table scheme.) This can be prevented by passing the searchValue as a parameter to the sp_Executesql procedure.
DECLARE @searchValue AS VARCHAR(200)
-- complete the query in the stored procedure before the searchValue
SET @searchValue = '10) AS [t1];'
-- add an insert query to the searchvalue
SET @searchValue = @searchValue + 'INSERT INTO Products VALUES (NEWID(), ''T'', ''T'', 1, 1.0);'
-- complete the query in the stored procedure after the searchValue
SET @searchValue = @searchValue + 'select p_Id ProductId,'''' ProductName,'''' Description,1 Quantity,1 Price FROM (select *, 1 as ROW_NUMBER from Products'
EXEC GetProducts 0, 1000, 'ProductName', 1, 'Quantity', @searchValue
SELECT * FROM Products WHERE p_Name = 'T'
I’m also curious why you’re using a table variable instead of simple calling sp_Executesql like this:
EXEC sp_Executesql @Query,
N'@OrderQuery nvarchar(max),
@StartRowIndex int,@MaxRows int',
@OrderQuery=@OrderQuery,
@StartRowIndex = @StartRowIndex,
@MaxRows = @MaxRows
|
|
|
|
|
Wouldn't using .NET command object take care of the SQL injection problem for you? Granted this is not my favorite way to get pages but he at least recognizes that for true perfomance dymanic sql is going to have to be involved. Which means he is reducing the dataset size right from the start, I'm gonna have to give him points for this.
|
|
|
|
|
If you’re using .Net Commands the right way it probably will solve the SQL injection problem, but there’s no guarantee .Net is used. I’m also not saying dynamic queries are bad. I think it’s a good solution for this problem. By passing the searchvalue as a parameter to the sp_Executesql procedure however you can solve the SQL injection problem, still have a dynamic query and you don’t have to rely on .Net Commands.
|
|
|
|
|
That's a good point, I just assumed that everyone uses .NET with SQL Server because I do. Good thing I'm not narrow minded lol.
|
|
|
|
|
First of all Thank you for showing your interest in my article.
I have used table variable because I am using this store procedure in Linq to SQL.
When you use directly EXEC sp_Executesql,Linq To Sql parse it as integer value. So it will not get exact table structure to create class definition for it.
Mohan Prajapati
|
|
|
|
|