SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


t-sql trimming before and after stored proc name


t-sql trimming before and after stored proc name

Author
Message
kalpit_yellow
kalpit_yellow
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 331
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 )
natevdh
natevdh
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 357
Is there a reason you cant do
SELECT p.name FROM sys.procedures p

?
kalpit_yellow
kalpit_yellow
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 331
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
j.miner
j.miner
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 358
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
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26266 Visits: 12487
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.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26266 Visits: 12487
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.
Mansfield
Mansfield
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 394
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


j.miner
j.miner
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 358
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
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74527 Visits: 40983
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
j.miner
j.miner
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 358
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search