September 2, 2008 at 4:00 am
Hi,
We are all aware of dynamic queries. for executing dynamic queries we use EXECUTE / sp_ExecuteSQL. but if we use dynamic queries in SP and we have security contraints we need to specify the permission for table or for SP [using EXECUTE AS Clause]
Query :
I have 1 table Product( ProductID, ProductName, Description, Price ). I have created 1 SP of retrieving records from product tables with input parameters... ProductID, ProductName, Description, Price which are nullable. It means user can search on any search parameter in where clause. Now the Code look like this
create procedure dbo.uspProductSel
(@ProductID INT, @ProductName VARCHAR(100), Description VARCHAR(255), Price MONEY)
As
begin
set nocount on
--- either i can write the query in this way
select * from product where (@ProductID is null or ProductID = @ProductID ......)
-- OR in this way
declare @sql varchar(max)
if @ProductID is not null
set @sql = 'ProductID = ' + cast(@ProductID as varchar(10))
if @ProductName is not null
set @sql =@sql + 'ProductName Like ' + @ProductName + '%' ......
in the 1st way i can execute the procedure directly; but in 2nd way i have to execute it as dynamic query.
Suppose SP only accepts WhereClause send by Application.
create procedure dbo.uspProductSel
(@WhereClause varchar(max) = null)
WITH EXECUTE AS OWNER
as
begin
declare @sql varchar(8000)
set @sql = 'select * from product ' + case when @WhereClause is null then ''
else 'where' + @WhereClause end
execute sp_executeSQL @sql
end
in this case if i use the dynamic query then i have to use EXECUTE clause as specified. How i avoid using dynamic query in this case
Abhijit - http://abhijitmore.wordpress.com
September 2, 2008 at 9:11 am
Does the table you're querying have an ID column or some other primary key?
If so, a union statement might be better than dynamic SQL for this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 1:06 pm
Hi Abhijit,
I don't think you can avoid Dynamic SQL if your intention is to have the application supply the text of the where clause as an input parameter the way you show in your last example.
I think you are on the correct track in you first example with the where clause testing the parameter for NULL.
Just a few days ago one of the articles was titled "Dynamic Where Clauses". It has more detailed information. You should refer to it.
September 4, 2008 at 12:18 am
Ray,
I appreciate your view but the problem arise for permissions when i execute the SP either i have to user EXECUTE AS Clause or I have set the permissions for table.
GSquared,
yes I have primary key on that table i.e ProductID. but how could i achive the resultset. where i dn't know the columns to be search it come from Application. as mention by Ray.
September 4, 2008 at 7:55 am
Hi Abhijit,
personally, I think it is a mistake to accept the text of the where clause from the application. First, you are opening a huge security hole for SQL Injection. Second you are depending on the application to provide a syntactically correct string. Third, you run the risk of the application seriously degrading performance with a poorly constructed where clause or with additional clauses. I am sure I can come up with other reasons but any of these three is sufficient to not do what you are proposing.
If you really do not know the where conditions then perhaps you need to reassess the application. If you don't know the where conditions then can you really know what columns the app is interested in?
If it is truly AD HOC in nature then let the application generate and execute SQL queries directly via OLE, ADO or whatever. Then the Application is completely responsible for the syntax and performance.
September 4, 2008 at 9:02 am
It would look something like this:
;with IDs (ProdID) as
(select ProductID
from dbo.Table
where ProductID = @ProductID
union
select ProductID
from dbo.Table
where ProductName = @ProductName
union
select ProductID
from dbo.Table
where Description = @Description
union
select ProductID
from dbo.Table
where Price = @Price)
select *
from dbo.Table
inner join IDs
on Table.ProductID = IDs.ProdID
Of course, using Union makes it an OR comparison. If you change it from Union to Intersect, it will operate as an AND comparison. Other combinations are possible using Except, etc.
This allows you to search on all the columns you want to search on, without having to police for SQL injection. It usually performs pretty well, too.
If you change the final query from a join to a Where In, you can use Union All in the CTE, which might perform better, depending on your indexes, etc. Try both, see how each performs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply