How to How to execute a concatenated query in a procedure

  • I have a procedure which is->

    ============================================================

    create procedure procedure1(@city varchar(20),@department varchar(20),@name varchar(20))

    as

    begin

    declare @a varchar(50);

    declare @b-2 varchar(50);

    declare @C varchar(50);

    declare @d varchar(500);

    if @city=''

    set @a='';

    else

    set @a='where emp_city='+@city;

    if @department=''

    set @b-2='';

    else

    set @b-2='and emp_department='+@department;

    if @name=''

    set @C='';

    else

    set @C='and emp_name='+@name;

    set @d='select * from employees where'+@a+@b+@c;

    ---???????????????????????????WHAT TO DO TO EXECUTE @d?

    return

    end;

    ===========================================================

    Now in the procedure, @d holds a select query but it is in varchar format. I cannot execute that as a query in the procedure.

    How to execute the query stored in @d?

    pls help.

  • exec (@d) - note the brackets

  • another way:

    EXEC SP_EXECUTESQL @d

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

  • Why not just do it like this:

    SELECT * FROM employees

    WHERE emp_city = @city

    AND emp_department = @department

    AND emp_name = @name

    I recommend that you read this:

    http://www.sommarskog.se/dynamic_sql.html

    John

  • You might want to read this btw, it's very useful...

  • Beat me to it John, lol 🙂

  • This whole thing just isn't going to work as it sits.

    If city <> '' your query will be "select * from employees where where city = 'city'"

    if city = '' you query will be "select * from employees where and emp_department= '+ @department"

    if all three = '' your query will "select * from employees where"

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • subhankar02dey

    First I altered your T-SQL code:

    set @d='select * from employees where'+@a+@b+@c;

    PRINT @d -- added

    ---???????????????????????????WHAT TO DO TO EXECUTE @d?

    return

    Then executed the resulting SP as procedure1 'NY','ABC','SAM' with the following results:

    select * from employees wherewhere

    emp_city=NYand emp_department=ABCand

    emp_name=SAM

    I would respectfully suggest that before attempting to EXECUTE your procedure you check the format of the command produced.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I changed this up and made it so that it can easily handle a dynamic number of where conditions..

    create procedure procedure1

    (

    @city varchar(20),

    @department varchar(20),

    @name varchar(20)

    ) as

    begin

    declare @sql varchar(500);

    set @sql = 'select * from employees where 1 = 1 '

    if @city <> ''

    set @sql = @sql + 'and emp_city = ' + @city;

    if @department <> ''

    set @sql = @sql + 'and emp_department = ' + @department;

    if @name <> ''

    set @sql = @sql + 'and emp_name = ' + @name;

    exec SP_EXECUTESQL @sql

    end

    I would highly recommend you follow the suggested links above. Read them and understand what they are saying. Keep in mind this code I wrote is untested and HIGHLY vulnerable to sql injection. I would not suggest using this as is. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SeanLange (10/21/2010)


    I would highly recommend you follow the suggested links above. Read them and understand what they are saying. Keep in mind this code I wrote is untested and HIGHLY vulnerable to sql injection. I would not suggest using this as is. 😉

    Gosh, Sean... you shouldn't post the code if it's so bloody dangerous (and, it IS dangerous). Can't you post the correct method for doing this instead?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here is how to do it where you do not have the SQL injection problem and you get the plan-reuse features of sp_executesql. There is also proper semicolon usage for future versions of SQL Server.

    create table dbo.employees (emp_city varchar(20), emp_department varchar(20), emp_name varchar(20))

    go

    create procedure dbo.procedure1

    @city varchar(20),

    @department varchar(20),

    @name varchar(20)

    as

    declare @sStr nvarchar(1000);

    declare @pstr nvarchar(1000);

    declare @conj nvarchar(1000) = N'where ';

    set @sStr = N'

    select *

    from dbo.employees;';

    if (@city > '') begin

    set @sStr = REPLACE(@sStr, N';', N'

    ' + @conj + N' emp_city = @city;');

    set @conj = case @conj when N'where ' then N'and ' else @conj end;

    end;

    if (@department > '') begin

    set @sStr = REPLACE(@sStr, N';', N'

    ' + @conj + N' emp_department = @department;');

    set @conj = case @conj when N'where ' then N'and ' else @conj end;

    end;

    if (@name > '') begin

    set @sStr = REPLACE(@sStr, N';', N'

    ' + @conj + N' emp_name = @name;');

    set @conj = case @conj when N'where ' then N'and ' else @conj end;

    end;

    set @pstr = N'

    @city varchar(20),

    @department varchar(20),

    @name varchar(20)

    ';

    -- Remove print after debugging

    print @sStr + N'

    ';

    exec sp_executesql

    @stmt = @sStr,

    @params = @pstr,

    @city = @city,

    @department = @department,

    @name = @name

    ;

    GO

    exec procedure1

    @city = '',

    @department = '',

    @name = ''

    ;

    exec procedure1

    @city = 'asdf',

    @department = '',

    @name = ''

    ;

    exec procedure1

    @city = '',

    @department = 'asdf',

    @name = ''

    ;

    exec procedure1

    @city = '',

    @department = '',

    @name = 'asdf'

    ;

    exec procedure1

    @city = 'asdf',

    @department = 'asdf',

    @name = ''

    ;

    exec procedure1

    @city = 'asdf',

    @department = '',

    @name = 'asdf'

    ;

    exec procedure1

    @city = '',

    @department = 'asdf',

    @name = 'asdf'

    ;

    exec procedure1

    @city = 'asdf',

    @department = 'asdf',

    @name = 'asdf'

    ;

    GO

    drop procedure dbo.procedure1;

    drop table dbo.employees;

    GO

    Sincerely,
    Daniel

  • Come to think of it, why use dynamic SQL at all? You can simply pass the variables to a standard select query with the use of a "like" predicate, without degrading performance.

    Using the following example, the execution plan estimates a 49/51% split for the two methods given (with the 'like' query slightly more expensive), probably as a result of the seek predicate changing from "= value" to ">= value and <= value". But, on execution, the read counts are the same. (Note that this method works only because we pass an explicit value to the like predicate, without any % bracketing to get in the way of an index seek).

    If you have a properly clustered index on the table, covering all of the searched fields, this method should be at least as fast (if not faster) and avoids the security issue completely. Note that your index should have the most frequently searched column as the clustering key, followed by the second most searched, etc.

    Thoughts?

    -- create temp table for testing

    create table #t (f1 varchar(10), f2 varchar(10), f3 varchar(10))

    -- use tally table to populate test data

    insert #t

    select 'Bob','Sue','Dave'

    from Dev_Utils.dbo.Tally_Table

    where Tally_Number <=1000

    union all

    select 'Mike','Claire','Lucy'

    from Dev_Utils.dbo.Tally_Table

    where Tally_Number <=1000

    union all

    select 'Sarah','Louise','Iain'

    from Dev_Utils.dbo.Tally_Table

    where Tally_Number <=1000

    -- cluster table, covering all fields <-- important

    create clustered index ix_f1 on #t(f1,f2,f3)

    --------------------------------------------

    -- make sure counts are off

    set statistics io off

    -- simulate some proc inputs

    declare @c1_input varchar(10) = 'Bob'

    , @c2_input varchar(10) = null

    , @c3_input varchar(10) = null

    -- simulate converting proc inputs

    declare @c1 varchar(10)

    , @c2 varchar(10)

    , @c3 varchar(10)

    -- set inputs

    -- either use the passed in value

    -- or use a single %

    -- which will return all rows on a like query

    set @c1 = isnull(@c1_input, '%')

    set @c2 = isnull(@c2_input, '%')

    set @c3 = isnull(@c3_input, '%')

    -- start counting

    set statistics io on

    -- clear buffers - DO NOT DO THIS ON A PRODUCTION SERVER

    dbcc dropcleanbuffers

    dbcc freeproccache

    -- select via variable criteria

    select * from #t

    where f1 like @c1

    and f2 like @c2

    and f3 like @c3

    -- Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0

    -- clear buffers

    dbcc dropcleanbuffers

    dbcc freeproccache

    -- equivalent passed to dynamic sql using old method

    select * from #t

    where f1 = 'Bob'

    -- Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0

    set statistics io off

    drop table #t

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply