Returning User Defined Data Requests.
-
I have a few questions about building stored procedures that returns data from search criteria entered by the user.
1) Is there any alternative to using a Dynamic Query?
2) Is there a much of a performance drop off with a Dynamic Query?
Here is an example of a Dynamic Query in which I was wondering if there was a more efficient way of writing it.
CREATE Procedure pr_BuildSrchReadingData( @Meterkey int, @BuildingKey int , @Month int, @Year int)
-- Routine Called: Form SrchReadings -> When Search Button is Clicked
-- Purpose: Fills the Table tblEditReadings with the search results (Only inserts data needed for viewing)
As
DELETE From tblEditReadings WHERE Userid = system_User
DECLARE @Select nvarchar (1000)
SET @Select = 'INSERT INTO tblEditReadings(TransNum, BuildingNum, MeterId, UtilType, Month, Year, Usage, Costtotal, Userid)
SELECT TransNum, Buildingnum, MeterId, UtilType, Month, Year, Usage, Costtotal, System_User
FROM qryUtilRead_Search WHERE TransNum is not null'
IF ( @meterkey > 0) SET @Select = @Select + char(13) + ' AND Meterkey = ' + cast(@meterkey as nvarchar(5))
IF(@buildingkey > 0) SET @Select = @Select + char(13) + ' AND Buildingkey = ' + cast(@buildingkey as nvarchar(5))
IF(@Month > 0) SET @Select = @Select + char(13) + ' AND Month = ' + cast(@Month as nvarchar(5))
IF(@Year > 0) SET @Select = @Select + char(13) + ' AND Year = ' + cast(@Year as nvarchar(5))
exec(@Select)
return
Thanks.
fryere
fryere
-
If the part that is dynamic includes the structure of the tables - e.g. column names, table names, then these can't be parameterised directly.
You can however do the following:
Insert everything into temp table
if Meterkey > 0
delete from temptable where meterkey <> meterkey
etc...
select * from temptable
But I wouldn't recommend that!
Dynamic SQL in SQL 2000 is fast - there will be 11 Execution plans for this stored procedure stored in SQL Server, which is quite a lot but I can't immediately think of any better way right now. I'll have a think about it.
Dave Hilditch.
-
I believe the example below could be an alternative approach, given the assumption that 0 in input indicates that this is no search-criteria. It is doubtful whether this is faster, though, and my expericence is that the dynamic SQL is fast enough in most cases.
CREATE Procedure pr_BuildSrchReadingData( @Meterkey int, @BuildingKey int , @Month int, @Year int)
-- Routine Called: Form SrchReadings -> When Search Button is Clicked
-- Purpose: Fills the Table tblEditReadings with the search results (Only inserts data needed for viewing)
As
DELETE From tblEditReadings WHERE Userid = system_User
INSERT INTO tblEditReadings(TransNum, BuildingNum, MeterId, UtilType, Month, Year, Usage, Costtotal, Userid)
SELECT TransNum, Buildingnum, MeterId, UtilType, Month, Year, Usage, Costtotal, System_User
FROM qryUtilRead_Search
WHERE TransNum is not null
AND @meterkey in (0, MeterKey)
AND @buildingkey in (0, BuildingKey)
AND @Month in (0, Month)
AND @Year in (0, Year)
RETURN
I would guess that if the table (qryUtilRead_Search) is relatively small, this would perform better than dynamic SQL. If the table is large, I think the overhead of dynamic SQL would be small compared to the penalty of the rather inefficient "in"-clause - which effectively translates into an or-clause.
-
Thank you for the replys. I'm glad to hear that the performance of Dynamic queries are usually fine. I will look into the "In" clause olavho.
If anyone else has any comments on Dynamic queries verses other methods, I would certainly enjoy reading them.
Thanks.
fryere
fryere
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply