Stored Procedures - Eliminating Repetitive code
-
Remi,
Good solution. Using a table function definitely improved the performance. It takes 10 sec compared to 5 sec without using a function at all (I guess that's expected) but definitely much better than the 30 sec it took with using scalar functions as part of the query. Thanks a lot.
Bala.
-
Maybe something was recompiled, or rewritten in some way because you shouldn't get that big of a boost... moving from a sp to am inline function.
-
hello to all, i just want butt-in into this discussion thread and know how to pass a condition to a sp, the condition contains a quote as the filter expr is string..
exec stored_procedure 'select * from table1 where tag = '1''
is this possible or is it better to do it with a script like this:
declare @tagfilter char(1)
set @tagfilter = '1'
exec stored_procedure
'db',
'select * from table1 where tag = @tagfilter', 'c:\outputfile.txt
go
im not sure if the script i presented above is correct, how is macro substitution done is sql? also, the stored_procedure im referring to is sp_dbm_query_to_file, i got it from this site, it claims to redirect the output of the passed query into a textfile, but so far im having difficulty producing the output file..
any help will be appreciated, thanks ..
-
hello to all, i just want butt-in into this discussion thread and know how to pass a condition to a sp, the condition contains a quote as the filter expr is string..
exec stored_procedure 'select * from table1 where tag = '1''
is this possible or is it better to do it with a script like this:
declare @tagfilter char(1)
set @tagfilter = '1'
exec stored_procedure
'db',
'select * from table1 where tag = @tagfilter', 'c:\outputfile.txt
go
im not sure if the script i presented above is correct, how is macro substitution done is sql? also, the stored_procedure im referring to is sp_dbm_query_to_file, i got it from this site, it claims to redirect the output of the passed query into a textfile, but so far im having difficulty producing the output file..
any help will be appreciated, thanks ..
-
Victor, please repost your question by creating a new thread - this thread deals with another question entirely. It will be very confusing with two discussions in one thread.
/Kenneth
-
Here's a way to consolidate the aggregates into one query by using CASE.
SELECT t.CurDate,
CASE @option
WHEN 1 THEN SUM(t.Value)
WHEN 2 THEN AVG(t.Value)
END
FROM table1 t
WHERE <...complex whereclause...>
GROUP BY t.CurDate
This way the complex where clause is only needed once. Unfortunately you can't also use option 3, since it has no aggregate.
/Kenneth
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply