Fine Tune My Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Fine Tune My Query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-09 : 01:23:41

Dear Friends,

I have the query that are used to fetch the value, I have to check the different Column name for different scenario, So i am forming the Dynamic query based on the user input, but i want to fine tune my query if possible.

Table

ReferenceId | Company | Location | Place | SubLocation | PlaceRegion
1001 | 15 | 0 | 5
1003 | 15 | 4 | 0
1004 | 14 | 0 | 6

I will have value[Not 0] either in Location / Value, And the value will be unique.

@ColumnName VARCHAR(30),
@Company VARCHAR(20),
@Value VARCHAR(20)

IF(@ColumnName = 'Company')
BEGIN
SET @StrQuery = 'SELECT ReferenceId FROM ReferenceInfo WHERE '
+ ' Company = ' + @Value + ' AND Location = 0 AND Place = 0 AND SubLocation = 0 AND PlaceRegion= 0'
END

IF(@ColumnName = 'Location')
BEGIN
SET @StrQuery = 'SELECT ReferenceId FROM ReferenceInfo WHERE '
+ ' Company = ' + @Company + ' AND Location = ' + @Value + ' AND Place = 0 AND SubLocation = 0 AND PlaceRegion= 0'
END

IF(@ColumnName = 'Place')
BEGIN
SET @StrQuery = 'SELECT ReferenceId FROM ReferenceInfo WHERE '
+ ' Company = ' + @Company + ' AND Location = 0 AND Place = ' + @Value + 'AND SubLocation = 0 AND PlaceRegion= 0'
END

-- Like the above query i will do for SubLocation and PlaceRegion also

EXECUTE SP_EXECUTESQL @StrQuery

I would like to hear from the experts to fine tune my Stored procedure, Please help me to get a more effective SP, because i may be have more records.

Devart
Posting Yak Master

102 Posts

Posted - 2010-08-09 : 02:40:23

Hello,

You can try this:

CREATE PROCEDURE <your_procedure_name>
(@ColumnName VARCHAR(30), @Company VARCHAR(20), @Value VARCHAR(20)
AS
SET NOCOUNT ON

DECLARE @Location VARCHAR(20)
DECLARE @Place VARCHAR(20)
DECLARE @SubLocation VARCHAR(20)
DECLARE @Region VARCHAR(20)
set @Location = '0'
set @Place = '0'
set @SubLocation = '0'
set @Region = '0'

IF @ColumnName='Company'
SET @Company=@Value
IF @ColumnName='Location'
SET @Location=@Value
IF @ColumnName='Place'
SET @Place=@Value
IF @ColumnName='SubLocation'
SET @SubLocation=@Value
IF @ColumnName='Region'
SET @Region=@Value

SELECT
ReferenceId
FROM
ReferenceInfo
WHERE
Company = @Company AND
Location = @Location AND
Place = @Place AND
SubLocation = @SubLocation AND
PlaceRegion= @PlaceRegion

Best Regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-09 : 02:58:59

Hello Devart,

Thank you so much, it helped lot..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources