Get Default Values of Parameters of Stored Procedures

If you are dealing and quering on the
schema of your database, you probably need this script;

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--
=============================================

-- Author:        Atif

-- Create date:24-May-2010

-- Description:    Get Default Value of a Parameter of SP or Function

--
=============================================

ALTER FUNCTION [dbo].[fnGetParameterDefaultValue]

(    

    @pSPName varchar(1000)='',

    @pParameterName varchar(100),

    @pDataType varchar(100),

    @pType bit=0 --0 for Stored Procedure and 1 for Function

)

RETURNS varchar(1000)

AS

BEGIN    

    Declare @pOutPut varchar(1000)

    Declare @vStartPosition int

    Declare @vStartPosition2 int    

    Declare @vStartPosition3 int

    Declare @vStartPosition4 int

    Declare @vStartPosition5 int

    Declare @vSPText varchar(max)

    Declare @vSPText2 varchar(max)

-- Get the text form syscomments (first 4000 characters if length is > 4000)

    if @pType = 0

    begin

        SELECT @vSPtext = (SELECT text FROM syscomments

         WHERE id = object_id(@pSPName) and colid=1 and number = 1)

        Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000)

    end

    else

        SELECT @vSPtext = (SELECT text FROM syscomments

         WHERE id = object_id(@pSPName) and colid=1 and number = 0)

      Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE FUNCTION',@vSPtext),4000)

    if IsNull(@vSPtext,'') = ''

    begin

        -- Exit if SP Name Not found in syscomments....

        Select @pOutPut = ''

        RETURN @pOutPut

    end 

    Set @pOutPut = ''
    While 1=1
    Begin
        -- Get the position of the parameter definition.
        Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText)
        -- Check if parameter exists
        if @vStartPosition > 0
        begin
            -- Get the Definition String
            select @vSPText=RIGHT(@vSPText, DataLength(@vSPText)-(@vStartPosition -1))

            -- Get the string breaker

            if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0)

                or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText)

      > 0)
            begin                
                Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1

                Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1

                Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1

                if @vStartPosition <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition = 10000000

                if @vStartPosition2 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition2 = 10000000

                if @vStartPosition3 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition3 = 10000000

                if @vStartPosition4 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition4 = 10000000

                if @vStartPosition5 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)

                    Set @vStartPosition5 = 10000000

                Select Top 1 @vStartPosition = [value]
                from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10))
                                + ',' + Cast(@vStartPosition3 as varchar(10))

                                + ',' + Cast(@vStartPosition4 as varchar(10))

                                + ',' + Cast(@vStartPosition5 as varchar(10)) ,',')    

                order by Cast([value] as int)

            end

            else

            begin

                -- SP text must atleast have AS to break the parameter definition string

                Set @vStartPosition = CharIndex('AS',@vSPText) - 1

            end             

            -- Get the specific Definition String

            Set @vSPText2 = Left(@vSPText,@vStartPosition)

            -- Check if you got the right one by data type...            

            if CharIndex(@pDataType,@vSPText2) > 0

            begin

                --Select 'IN'

                --Select @text2

                if CharIndex('=',@vSPText2) > 0

                begin

                    -- check the default value

                    Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2))

                    -- We have default value assigned here

                if Right(@pOutPut,1) = ','

                        Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1)

                end

                else

                begin

                    --Set @pOutPut = 'No Default Value Defined...'

                    -- We DO NOT have default value assigned here

                    Set @pOutPut = ''

                end

                --No need to work further with this parameter

                BREAK

            end

            else

            begin   

                --Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000)

                -- Cut the SP text short and loop again

                Set @vSPText = SubString(@vSPText,@vStartPosition,4000)

            end

            -- This should never be the case. Just a check....

            if Datalength(@vSPText) < Datalength(@pParameterName)

                Break

        end

        else

        begin

            --Set @pOutPut = 'Parameter Not Found...'

            -- Wrong parameter search...

            Set @pOutPut = ''

            Break

        end

    End

    Select @pOutPut = rtrim(ltrim(@pOutPut))

    RETURN @pOutPut

END

To use this function, here is the proper way;

Select a.[name],b.[name] ,dbo.fnGetParameterDefaultValue('[dbo].[YourSPName]',a.[name],b.[name],0) as defaultVal

