Post reply

Manipulating Strings in a Stored Procedure

  • October 22, 2002 at 4:35 am

    #158796

    Hi,

    I am having problems passing in a string into a stored procedure. Is it possible to run this query without using the "=" sign and replacing it with the string passed in. Ie, I am passing in the string @strProduct2 which is "<> 'B'" into the query, so I wanted the last line to say "Where (R2090_Product & @strProduct2), hoping it would transform it to WHERE (R2090_Product <> 'B') but I am having problems with the quotes, can anyone help?

    SELECT Sum(R2090_Results_PD1),

    Sum(R2090_Results_PD2)

    From R2090_Results_PD1

    WHERE (R2090_Product = '' + @strProduct2 + '')

  • October 22, 2002 at 5:33 am

    #439108

    Sorry you can only do that with dynamic SQL as the string will be passed as '<> B' and not <> 'B'

    You could however create several procedures with each having a specific operator like != and = and > and <. Then pass your data into a main SP that based on the operator in the string picks and calls te right version and passes all but the operator to the operator based SP. Means more code but the performance is better that dynamic SQL offers and you don't potentially open yourself to injection attacks.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • October 22, 2002 at 5:37 am

    #439110

    The only way to achieve this I know of, is using dynamic SQL.

    It would turn out something like :

    

    DECLARE @strSql VARCHAR(1000)

    SET @strSql = "SELECT SUM( ... WHERE R2090_Product " + @strProduct2

    EXEC (@strSql)

    This clearly has some downsides, found all over this site like :

    - no cached query plan

    - not possible to assign values to variables inside the dynamic SQL

  • October 23, 2002 at 2:06 am

    #439192

    Thank you both for your help, I have managed to do it by using a dynamic query as you said.

  • October 23, 2002 at 4:21 am

    #439199

    You should pass in the operator and variable as 2 different parameters, this will allow the use of compile queries.

    DECLARE @strSql VARCHAR(1000)

    SET @strSql = "SELECT SUM( ... WHERE R2090_Product " + @operator + " @value"

    exec sp_executesql @strSql, N'@value int', @value

    Where @operator is the operator passed in and the @value is the value passed in.

    You can use sp_executesql to assign values to variables as well

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 23, 2002 at 5:18 am

    #439208

    simonsabin,

    quote:

    this will allow the use of compile queries.

    I am confused by this and some other statements made in regards to execution plans wth sp_executesql in Procs. Can I ask where this information has come from so I can verify, as I don't believe this is correct, especially since they are altering the query string each time and EXECUTE compiles queries as well to run them.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

    Edited by - antares686 on 10/23/2002 05:19:01 AM

  • October 23, 2002 at 6:32 am

    #439214

    If you have a look at my post on the http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=2568&FORUM_ID=76&CAT_ID=7&Topic_Title=When+to+Use+Dynamic+SQL&Forum_Title=Discuss+Content+Posted+by+Robert+Marda

    to test what i say create a looping bit of code, using a nice query (a couple of joins), try with execute and with sp_execute changing the value in the query for each loop. Profile the server for recompiles. execute will have lots or recompiles sp_executesql won't.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 23, 2002 at 7:29 am

    #439218

    Ok, just to clear things up. Looping thru and not changing the string will work that way. However once the execution of the loop or process is over and the sp_executsql process goes out of scope or the string is changed there will be a compile again.

    From BOL

    quote:

    sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

    Being able to substitute parameters in sp_executesql offers these advantages to using the EXECUTE statement to execute a string:

    1) Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.

    2) The Transact-SQL string is built only once.

    3) The integer parameter is specified in its native format. Casting to Unicode is not required.

    If you look at advantage one, you will understand why a loop with sp_executsql does not recompile like EXECUTE does. However if you change the string a recompile will occurr. Also, when the processing of sp_executsql goes out of scope the execution plan is dropped not stored.

    There are advantages with sp_executsql but in a quick and dirty situation EXECUTE is faster and easier to implement with the same effect.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • October 23, 2002 at 8:17 am

    #439226

    My understanding is that there is no scope on execution plans.

    When a query is executed the plan is parameterised and match is looked for in the cache if one is found it uses it. If not a plan is compiled and put in the cache, which can then be matched against other queries when submitted.

    Trying to find the document which details this.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 23, 2002 at 8:24 am

    #439229

    Here you go it is for 7 but won't have changed much for 2000

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp

    Nice bed time reading

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 10 posts - 1 through 10 (of 10 total)

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