You can replace LEFT by OUTER APPLY.
Query:
SELECT [rs].[ID]
, [rs].[event1]
, [Exists] = CASE WHEN [bph].[uuid] IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM #robschneider rs
OUTER APPLY (
SELECT TOP(1) [uuid]
FROM #bigponyhorses
WHERE [empid] = @empid
AND [rs].[uuid] = [uuid]
) [bph]
Or:
SELECT [rs].[ID]
, [rs].[event1]
, [Exists] = COALESCE([bph].[Exists], 'No')
FROM #robschneider rs
OUTER APPLY (
SELECT TOP(1) [Exists] = 'Yes'
FROM #bigponyhorses
WHERE [empid] = @empid
AND [rs].[uuid] = [uuid]
) [bph]
Or:
SELECT [rs].[ID]
, [rs].[event1]
, [Exists] = COALESCE(
(
SELECT TOP(1) [Exists] = 'Yes'
FROM #bigponyhorses
WHERE [empid] = @empid
AND [rs].[uuid] = [uuid]
)
, 'No')
FROM #robschneider rs
Output:
ID event1 Exists
1 cycle Yes
2 run Yes
3 walk No
Try this and let me know.
I added one more cte selecting from recurse to add a ROW_NUMBER() over (partition by depentent_type order by depth, row) as rn_type and added at the bottom WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
--==================================================================
--==================================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;WITH stored_procedures AS (
SELECT
o.object_id as proc_id
,oo.object_id as depentent_id
,SCHEMA_NAME(O.schema_id) + '.' + o.name AS proc_name
,o.type_desc AS proc_type
,depentent_Name =SCHEMA_NAME(OO.schema_id) + '.' + oo.name
,[depentent_type]=oo.type_desc
,ROW_NUMBER() OVER(PARTITION BY o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sys.sql_dependencies d
INNER JOIN sys.objects o ON o.object_id=d.object_id
INNER JOIN sys.objects oo ON oo.object_id=d.referenced_major_id
--WHERE 1 = CASE WHEN @SPID = 0 THEN 1
-- ELSE CASE WHEN @spid = o.object_id
-- THEN 1
--ELSE 0
-- END
-- END
--==================================================================================
-- THE OBJECT TYPES
--AF = Aggregate function (CLR)
--C = CHECK constraint
--D = DEFAULT (constraint or stand-alone)
--F = FOREIGN KEY constraint
--FN = SQL scalar function
--FS = Assembly (CLR) scalar-function
--FT = Assembly (CLR) table-valued function
--IF = SQL inline table-valued function
--IT = Internal table
--P = SQL Stored Procedure
--PC = Assembly (CLR) stored-procedure
--PG = Plan guide
--PK = PRIMARY KEY constraint
--R = Rule (old-style, stand-alone)
--RF = Replication-filter-procedure
--S = System base table
--SN = Synonym
--SO = Sequence object
--==================================================================================
)
--select *
--from stored_procedures sps
--where sps.proc_id = 20351287
--and ROW = 1
, recurse
as (
SELECT depth = 1
,sps.*
,THE_ORDER= CASE WHEN sps.depentent_type='USER_TABLE' THEN '' ELSE sps.depentent_type END
FROM stored_procedures sps
WHERE sps.proc_id = 858642302 -- <-- use this to filter results.
AND sps.row = 1
UNION ALL
SELECT depth = R.depth + 1
,sps.*
,THE_ORDER= CASE WHEN sps.depentent_type='USER_TABLE' THEN '' ELSE sps.depentent_type END
FROM stored_procedures sps
INNER JOIN recurse R
ON SPS.proc_id = R.depentent_id
AND sps.row = 1
)
,RecurseRowNumberPartitionedByDepententType as
(
SELECT r.proc_id
,r.proc_name
,r.proc_type
,[Parameters] = STUFF( ( SELECT ','+
CASE WHEN p.parameter_id = 0 THEN 'RETURNS' ELSE p.name
END -- this is the return value
+
' '
+
UPPER(TYPE_NAME(p.user_type_id))
+
CASE UPPER(TYPE_NAME(p.user_type_id))
WHEN 'CHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length AS VARCHAR) END + ')'
WHEN 'DECIMAL' THEN '(' + CAST (p.precision AS VARCHAR) + ',' + CAST (p.scale AS VARCHAR) + ')'
WHEN 'NUMERIC' THEN '(' + CAST (p.precision AS VARCHAR) + ',' + CAST (p.scale AS VARCHAR) + ')'
WHEN 'NCHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length/2 AS VARCHAR) END + ')'
WHEN 'NVARCHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length/2 AS VARCHAR) END + ')'
WHEN 'VARCHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length AS VARCHAR) END + ')'
WHEN 'VARBINARY' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length AS VARCHAR) END + ')'
WHEN 'XML' THEN ''
ELSE ''
END
+
CASE WHEN is_output = 1 THEN ' OUTPUT' ELSE '' END
FROM sys.parameters p
WHERE r.proc_id = p.object_id
ORDER BY p.parameter_id FOR XML PATH('')),1 ,1, '')
,r.depentent_id
,R.depentent_Name
,R.depentent_type
,R.depth
,R.row
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE rn_type = 1
ORDER BY depth
,THE_ORDER
Best Answer
For the original requirement -- 1000 max characters, up to the last period, but there might not be any period -- the following would work:
The
LEFT(string, 1000)is used as the expression to take theLEFT(expression, some number)from because it ensures that no more than 1000 characters will be taken which would otherwise happen in the case of the string not having any periods at all. While theLEFT(expression, 1000)could have instead been wrapped around the entireLEFT(string, 1001 - ...), doing it the way shown above should allow for the expression to be reused since it also shows up exactly the same inside of theREVERSEfunction, hence a little more efficient.For the updated requirement of introducing a Carriage Return as a fall-back character to look for, you can maybe switch to using
PATINDEXas it can look for a list of characters:However, there is one final requirement added:
Unfortunately,
PATINDEXcan't distinguish which one comes first. At this point it seems like some form of conditional logic will need to be added :-(.Here I not only reused the
LEFT(TestData, 1000), but theREVERSE(LEFT(TestData, 1000))shows up 3 times, and for theIIF(which is really just shorthand forCASE WHEN x THEN y ELSE x END) I reused the entireCHARINDEX('.', REVERSE(LEFT(TestData, 1000))).The example code is posted on Pastebin.com at:
Get 1000 chars max, up to final period, else to final return
The example shows the outcome of the 3 methods shown above across 7 different test cases.