QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

QUERY

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-03-18 : 07:39:23

Hi

In stored Procedures i used one field name. If i pass procedure name and content text. i need to know how many times i used this field name inside the procedures.

For examples

I used Location is the column name in XYZ procedure.

If i passed XYZ procedure name & content text "Location"

I will get the total count.

Please help me out

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:53:03

quote:
Originally posted by WoodHouse

Hi

In stored Procedures i used one field name. If i pass procedure name and content text. i need to know how many times i used this field name inside the procedures.

For examples

I used Location is the column name in XYZ procedure.

If i passed XYZ procedure name & content text "Location"

I will get the total count.

Please help me out

i dint get any..
please post some data and expected result.

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-03-18 : 07:56:12

Create procedure Test
@Location VARCHAR(50)
as
Begin
select * from tables where location = @Location

select Location, EmpId from tables where location = @Location
End

Expected Output

So I have used three times Location column name

Output is 3

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:58:44

quote:
Originally posted by WoodHouse

Create procedure Test
@Location VARCHAR(50)
as
Begin
select * from tables where location = @Location

select Location, EmpId from tables where location = @Location
End

Expected Output

So I have used three times Location column name

Output is 3

Could you please tell me,why do you want to do this.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2010-03-18 : 08:11:25

hi

Try this.. But am not sure you will get expected result set for all scenario.


DECLARE @SPNAME VARCHAR(50)
DECLARE @TEXT VARCHAR(50)

SET @SPNAME	=	'Test'

DECLARE @Totalcounts TABLE
(
  ID  int IDENTITY(1,1),
  Texts NVARCHAR(MAx)
)

INSERT INTO @Totalcounts
EXEC SP_HELPTEXT @SPNAME


SELECT COUNT(*) FROM @Totalcounts 
WHERE CHARINDEX('Location', Texts)>0

-------------------------
[R][A][J]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-18 : 08:14:43

If its not specific for column name then below solution can work for any text search in any stored procedure.


CREATE PROC GetCount
	@procName AS VARCHAR(100), 
	@Searchtext AS VARCHAR(100)
AS
BEGIN 
	DECLARE @script AS varchar(max)

	SET @script = ''

	SELECT @script = @script + text FROM sys.syscomments c INNER JOIN sys.procedures p on c.id = p.object_id WHERE p.name = @procName

	SET @script = REPLACE(@script, @Searchtext, '~')

	SELECT len(@script) - len(replace(@script, '~', '')) AS [COUNT]

END
GO
EXEC GetCount 'PM_ticketgeneration', 'RegID'

Vaibhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-18 : 08:25:52

rajdaksha's Query will give wrong output as it is calculating for each row but there might be multiple occurence of any searchtext rows.

Vaibhav T

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2010-03-18 : 08:49:56

Yes vaibhavktiwari83. if u used two times in the same row might not work.

-------------------------
[R][A][J]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2010-03-18 : 08:52:34