Dynamic SQL - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL

Author

Topic

jonno232
Starting Member

2 Posts

Posted - 2007-11-06 : 12:12:11

Hi,
First of all I am no SQL programmer but I am just after a few opinions on some code that our developers have written for a stored procedure on the backend of a website for a advanced search button.

ALTER PROCEDURE [dbo].[GetOrdersByAdvSrch]
@Guid varchar(10) = NULL,
@CustomerName [varchar](50) = NULL,
@name [varchar](50) = NULL,
@PostCode [varchar](10) = NULL,
@StartDate datetime,
@EndDate datetime,
@AccountCode [varchar](50) = NULL
AS
DECLARE @SQL VarChar(2000)

SELECT @SQL = 'SELECT DISTINCT [Order].OrderId, picture.Guid, [Order].DateReceived, picture.Orientation, Class.ClassId, Job.JobId, '
SELECT @SQL = @SQL + ' temp.Path + ''\'' + job.path + ''\'' + RTRIM(class.path) + ''\'' + picture.picturepath AS path FROM [Order] INNER JOIN picture ON [Order].pictureId = picture.pictureId'
SELECT @SQL = @SQL + ' INNER JOIN Class ON picture.ClassId = Class.ClassId INNER JOIN PhotographerJob ON Class.TableJobId = PhotographerJob.PhotographerJobId '
SELECT @SQL = @SQL + ' INNER JOIN job ON photographerjob.jobid = job.jobid INNER JOIN temp ON job.tempId = temp.tempId '
SELECT @SQL = @SQL + ' WHERE ([Order].DateReceived BETWEEN ''' + CAST(@StartDate AS VARCHAR(30)) + ''' AND ''' + CAST(@EndDate AS VARCHAR(30)) + ''' )'

IF NOT (@Guid IS NULL)
BEGIN

SELECT @SQL = @SQL + ' AND (picture.guid LIKE ''%' + @Guid + '%'')'
END

IF NOT (@CustomerName IS NULL)
BEGIN

SELECT @SQL = @SQL + ' AND ([Order].CustomerName LIKE ''%' + @CustomerName + '%'')'
END

IF NOT (@name IS NULL)
BEGIN

SELECT @SQL = @SQL + ' AND (picture.name LIKE ''%' + @name + '%'')'
END
IF NOT(@PostCode IS NULL)
BEGIN

SELECT @SQL = @SQL + ' AND ([Order].Postcode LIKE ''%' + @PostCode + '%'')'
END
IF NOT(@AccountCode IS NULL)
BEGIN
SELECT @SQL = @SQL + ' AND (temp.tempnumber LIKE ''%' + @AccountCode + '%'')'
END

SELECT @SQL = @SQL + ' ORDER BY picture.Guid, [Order].DateReceived ASC'

--print @SQL
EXEC (@SQL)

Should dynamic sql be used for something like this? Also I have read that using a like%% query doesn't use the indexes. Is this correct?

Many Thanks.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-06 : 15:32:49

LIKE '%
is not indexed.
So there are no benefits (in potential indexing) of these conditions
I would recommend to get rid of dynamic SQL completely

SELECT DISTINCT [Order].OrderId, picture.Guid, [Order].DateReceived, picture.Orientation, Class.ClassId, Job.JobId,
temp.Path + '\' + job.path + '\' + RTRIM(class.path) + '\' + picture.picturepath AS path FROM [Order] INNER JOIN picture ON [Order].pictureId = picture.pictureId
INNER JOIN Class ON picture.ClassId = Class.ClassId INNER JOIN PhotographerJob ON Class.TableJobId = PhotographerJob.PhotographerJobId
INNER JOIN job ON photographerjob.jobid = job.jobid INNER JOIN temp ON job.tempId = temp.tempId
WHERE ([Order].DateReceived BETWEEN @StartDate AND @EndDate

-- former dynamic part
(@GUID is NULL OR picture.guid LIKE '%' + @Guid + '%')
AND
(@CustomerName IS NULL OR [Order].CustomerName LIKE '%' + @CustomerName + '%)

...etc...
ORDER BY picture.Guid, [Order].DateReceived ASC

P.S
While I can believe that CustomerName is searched using LIKE '%name%',
I doubt that GUID is searched that way. Usually guids are compared literally, using =.
Something is wrong here...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-07 : 01:35:52

More on dynamic sql http://www.sommarskog.se/dynamic_sql.html
and dynamic search http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

jonno232
Starting Member

2 Posts

Posted - 2007-11-12 : 04:18:26

Thanks guys your information has been very useful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources