SQL Server Performance

Query optimization

Discussion in 'T-SQL Performance Tuning for Developers' started by menelaos, Mar 19, 2003.

  1. menelaos New Member

    Hi, I'm trying to optimize a stored procedure.
    I'm using the "exec sp_executesql"
    with an SQL statement that includes a few parameters.
    One of the parameters is -or I want it to be- in a comma delimited string i.e.
    '1,2,3' which I need to pass to a parameter of the
    "exec sp_executesql" command.
    For example I want to do

    exec sp_executesql
    N'select * from data where id in (@dataids),
    N'@dataids varchar(40),
    @dataids

    When I pass the '1,2,3' to @dataids from above, it thinks it is simply
    one value instead of three and it won't give the right
    results.

    Since I do want to use "exec sp_executesql", rather than
    rebuilding the SQL statement from scratch and the use it in sp_execsql, does any of you
    know if this is accompishable?

    Thanks in advance,
    Menelaos
  2. trifunk New Member

    You could try something like this :

    declare @sql nvarchar(1000)
    declare @param nvarchar(40)

    set @param = N'''1'',''2'',''3'''
    set @sql = N'select * from data where id in (' + @param + ')'
    exec sp_executesql @sql

    Not sure if it's exactly what you wanted but it should work

    Cheers
    Shaun



    World Domination Through Superior Software
  3. menelaos New Member

    Thanks, Shaun but I do not want to rebuild the SQL statement....
    which is what your solution does.
    I want to avoid re-preparing the query as much as possible...

    Menelaos
  4. Argyle New Member

    Create a stored procedure that takes an array of values as a single inparameter. Then execute the SP via sp_executesql. Search around on the net and you will find quite a few examples on how to parse a comma seperated array list in an SP. Most likely there are some examples on www.sqlteam.com or maybe on this site as well?

    /Argyle
  5. menelaos New Member

    The problem is not how I parse the comma delimited string.
    Rather my problem is how to pass a parameter to sp_executesql that can have
    more than one value.
  6. bambola New Member

    I wouldn't use sp_executesql for this task. Instead I suggest you write
    a function (if you're using SQL 2000) to parse the string.

    something like:

    create function parse_string(@param varchar(8000), @delimiter char(1) )
    returns @tElements table (element varchar(500)) -- or int or whatever you need it to be
    begin
    -- parse the string using charindex, and insert each item into the @table
    end

    At this point you can refer to it as any other table.

    declare @param nvarchar(40)
    set @param =N'''1'',''2'',''3'''

    select * from data
    inner join dbo.parse_string(@param, @delimiter) as elm
    on elm.element = data.id

    If you are using SQL 7, you can still so it with a sproc and #temp table.

    Bambola.
  7. menelaos New Member

    I did try to create a temp table but it took more time to create the table
    and execute the sp_executesql with this table rather than simply rebuild the SQL
    statement that sp_executesql will use.

    Any thoughts are welcomed
  8. bambola New Member

    quote:Originally posted by menelaos

    I did try to create a temp table but it took more time to create the table
    and execute the sp_executesql with this table rather than simply rebuild the SQL
    statement that sp_executesql will use.

    Any thoughts are welcomed

    You should also be considering security issues when using sp_executesql.

    Bambola.

    Bambola.

Share This Page