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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy