dynamic queries in sql

  • 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

  • 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

  • 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.

  • 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.

  • 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.

  • 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