from sys.parameters a

inner join sys.types b on b.system_type_id = a.system_type_id

where Object_id = object_id('[dbo].[YourSPName]')

Trim Non-Alpha characters from string

You want to trim non-alpha characters from your string, here is the function. This function takes a string parameter and returns a string with only alpha characters.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Atif Sheikh
-- Create date: 28-05-2010
-- Description: Trim Non Alpha Characters
-- =============================================
--Select dbo.fnTrimNonAlphaCharacters('Atif Sheikh')
ALTER FUNCTION [dbo].[fnTrimNonAlphaCharacters]
(
@pString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
Declare @vTable Table (Ch Char(1))
Declare @vRetString varchar(max)

Insert Into @vTable
SELECT SUBSTRING(@pString,N,1)
FROM dbo.tblTally a
WHERE N <= LEN(@pString) ORDER BY N

Set @vRetString = ''

Select @vRetString = @vRetString + Ch
from @vTable
Where Ascii(Ch) between 97 and 122
Or Ascii(Ch) between 65 and 90
Or Ascii(Ch) = 32

Return @vRetString

-- Select Ascii(' ')
END

PIVOT with optional number of columns.

I had this scenario from one of my developer. We needed to Pivot the columns according to the filter passed by the user on front end. If he passes filter that gives 3 rows, then only three columns to be in PIVOT table. if 2 then t columns and so on.

This is the code i wrote to accomplish the task;



If Not Exists(Select * from sys.objects where [name] = 'tblTable3')
begin
Create Table tblTable3 (ID int, ID2 int,txt varchar(max))

Insert into tblTable3
Select 1,10,'1 - 10' Union All
Select 1,20,'1 - 20' Union All
Select 1,30,'1 - 30' Union All
Select 1,40,'1 - 40' Union All
Select 2,20,'2 - 20' Union All
Select 2,40,'2 - 40' Union All
Select 3,50,'3 - 50'
end

Declare @vCols varchar(Max)
Declare @vPCols varchar(Max)
Declare @vQuery varchar(max)

Declare @vFilterID int
Declare @vFilter varchar(max)

Set @vFilterID = 1 --We need to discuss how this filter will be applied...
Set @vFilter = ' where ID = ' + Cast(@vFilterID as varchar(10))

Set @vPCols = ''
Set @vCols = ''

Select @vCols = @vCols + ',IsNull([' + Cast(ID2 as varchar(10)) + '],'''') as [' + Cast(ID2 as varchar(10)) + ']'
,@vPCols = @vPCols + ',[' + Cast(ID2 as varchar(10)) + ']'
from tblTable3
where ID = @vFilterID

--Select * from tblTable3

Set @vCols = Stuff(@vCols,1,1,'')
Set @vPCols = Stuff(@vPCols,1,1,'')

Set @vQuery = 'Select ID,' + @vCols + '
From (Select * from tblTable3 ' + @vFilter + ') Data
PIVOT (Max(Txt) For ID2 in ('+ @vPCols+')) p '

Print @vQuery
Exec (@vQuery)


Drop Table tblTable3

Script to check Blocked processes with relevant detail

Here is the script to check the blocked processes with detail;

Declare @vTable table(SPID int,[Status] nvarchar(256),
Login nvarchar(256),HostName nvarchar(256),
BlkBy nvarchar(256),DBName nvarchar(256),
Command nvarchar(max),CPUTime int
,DiskIO int,LastBatch nvarchar(256),
ProgramName nvarchar(256),SPID1 int,
REQUESTID int)

Insert into @vTable
Exec sp_who2

Select SPID,Status,HostName,BlkBy as BlockBy, DBName, Command, CPUTime,DiskIO, ProgramName,objectid,text
from (Select a.sql_handle,b.* from sys.dm_exec_requests a
Inner Join @vTable b on b.SPID = a.session_ID) Main
cross apply sys.dm_exec_sql_text (Main.sql_handle)
where BlkBy <> ' .'

Stored Procedure and Functions Encryption

For the last couple of weeks, I was thinking about finding a solution to encrypt the stored procedures of my database in SQL Server 2005. The reason behind this goal is that normally our stored procedures contain complex business logic. This business logic is the intellectual property of the company and it is our job to keep it safe and protected.
There are many third party solutions and ‘WITH ENCRYPTION’ in SQL Server 2005 but the encrypted code is readable with the help of any free Decoders available on the internet. We needed a better solution to keep the code encrypted yet executable.
After searching through the internet, I came up with an idea ‘Why not to create SP at runtime and drop it after executing?’ I got this idea by the post of someone in one of the SQL Server Forums on this subject. And I feel that the forum poster was not serious about the idea.
But I thought why not to give it a try.

Core Logic

The logic is very simple. I decided to keep the actual code of the stored procedure in a table. This table will have the following columns;

a. tID int
b. vSQL varchar
c. SQL_Encrypted varbinary
d. SNO tinyint

ID will be used to refer and execute the actual code.

Explanation

In the following steps, I will tell you how I performed this task;

Step 1:

Create a table tblSQLTable. This table will be encrypted. Here is the CREATE TABLE statement for tblSQLTable;

if Not Exists(Select * from sys.objects where name = 'tblSQLTable' and Type = 'U')
begin
CREATE TABLE [dbo].[tblSQLTable](
[tID] [int] NOT NULL,
[SNO] [int] NOT NULL,
[vSQL] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SQL_Encrypted] [varbinary](max) NULL,
CONSTRAINT [PK_tblSQLTable] PRIMARY KEY CLUSTERED
(
[tID] ASC,
[SNO] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

end
else
begin
Delete from tblSQLTable
end

The column vSQL will have the original text of the stored procedure. SQL_Encrypted will contain the encrypted text of the stored procedure. Initially, I will work with vSQL column to explain my point. At last, I will discuss usage and implementation of SQL_Encrypted column.

Step 2:

Open the stored procedure that you want to encrypt. To insert the code in the table we will have to delete the SET statements at the top of each stored Procedure. After this, replace the single quote with two single quotes. Replace ALTER PROCEDURE with CREATE PROCEDURE and add # operator at the beginning of the stored procedure name. Finally, create a simple Insert statement for tblSQLTable against the respective stored procedure code.
Actual Stored procedure;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: Atif Sheikh
-- Create date: 18-May-2010
-- Description: Sp to be Encrypted
-- =============================================



ALTER PROCEDURE sp_who123
@pInt int,
@pVarchar varchar(10)
AS
BEGIN
SET NOCOUNT ON;
Select @pInt, @pVarchar
PRINT 'SP Executed successfully…'
END

Insert statement of stored procedure;
Insert into tblSQLTable (tID,vSQL)
Select 2,'
CREATE PROCEDURE #sp_who123
@pInt int,
@pVarchar varchar(10)
With Encryption
AS
BEGIN
SET NOCOUNT ON;
Select @pInt, @pVarchar
Print ‘'SP Executed successfully…’'
END '

I have added 'WITH ENCRYPTION' in the above statement so that the code may not be visible in SQL Profiler at the time of execution.

Step 3:

Now that we have inserted a record in tblSQLTable with the actual stored procedure code, now is the time to execute it. Replace the code of stored procedure with the following code;

ALTER PROCEDURE [dbo].[sp_who123]
@pInt int,
@pVarchar varchar(Max)
AS
BEGIN
SET NOCOUNT ON;

Declare @vSql varchar(max)
Select @vSql = vSQL
from dbo. tblSQLTable
Where tID = 1


Exec(@vSql)
exec [dbo].[#sp_who123] @pInt,@pVarchar
Drop Procedure [dbo].[#sp_who123]
END

Important thing to note is that the parameters are the same. Application will call this stored procedure normally. The body of the stored procedure is changed. We are retrieving the record from tblSQLTable according to its respective ID. Create the stored procedure from tblSQLTable with EXEC statement. As the name of stored procedure is starting with # sign in the tblSQLTable, it will be created in the session. Execute the newly created stored procedure with # sign with the same parameters and finally, drop it. That’s it.

Step 4:

Now the question rises that still the code can be seen in the tblSQLTable. Answer is that we will encrypt the table column having code of the stored procedures. I applied encryption to tblSQLTable as;

Create Master Key;

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

Create Certificate;

CREATE CERTIFICATE sp_who123_SPs
WITH SUBJECT = 'sp_who123 Stored Procedures';
GO

CREATE SYMMETRIC KEY StoredProcedures_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE sp_who123_SPs;
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY StoredProcedures_Key11
DECRYPTION BY CERTIFICATE sp_who123_SPs;


Now updating the SQL_Encrypted column;

UPDATE tblSQLTable
SET SQL_Encrypted = EncryptByKey(Key_GUID('StoredProcedures_Key11')
, vSQL, 1, HashBytes('SHA1', CONVERT( varbinary(max), tID)));
GO

And finally, check the SQL_Encrypted column;

SELECT vSQL, SQL_Encrypted
AS 'Encrypted SQL',
CONVERT(varchar(max),
DecryptByKey(SQL_Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary(max), tID)))
)
AS 'Decrypted SQL' FROM tblSQLTable;
GO

The above query should give the following result;

Now use the SQL_Encrypted column in the actual stored procedure (as the vSQL column containing actual text should be deleted and should not be there.) After applying encryption to the tblSQLTable, the actual stored procedure will look like this;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: Atif Sheikh
-- Create date: 12-May-2010
-- Description: Encryption SP
-- =============================================

ALTER PROCEDURE [dbo].[sp_who123]
@pInt int,
@pVarchar varchar(Max)
AS
BEGIN
SET NOCOUNT ON;


Select @vSql = CONVERT(varchar(MAx),
DecryptByKey(SQL_Encrypted, 1 ,
HashBytes( ‘SHA1’, CONVERT(varbinary(Max), 1))))
from dbo.tblSQLTable
Where tID = 1

Exec(@vSql)
exec [dbo].[#sp_who123] @pInt,@pVarchar
Drop Procedure [dbo].[#sp_who123]

END

The Key to open the Key will be executed when the connection is made.

To check and call the stored procedure from the query window, here is the code;

-- This will be done in application soon after connection is made
OPEN SYMMETRIC KEY StoredProcedures_Key11 DECRYPTION BY CERTIFICATE sp_who123_SPs;
----------------------------------
exec [dbo].[sp_who123] 2,'Atif'

The above statements will be passed from the application normally.
To Add All Stored Procedures on the Fly
Now I will discuss to add all stored procedures. First Create Certificate and Symmetric Keys. Then execute the given code. Remember to take backup of the database. The function to get the parameters of stored procedure with Default values is;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author: Atif
-- Create date: 24-May-2010
-- Description: Get Default Value of a Parameter
-- =============================================
ALTER FUNCTION [dbo].[fnGetParameterDefaultValue]
(
@pSPName varchar(1000),
@pParameterName varchar(100),
@pDataType varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @pOutPut varchar(1000)
Declare @vStartPosition int
Declare @vStartPosition2 int
Declare @vStartPosition3 int
Declare @vStartPosition4 int
Declare @vStartPosition5 int
Declare @vSPText varchar(max)
Declare @vSPText2 varchar(max)


-- Get the text fro syscomments (first 4000 characters if length of SP is > 4000)
SELECT @vSPtext = (SELECT text FROM syscomments
WHERE id = object_id(@pSPName) and colid=1 and number = 1)

Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000)

if IsNull(@vSPtext,'') = ''
begin
-- Exit if SP Name Not found in syscomments....
Select @pOutPut = ''
RETURN @pOutPut
end


Set @pOutPut = ''

While 1=1
Begin
-- Get the position of the parameter definition.
Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText)
-- Check if parameter exists
if @vStartPosition > 0
begin
-- Get the Definition String
select @vSPText = RIGHT ( @vSPText, DataLength(@vSPText)-(@vStartPosition -1))

-- Get the string breaker
if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0)
or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText) > 0)
begin
Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1
Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1
Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1
Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1
Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1

if @vStartPosition <= Len(@pParameterName) Set @vStartPosition = 10000000 if @vStartPosition2 <= Len(@pParameterName) Set @vStartPosition2 = 10000000 if @vStartPosition3 <= Len(@pParameterName) Set @vStartPosition3 = 10000000 if @vStartPosition4 <= Len(@pParameterName) Set @vStartPosition4 = 10000000 if @vStartPosition5 <= Len(@pParameterName) Set @vStartPosition5 = 10000000 Select Top 1 @vStartPosition = [value] from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10)) + ',' + Cast(@vStartPosition3 as varchar(10)) + ',' + Cast(@vStartPosition4 as varchar(10)) + ',' + Cast(@vStartPosition5 as varchar(10)) ,',') order by Cast([value] as int) end else begin -- SP text must atleast have AS to break the parameter definition string Set @vStartPosition = CharIndex('AS',@vSPText) - 1 end -- Get the specific Definition String Set @vSPText2 = Left(@vSPText,@vStartPosition) -- Check if you got the right one by data type... if CharIndex(@pDataType,@vSPText2) > 0
begin
--Select 'IN'
--Select @text2
if CharIndex('=',@vSPText2) > 0
begin
-- check the default value
Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2))
-- We have default value assigned here

if Right(@pOutPut,1) = ','
Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1)
end
else
begin
--Set @pOutPut = 'No Default Value Defined...'
-- We DO NOT have default value assigned here
Set @pOutPut = ''
end
--No need to work further with this parameter
BREAK
end
else
begin
--Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000)
-- Cut the SP text short and loop again
Set @vSPText = SubString(@vSPText,@vStartPosition,4000)
end
-- This should never be the case. Just a check....
if Datalength(@vSPText) < poutput =" 'Parameter" poutput =" ''" poutput =" rtrim(ltrim(@pOutPut))" object_id =" a.tid" schema_id =" b.schema_id" tid =" 1721577717" fetch_status =" 0" valterparams =" ''" vactualalterparams =" ''" valtersql =" ''" valtersql2 =" ''" vdeclarestr =" ''" vaddsql =" ''" valterparams =" @vAlterParams" max_length =" -1" is_output =" 1" vactualalterparams =" @vActualAlterParams" is_output =" 1" system_type_id =" a.system_type_id" object_id =" @vtID"> 'sysname'
and b.name <> 'FloatPercentage'
order by parameter_id

Set @vAlterParams = Stuff(@vAlterParams,1,1,'')
Set @vActualAlterParams = Stuff(@vActualAlterParams,1,1,'')

Set @vAlterSQL = ' ALTER PROCEDURE ' + @voName + ' ' + @vAlterParams
+ ' AS
BEGIN
SET NOCOUNT ON;

Declare @vSql varchar(max)

Set @vSql = ''''
'

Select @vAlterSQL2 = @vAlterSQL2 + '
Select @vSql' + Cast(SNO as varchar(10)) + ' = CONVERT(varchar(MAX),
DecryptByKey(SQL_Encrypted, 1 ,
HashBytes( ''SHA1'', CONVERT(varbinary(Max), tID))))
from dbo.tblSQLTable
Where tID = ' + Cast(@vtID as varchar(100)) + '
And SNO = ' + Cast(SNO as Varchar(10))
,@vDeclareStr = @vDeclareStr + 'Declare @vSQL' + Cast(SNO as Varchar(10)) + ' varchar(MAX) ' + Char(10) + Char(13)
,@vAddSQL = @vAddSQL + '+ @vSQL' + Cast(SNO as Varchar(10))
From tblSQLTable
where tID = @vtID

Set @vAddSQL = '
Set @vSQL = ' + Stuff(@vAddSQL,1,1,'')

Set @vAlterSQL = @vAlterSQL + @vDeclareStr + @vAlterSQL2 + @vAddSQL + '
print @vSql
Exec(@vSql)
exec ' + @voName2 + ' ' + @vActualAlterParams + '
Drop Procedure ' + @voName2 + '
END'

print @vDeclareStr
print @vAlterSQL2
print @vAddSQL

Exec (@vAlterSQL)
Set @vAlterSQL = ''
Fetch Next from C1 into @vtID,@voName,@voName2
end
Deallocate C1

commit

I hope you will not get any error in the above code. I tested on 3 databases of different domains and it was working fine. After executing the above code, you can delete the column vSQL from tblSQLTable.

I will not discuss Encryption here in detail. Just one thing to mention is that the EncryptByKey returns varbinary with a maximum size of 8000 bytes.
I hope this will help someone who is on SQL Server 2005 and looking for encryption of stored procedures. The same pattern can be applied on functions but I have not tested it yet on functions.

I know that there are some performance issues like possible Temp DB contention in this solution. I will be expecting a lot of suggestions and comments in this regard so that we together can make this solution more workable and efficient.