SQL Tips-Search string in stored procedures of SQL,Sending Email

You can write following way to Search string in stored procedures of SQL. Syscomments and sysobject are sql system table. Sysobject table stored all object of sql like stored procedure,table name etc.

declare @SearchStr      varchar(100)
set @searchstr = 'String to search for'
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name'
FROM  syscomments c
      INNER JOIN
      sysobjects o
      ON c.id = o.id
WHERE c.text LIKE '%' + @SearchStr + '%'  and
      encrypted = 0

I hope you will able to understand this SP.

Sending mail through SQL Stored proceduare

You can write following sql script in stored procedure to Send mail through SQL Stored procedure.

@SenderNm varchar(100),
@Senderemail varchar(100),
@RecipientNm varchar(100),
@Recipientemail varchar(100),
@Subject varchar(200),
@Body varchar(8000)AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
 BEGIN
  EXEC @resultcode = sp_OASetProperty @oMail, 'From', @Senderemail
  EXEC @resultcode = sp_OASetProperty @oMail, 'To', @Recipientemail
  EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
  EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
  EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
  EXEC sp_OADestroy @oMail
 END
SET nocount off