June 22, 2010 at 2:57 pm
Hi folks,
I have a big stored procedure, it is a long sp with long redundant queries, for example:
insert into #temp
SELECT distinct 0, m.ID, Status,
dbo.fnReadField(description, 'TargetServer.hostName') as HostName,
FCID = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'TargetAccount.userName'), 1, 50) else '' end,
PLOG = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'referenceCode'), 1, 50) else '' end,
StartDate,
dbo.fnReadField(description, 'TargetApplication.name') as ApplicationName,
Reason = case when Substring(dbo.fnReadField(description, 'reasonDetails'), 1, 50) <> '' then Substring(dbo.fnReadField(description, 'reasonDetails'), 1, 50) + '...'
else '' end,
CreateUserID,
Manager = case when u3.firstname + ' ' + u3.lastname is not null then u3.firstname + ' ' + u3.lastname else 'Unknown' end,
ManagerID = case when u3.firstname + ' ' + u3.lastname is not null then u3.id else -1 end
from Metric m
left outer join u1 on m.createuserid = SUBSTRING(u1.[login], CHARINDEX('\',u1.[login]) + 1, len(u1.[login]))
left outer join u2 on u2. firstname + ' ' + u2.lastname = u1.Manager
left outer join u3 on u3.[login] = m.lastupdatedby
where dbo.fnGetDateDiff(m.id) <= case when @day = 0 then 365 else @day end
and type = 'viewAccountPassword' and dbo.fnReadField(description, 'this.cn') = 'viewAccountPassword'
and ([Status] = @Status)
Now this query is used in many places in the sp, I want to convert it dynamic sql to make the whole sp easier for maintenance, so what I am doing is:
Declare @SelectApprovedCondition varchar(1000)
Set @SelectApprovedCondition = '
insert into #temp
SELECT distinct 0, m.ID, Status,
dbo.fnReadField(description, ' + '''' + 'TargetServer.hostName' + '''' + ') as HostName, ' +
'FCID = case when type = ' + '''' + 'viewAccountPassword' + '''' + ' then Substring(dbo.fnReadField(description, ' + '''' + 'TargetAccount.userName' + '''' + '), 1, 50) else ' + '''' + ' ' + '''' + ' end,' +
'PLOG = case when type = ' + '''' + 'viewAccountPassword' + 'then Substring(dbo.fnReadField(description, ' + '''' + 'referenceCode' + '''' + '), 1, 50) else ' + ' ' + ' end,' +
'StartDate, ' +
'dbo.fnReadField(descriptio...........
The problem is: it is very difficult to do this conversion, because I need to convert all the single quotes which is a very cumbersome job -- I am very reluctant to do this manually and one letter by one letter.
Since this is a very common scenario, I am wondering if there is any existing function to do this job? or at least some easier way?
Thanks.
June 22, 2010 at 3:22 pm
Is there some reason you can't use an editor to replace all single quotes with two single quotes?
June 22, 2010 at 4:05 pm
I second Michael.
And additionally, why don't you use STRG+H in SSMS and replace every single quotation mark with a double in the required section?
As a side note:
dbo.fnReadField(description, ' + '''' + 'TargetServer.hostName' + '''' + ')
can be rewritten as
dbo.fnReadField(description, ''TargetServer.hostName'').
Furthermore, you don't need to add the blank when checking for NULL on a string concatenation:
case when u3.firstname + ' ' + u3.lastname is not null
is equal to
case when u3.firstname + u3.lastname is not null
I'm also concerned regarding the heavy use of functions, especially dbo.fnReadField(). From the distance it looks like a performance killer...
It might be a good decision to get a consultant in to have a look at the overall process. There seems to be more room for improvement than just the dynamic SQL...
June 23, 2010 at 8:31 am
Thank you guys for replying me, I have the same concern about the function, I don't know if/how to improve it because for each record in the query result, I need to use the function to calculate the different field's value. I can't do it in advance, right?
As to use double quotes to replace single quote, I don't think that would be recognized as string by SQL. Am I missing any point here?
June 24, 2010 at 7:36 am
I don't think anyone said replace single quotes with double quotes.
You need to replace the single quotes with two single quotes.
June 24, 2010 at 8:04 am
Why not convert it into a view?
You could also resolve out all the UDF's to improve performance - since you'd be left with a single code chunk.
You could also remove redundant chunks of it: the output list contains
FCID = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'TargetAccount.userName'), 1, 50) else '' end,
PLOG = case when type = 'viewAccountPassword' then Substring(dbo.fnReadField(description, 'referenceCode'), 1, 50) else '' end, ...
and there's a filter for type = 'viewAccountPassword'.
Also, replace
Manager = case
when u3.firstname + ' ' + u3.lastname is not null then u3.firstname + ' ' + u3.lastname
else 'Unknown' end, ...
with
Manager = ISNULL(u3.firstname + ' ' + u3.lastname, 'Unknown'), ...
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 24, 2010 at 8:26 am
Thank you Chris, I didn't notice the redundancy.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply