January 10, 2018 at 8:32 am
Dear All,
I have below stored procedure.
IF I execute it passing just one DB name, it works properly : exec uspReturnSPPerformance 'dwhCore', '01.01.2018', '10.01.2018'
IF I execute it passing just two or more DB names, it does NOT work properly : exec uspReturnSPPerformance 'dwhCore; dwhStaging', '01.01.2018', '10.01.2018'
This is because of the SELECT
[d].[name]
FROM
[sys].[databases] AS [d]
WHERE
[d].[name] in (@databases)
ORDER BY
Can you please help?
Thank you
alter PROCEDURE uspReturnSPPerformance
(@databases varchar(2000), @startdate VARCHAR(10), @enddate VARCHAR(10))
AS
Declare @StrSQL varchar (2000), @DB Varchar(1000)
set @databases = '''' + replace(replace(@databases, ';',''','''),' ','') + ''''
BEGIN
if exists (select * from sys.objects where name ='#ReturnSPNames')
Begin
Drop table #ReturnSPNames
end
create table #ReturnSPNames
(
NumberExecutions bigint,
StoredProcedure varchar(200),
DataChanges bigint,
TExecutionTimeSec int,
DatabaseName Varchar(200)
)
print @databases
DECLARE UserDBs CURSOR FOR
SELECT
[d].[name]
FROM
[sys].[databases] AS [d]
WHERE
[d].[name] in (@databases)
ORDER BY
[d].[name]
OPEN [UserDBs]
FETCH NEXT FROM [UserDBs] INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
set @StrSQL = ('
insert into #ReturnSPNames
select top 10
COUNT(*) NumberExecutions,
execsp.schemaname + '' - '' + execsp.SPname StoredProcedure,
(sum(rll.rowsinserted) + sum(rll.rowsdeleted) + sum(rll.rowsupdated)) DataChanges,
avg(DATEDIFF(second, execsp.[StartDateTime], execsp.[EndDateTime])) TExecutiontimeSec,
databasename
from
' + @DB + ' .ETL.vwRowLoadLog rll
INNER JOIN
' + @DB + '.Logging.SPExecutions execsp
ON
execsp.ExecutionID=rll.ExecutionID
AND
convert( date, execsp.StartDateTime,103) >= convert( date, ''' + @StartDate + ''' , 103)
AND
convert (date, execsp.enddatetime,103) <= convert (date, ''' + @EndDate + ''', 103)
GROUP BY
databasename, execsp.schemaname + '' - '' + execsp.SPname
order by texecutiontimesec desc
')
EXEC (@StrSQL)
FETCH NEXT FROM [UserDBs] INTO @DB
END
CLOSE [UserDBs];
DEALLOCATE [UserDBs];
select * from #ReturnSPNames
if exists (select * from sys.objects where name ='#ReturnSPNames')
Begin
Drop table #ReturnSPNames
end
END
January 10, 2018 at 8:48 am
IN (@Variable) is equivalent to = @Variable.
If you want multiple values in a string, you need dynamic SQL (strongly not recommended due to the security implications) or a string splitting function like DelimitedSplit8k to turn your string into a resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2018 at 8:51 am
If you need to pass multiple values in a parameter, look at using a table valued parameter.
If you pass a parameter as you have done, SQL still sees it as a single string value, it won't automatically split your string.
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
January 22, 2018 at 3:06 am
use fnsplit tabular function . with ';' and a separated parameter.
----------------------------------------------------------------------------------------------
ALTER Function [dbo].[fnSplit]
(@pString VARCHAR(max),@pSplitChar CHAR(1))
RETURNS @tblTemp TABLE (tid INT,value VARCHAR(1000))
as
begin
declare @vStartPositionint
declare @vSplitPositionint
declare @vSplitValuevarchar(1000)
declare @vCounterint
set @vCounter=1
select @vStartPosition = 1,@vSplitPosition=0
set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
if (@vSplitPosition=0 and len(@pString) != 0)
begin
INSERT INTO @tblTemp
(
tid,
value
)
VALUES
(
1,
@pString
)
return--------------------------------------------------------------->>
end
set @pString=@pString+@pSplitChar
while (@vSplitPosition > 0 )
begin
set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )
set @vSplitValue = ltrim(rtrim(@vSplitValue))
INSERT INTO @tblTemp
(
tid,
value
)
VALUES
(
@vCounter,
@vSplitValue
)
set @vCounter=@vCounter+1
set @vStartPosition = @vSplitPosition + 1
set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
end
return
end
-------------------------------------------------------
January 22, 2018 at 4:08 am
Asim Yousaf - Monday, January 22, 2018 3:06 AMuse fnsplit tabular function . with ';' and a separated parameter.
<<snip>>
The "house" splitter is likely to perform much better:
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
January 22, 2018 at 7:19 am
ChrisM@Work - Monday, January 22, 2018 4:08 AMAsim Yousaf - Monday, January 22, 2018 3:06 AMuse fnsplit tabular function . with ';' and a separated parameter.
<<snip>>The "house" splitter is likely to perform much better:
It isn't likely to perform better. It will blow the doors out of that loop based splitter. This is like comparing a Yugo to a Porsche.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply