May 23, 2007 at 4:25 am
how do i write a stored procedure where input parameters are not fixed
any idea, or do i need to pass a sql string which will then concat with sql string in stored procedure and fire a query
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 23, 2007 at 12:00 pm
You will need to create your SP wit the max number of parameters allowed and then design the logic around them based on their values. if you are using those params to build the WHERE clause then you already know what to do...build you SQL string and then sp_executesql "it".
May 24, 2007 at 7:42 am
sp_executesql looks more easy , i guess i will use that
but then will it be giving me the benfits of compiled sql query, iam looking for more speed in query execution,
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 24, 2007 at 11:01 am
What's the nature of your input?
May 25, 2007 at 4:37 am
it is a search query, generated based on user selection and unselection
input can be number or text
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 25, 2007 at 6:55 am
can you post an example?
May 25, 2007 at 7:03 am
sp_executesql will reuse compile query plans but it doesn't benefit from pre-compiled query plans like stored procedures do. If you only have two parameters beind passed to your procedure then add two parameters to your procedure, one a "number" parameter and the other one a "character" parameter. Such as:
CREATE PROCEDURE MyProcedure
@NumParameter INTEGER,
@CharParameter VARCHAR(60)
AS
BEGIN
IF @NumParameter IS NULL
SELECT TOP 10 * FROM XTable WHERE Name = @CharParameter
ELSE
SELECT TOP 10 * FROM XTable WHERE EmployeeID = @NumParameter
END
If you are really looking for speed though, I first recommend that you spend a few hours looking at the query plans for the queries you are executing and work on optimizing your indexes, then consider converting to a stored procedure. You will gain a performance advantage from compiled query plans but if your query isn't performing well in the first place, you probably won't even notice the boost in performance.
May 27, 2007 at 10:27 pm
for many methods of constructing a dynamic search condition, read this:
http://www.sommarskog.se/dyn-search.html
---------------------------------------
elsasoft.org
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply