October 9, 2007 at 2:36 am
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.
October 9, 2007 at 3:41 am
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
October 9, 2007 at 3:45 am
Thank you.
So.. when is the stored procedures proper to use ?
October 9, 2007 at 4:09 am
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.
October 9, 2007 at 5:14 am
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???
October 9, 2007 at 5:56 am
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"
October 9, 2007 at 6:04 am
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 ...
October 9, 2007 at 7:49 am
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
October 9, 2007 at 8:24 am
Thank you all guys... very much appreciated...
🙂
October 9, 2007 at 10:37 pm
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
October 10, 2007 at 10:13 pm
October 11, 2007 at 2:16 am
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
October 11, 2007 at 2:35 am
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