April 4, 2002 at 8:55 am
I currently use a search form that pulls up a data grid using ADO. The users can type parameters into text boxes above the data grid. On average, they can provide 6 parameters. On click, the app modifies the dynamic SQL with ONLY the parameters provided to return a recordset to the data grid. Can anyone tell me if there is a way to use a single stored procedure that won't be junk (have asterisks in all columns where a parameter wasn't provided)? Best practices indicate using SP's, not dynamic SQL, but I'm having a hard time figuring out how to do it in this particular case.
April 4, 2002 at 1:10 pm
When you create stored procedure set default values for all parameters ( may be even '%' as default).
Then, when you call it from ADO, create CommandText line like:"exec MyProc " & "@myFirstParam = '" & txt1.text & "',@myFifthParam = " & cint(txt5.text)
(you can create this parameters' string dynamically, according to the provided parameters) and run it as usual SQL statement.
April 4, 2002 at 2:13 pm
Or send in all the values for the params to one procedure and have that one call the appropriate procedure to do what you want.
Steve Jones
April 4, 2002 at 5:51 pm
Or Can you do this if the column you return is fixed columns:
when you declare the sp, set the input parameters' values as null, then
passing in your parameters and construct your sql like this:
select some_ids_or fields
from your_table
where f1 = coalesce ( @input_para1, f1 )
and f2 = coalesce (@input_para2, f2 )
and f3 = coalesce (@input_para3, f3 )
and f4 = coalesce (@input_para4, f4 )
and f5 = coalesce (@input_para5, f5 )
Abby Zhang
April 4, 2002 at 7:29 pm
The last statement should work but you have to check the execution plan to make sure it does not cause an issue. I prefer Steves idea as the point of a Procedure is to take advantage of a saved execution plan to speed things up. And when you build seperate stored procedures calling them will allow each to use a stored execution plan specific to itself. However if any or any combination of all can be submited then Dynamic may be close to the only thing that will work. Something like this
CREATE PROCEDURE ip_GetData
@FParam AS DataType = NULL,
@SParam AS DataType = NULL,
@TParam AS DataType = NULL
AS
SET NOCOUNT ON
IF @FParam IS NULL AND @SParam IS NULL AND @TParam IS NULL
BEGIN
RAISERROR('You must enter at least on field',16,-1)
RETURN
END
DECLARE SQLStr AS VARCHAR(200)
SET @SQLStr = 'SELECT * FROM tblX WHERE '
IF @FParam IS NOT NULL
BEGIN
SET @SQLStr = @SQLStr + ' FParamCol = ' + @FParam ' AND '
END
IF @SParam IS NOT NULL
BEGIN
SET @SQLStr = @SQLStr + ' SParamCol = ' + @SParam ' AND '
END
IF @TParam IS NOT NULL
BEGIN
SET @SQLStr = @SQLStr + ' TParamCol = ' + @TParam ' AND '
END
SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 4)
EXEC(@SQLStr)
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 8:27 pm
I read an article talking about the opposite part. Here is some excerpts from the article:
"The dynamic SQL approach will certainly work, but it has two downsides. The first is that it is cumbersome to implement. Building SQL statements in this manner is an error-prone endeavor that takes a lot of time to get right. The second downside has to do with query performance speed. It is impossible for SQL Server's query processor to re-use execution plans produced by dynamic SQL statements. An execution plan is how the database engine actually retrieves the data from the database. When static SQL is used, execution plans can be re-used by different calls to the same statement. This results in faster query processing time because one less step is required to process the query. "
The author of this article uses coalesce to build a static sql.
Here is the url for the article:
http://www.sqlteam.com/item.asp?ItemID=2077
Since I am new to sql server, which way is better? Can we have a saved execution plan by using coalesce to build a static sql?
April 4, 2002 at 8:38 pm
Both have their bennifits and yes the coalesce method will allow a saved execution plan to be used but that execution plan may not cause it to outperform a dynamically built SQL string. And as for the statement
quote:
The first is that it is cumbersome to implement. Building SQL statements in this manner is an error-prone endeavor that takes a lot of time to get right.
I disagree with this. The reason is that I have been building dynamic SQL to build these strings when a complex situation occurrs and never had as much of an issue as when I rewrote most of them with non-dynamic SQL and with testing to make sure I got at least the same performance, took me a lot longer and considerable testing with some. This boils down to you should avoid but don't have to and in some case may not be able to. Also should always test what gives you the best performance even with a stored plan.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 8:49 pm
Thank you very much.
Do you have a way to return the result sets
to another procedure or function after
your execute the code:
exec (@SQLStr) ?
I have similar situation in my code. I need to
do a dynamic search in a table having 44 fields. I constructed a dynamic sql, but I don't know what will be an efficient way to save the exec(@dynamicSql) result, since I need to piece the search result with other
table search result together.
If it is a static sql ( by using coalesce ), then I can insert the search result to a table variable, but I can't insert the result to a table variable when it is from dynamic sql.
Using a temp table to save the exec (@dsql)
result will slow down the performance?
Thanks.
April 4, 2002 at 9:05 pm
Not sure there. Getting outside what I have done so I would have to find time to test and see if there is some way. Robert Marda wrote several articles on Dynamic SQL and there may be helpfull information in there to do this. Maybe someone else can jump in here, but you may want to post some more detail on what you do and how often this is done and hom many simultaneous transactions take place for us to understand better what must be met to help.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 6:29 am
The dynamic SQL I'm using can be any combination of an average of 6 parameters. I'm returning the same columns with each select statement, but using any combination of up to 6 parameters. I thought about creating a main sp to call the individual sp's, but felt I would end up with too many sp's. There are no performance issues yet (due to not being cached), but we're talking about small numbers of records so far (less than 100K). It is also a support nightmare b/c DBA can't see what the app is requesting and therefore can't troubleshoot. (We keep app support separate from DB support over here at Aetna).
April 5, 2002 at 9:00 am
We've been discussing this (me, Sean, Steve, Leon) about possible solutions. One was to build a code generator that would build procs for all the permutations. Leon has an interesting idea for building procs only for those combinations that occur frequently (or at all), probably see an article on that in the next few weeks.
I'm not sure that either is a good solution compared with building the SQL statement on the client and sending to the server. Performance is not THAT bad compared with the maintenance overhead of some of the solutions we have thought of or have been offered in this thread. The biggest issue to me is having to grant table access at all rather than going through a proc. In the case of searche forms/dialogs I don't see a better way.
Andy
April 5, 2002 at 9:08 am
The security issue is another big one. Right now, we try to grant execute only access b/c we're plagued with Access and Excel "dabblers", who try to do ad-hoc reporting with their select access. This causes nightmares for Network Engineering. I am actually building the statement on the client and sending it to the server. I tried doing this in a larger app (approx. 1m records) and got a fairly slow response requesting only 3 cols. I look forward to your article!! Perhaps I'll just remind my clients that, sometimes, performance suffers a little when functionality increases and let them weigh it out.
April 11, 2002 at 10:18 am
Sue,
Here is a simple example of a stored proc that searches on the parameters supplied.
Use nulls for the parameters that are not supplied.
USE pubs
GO
CREATE PROC SEARCH_AUTHORS
@lastName varchar(50),
@city varchar(50),
@state varchar(30)
AS
SET NOCOUNT ON
SELECT *
FROM pubs.dbo.authors (nolock)
WHERE
CASE
WHEN @lastName IS NULL THEN 1
WHEN UPPER(au_lname) = UPPER(@lastName) THEN 1
ELSE 0
END = 1
AND
CASE
WHEN @city IS NULL THEN 1
WHEN UPPER(city) = UPPER(@city) THEN 1
ELSE 0
END = 1
AND
CASE
WHEN @state IS NULL THEN 1
WHEN UPPER(state) = UPPER(@state) THEN 1
ELSE 0
END = 1
/************************************************************
Use these to test the stored proc.
EXEC pubs.dbo.SEARCH_AUTHORS 'green' , null, null
EXEC pubs.dbo.SEARCH_AUTHORS null , 'Oakland', null
EXEC pubs.dbo.SEARCH_AUTHORS null , null, 'Ca'
EXEC pubs.dbo.SEARCH_AUTHORS null , 'Berkeley', 'Ca'
***************************************************************/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply