Stored Procedures flexibility

  • Hello All,

    As I am not a T-SQL developer, I want some help on stored procedures.

    In many cases it is needed to build a query that takes for example a different ORDER BY fields and a WHERE clause.

    To be more specific I will write an example:

    We have (in vb.net) the above code:

    String = "SELECT * FROM tbl WHERE 1=1 "

    if textbox.text <> "" then

    String &= "AND name='" & textbox.text & "' "

    end if

    String &= "ORDER BY " & DropDownListFields.SelectedValue

    As you see I build the query based on some values of textbox and dropdown lists... Ok, I know this technique is not

    correct. I want to call a stored procedure to do this, but this stored procedure must takes as parameters the ORDER

    field and take (if the textbox is not empty) the textbox.text value to search a specific name...

    I need some help on this.

    Thank you very much.

  • There's nothing wrong to construct a query based on whatever the userinput is, then when the query is known and complete, just run it from the app.

    There's no particular need to involve a stored procedure for this.

    /Kenneth

  • Thank you.

    So.. when is the stored procedures proper to use ?

  • Hi Dimitris,

    There is nothing wrong but it's a matter of good practice to place sql code inside sql and not in client code. There are lot of reason for that but I will not discuss about that now.

    About your example, you can solve it in a different ways. One is to simulate what you have in your vb code and looks like this:

    CREATE PROC udp_Search (@strWhere nvarchar(256)=null, @strOrder nvarchar(256)=null)

    AS

    BEGIN

    declare @strSQL nvarchar(1024)

    SET @strSQL = 'SELECT * FROM tbl WHERE 1=1 '

    if @strWhere is not null

    SET @strSQL = @strSQL + ' AND name=''' + @strWhere + ''''

    if @strOrder is not null

    SET @strSQL = @strSQL + ' ORDER BY ' + @strOrder + ''

    --print @strSQL

    EXEC sp_executesql @strSQL

    END

    More generic way will looks as follow:

    CREATE PROC udp_Search (@strName nvarchar(256)=null, @strOrder nvarchar(256)=null)

    AS

    BEGIN

    SELECT *

    FROM tbl

    WHERE (@strName is null or [Name]=@strName)

    ORDER BY case when @strOrder is not null then [Name] else 0 end desc

    END

    Both SP you can expand with parameters as much as you need.

  • Thank you very much!

    Is there a performance advantage when using stored procedures?

    I made an example and it seems to me that the results are returned quicker...

    Is it my idea or with the sp I have better performance???

  • Hi

    Stored procedures do have performance advantage over code written at client side. SP code resides in the server and is pre-compiled.

    "Keep Trying"

  • Jim, you are right. SP should give better performance. At least compilation is not needed on server side. But not only performance issue is reason to not write sql in your app code. Your application will be more independent from db changes. Also security issue - you can control execution right, etc ...

  • The pros and cons of procedures can fill entire books, but in general, I'd say that the concensus is that stored procs are a 'good thing'.

    However, it's a tool like many other things in the arsenal of T-SQL.

    Properly used, it's great, improperly may cause grey hairs and headaches.

    BOL (Books On Line) has very much written on and about stored procedures, not just syntax.

    (search for 'stored procedures')

    /Kenneth

  • Thank you all guys... very much appreciated...

    🙂

  • Probably the most important thing to avoid here is string concatenation, the last thing you want to do is blindly accept user provided parameters.

    The SQLCommand object allows for strongly typed parameters (as do stored procedures) as well. There is really nothing wrong with using a SQLCommand with parameters (stored procedures provide a nominal performance advantage when the SQL is fairly static). In your case, the variation of order by's, etc will probably result in little to no advantage when using a stored procedure. Look into writing your dynamic SQL to utilize parameters.

    The key is to use parameters rather than concatenated strings for your SQL statement.

    Joe

  • About string concatenations:

    http://xkcd.com/327/

    🙂

    _____________
    Code for TallyGenerator

  • Kenneth Wilhelmsson (10/9/2007)


    There's nothing wrong to construct a query based on whatever the userinput is, then when the query is known and complete, just run it from the app.

    There's no particular need to involve a stored procedure for this.

    /Kenneth

    I think constructing a query based on whatever the userinput is has its own weakness and many perpetrators (hackers) can exploit this type/kind of query

    "-=Still Learning=-"

    Lester Policarpio

  • I'm sorry if I was unclear.

    I don't mean 'whatever the input is' as in 'allow freetext queries', more in the form of 'whatever different combination of arguments/paramters the user chooses in the frontend'.

    (ie the WHERE clause can be any of a gazillion variants)

    /Kenneth

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

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