Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

t-sql trimming before and after stored proc name Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 11:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 12:18 PM
Points: 7, Visits: 246
Hi,

I have requirement to get only stored proc name and trim everything before and after stored procedure name. Here is the code i tried. so output only need to show "abc_SessionValuesUpdate"

Any help will be great...thanks...

declare @col varchar(20)

select @col = 'CREATE Procedure abc_SessionValuesUpdate AS
SET NOCOUNT ON
BEGIN
UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name
IF @@ROWCOUNT = 0
INSERT INTO SessionValues values (@ID,@Name,@Value)
END '

Select substring( LEFT(@Col,charindex('abc',@Col)+1),charindex
('abc_',@Col)+1,len(LEFT(@Col,charindex('abc',@Col)-1))+1 )
Post #1440948
Posted Wednesday, April 10, 2013 11:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:43 PM
Points: 4, Visits: 191
Is there a reason you cant do
SELECT p.name FROM sys.procedures p

?
Post #1440949
Posted Wednesday, April 10, 2013 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 12:18 PM
Points: 7, Visits: 246
Thanks for the reply...actually i have following query and i want actual stored proc name instead of whole procudere on Last_executed_SQL column...


SELECT ec.session_id, connect_time,s2.[text] as Last_Executed_SQL
, auth_scheme as Auth_Type, client_net_address
,client_tcp_port, es.host_name, es.program_name, es.login_time, es.login_name
FROM sys.dm_exec_connections ec
OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2
LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)
WHERE s2.text LIKE ('%Stored procedure%')
ORDER BY connect_time desc
Post #1440952
Posted Wednesday, April 10, 2013 11:33 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:56 PM
Points: 80, Visits: 329
Hi Kalpit,

A quick solution is below.

First, your @col variable was defined to small to hold the definition. That is the main problem I see here.

I am assuming the procedure name is followed by '(' for parameters or 'as' if there are none.

Sincerely

John



-- Declare larger string variable
declare @col varchar(max);

-- Start and end position of procedure name
declare @start int;
declare @end int;

-- Set variable to dynamic code
select @col = 'CREATE Procedure abc_SessionValuesUpdate (a int) AS
SET NOCOUNT ON
BEGIN
UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name
IF @@ROWCOUNT = 0
INSERT INTO SessionValues values (@ID,@Name,@Value)
END ';

-- Get start and end of name
select @start = charindex('procedure ', @Col);
select @end = charindex('(', @Col, @start + 10);
if @end = 0
select @end = charindex('as', @Col, @start + 10);

-- Select just the proc name
select substring(@col, @start + 10, @end-@start-10);


John Miner
Crafty DBA
www.craftydba.com
Post #1440953
Posted Wednesday, April 10, 2013 8:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
It's pretty common to find strings like that in syscomments:
/*
The procedure updates values stored in table SessionValues
*/
CREATE Procedure abc_SessionValuesUpdate (a int) AS
SET NOCOUNT ON
BEGIN
UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name
IF @@ROWCOUNT = 0
INSERT INTO SessionValues values (@ID,@Name,@Value)
END

The logic of the solution above will fail for all such cases.
Post #1441089
Posted Wednesday, April 10, 2013 9:13 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
j.miner (4/10/2013)
Hi Kalpit,

A quick solution is below.


John, I copied your syntax to my reply and only after posting I realised - it's wrong.

Parameters in stored procedures do not require brackets:
CREATE Procedure abc_SessionValuesUpdate @a int AS

Not to mention - there might be no parameters, like in the initial post.

Apart from that your solution does not consider following cases:

CREATE Procedure      abc_SessionValuesUpdate @a int AS
CREATE
Procedure
abc_SessionValuesUpdate @a int AS

CREATE Procedure
--- some comment
abc_SessionValuesUpdate @a int AS

CREATE Procedure dbo.[abc_SessionValuesUpdate] @a int AS

CREATE PROC dbo.[abc_SessionValuesUpdate] @a int AS

and many-many more.

Post #1441094
Posted Wednesday, April 10, 2013 10:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:36 PM
Points: 364, Visits: 381
kalpit_yellow (4/10/2013)
Thanks for the reply...actually i have following query and i want actual stored proc name instead of whole procudere on Last_executed_SQL column...


SELECT ec.session_id, connect_time,s2.[text] as Last_Executed_SQL
, auth_scheme as Auth_Type, client_net_address
,client_tcp_port, es.host_name, es.program_name, es.login_time, es.login_name
FROM sys.dm_exec_connections ec
OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2
LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)
WHERE s2.text LIKE ('%Stored procedure%')
ORDER BY connect_time desc


You can join back to sys.objects via sys.sql_modules to get the stored procedure name. This will make the query only show running stored procedures. e.g.

SELECT	ec.session_id, 
connect_time,
s2.[text] as Last_Executed_SQL,
auth_scheme as Auth_Type,
client_net_address,
client_tcp_port,
es.host_name,
es.program_name,
es.login_time,
es.login_name,
ob.name as Stored_Procedure_Name
FROM sys.dm_exec_connections ec
OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2
LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)
JOIN sys.sql_modules sm on sm.definition = s2.text
JOIN sys.objects ob on ob.object_id = sm.object_id
ORDER BY connect_time desc

Post #1441103
Posted Friday, April 12, 2013 1:06 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:56 PM
Points: 80, Visits: 329
Hi Kalpit,

The idea was to give you a general idea about the solution.

Modify the code to your hearts content.

The code below looks for the first 'procedure' marker and then the next space afterwards.

It assumes that there is one space between the key word and procedure name.

This does not handle nested definitions.

Good luck

John



-- Get start and end of name, changed to look for second space
select @start = charindex('procedure ', @Col);
select @end = charindex(' ', @Col, @start + 10);


John Miner
Crafty DBA
www.craftydba.com
Post #1441866
Posted Friday, April 12, 2013 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
another way is to use dbo.Delimitedsplit on sys.sql_modules.defintion, so every word in the code is separate, then simply join it to the name in sys.procedures.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441878
Posted Friday, April 12, 2013 1:35 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:56 PM
Points: 80, Visits: 329
Hi Lowell,

Are you talking about the new [dbo].[DelimitedSplit8K] function Jeff Moden wrote.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Real cool stuff.

John


John Miner
Crafty DBA
www.craftydba.com
Post #1441892
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse