SQL Server - Stored procedure get back?

Asked By farrukh on 14-Apr-12 02:03 AM
Hi All,

I really need some ugrent help. i have written a stored procedure then i alter it and execute then close the sql server as well it is possible that i can get my previous stored procedure ???

Thanks
hammeed
Suchit shah replied to farrukh on 14-Apr-12 02:21 AM
There is no direct way in sql server with that you can be able to retrieve your previous version of stored procedure.

The only way to get that stored procedure back is that if you have taken backup of this thing previously then get it from backup otherwise there is no way to retrieve old stored procedure once you alter it 
farrukh replied to Suchit shah on 14-Apr-12 02:40 AM
Suchit shah,

AH ! 


Thanks you for replying



hammeed
Anoop S replied to farrukh on 14-Apr-12 02:53 AM
Are you use IsEncrypted property while creating table?

IsEncrypted -> Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time.

1 = Encrypted

0 = Not encrypted
farrukh replied to Anoop S on 14-Apr-12 04:44 AM

IAM CREATING TABLE LIKE THAT ...   PLEASE CHECK AND ADVISE.



USE [TEST]

GO

/****** Object: Table [dbo].[ITEM_EVENT] Script Date: 04/14/2012 13:40:32 ******/

SET ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

SET

ANSI_PADDING ON

GO

CREATE

TABLE [dbo].[ITEM_EVENT](

[EVENT_GROUP_ID] [char](32) NULL,

[ITEM_ID] [char](32) NOT NULL,

[EVENT_TYPE] [varchar](14) NOT NULL,

[PERIOD] [varchar](20) NOT NULL,

[START_DATETIME] [datetime] NOT NULL,

[END_DATETIME] [datetime] NULL,

[VAL1] [numeric](28, 12) NULL,

[VAL2] [numeric](28, 12) NULL,

[VAL3] [numeric](28, 12) NULL,

[VAL4] [numeric](28, 12) NULL,

CONSTRAINT [PK_ITEM_EVENT] PRIMARY KEY CLUSTERED

(

[EVENT_TYPE] ASC,

[ITEM_ID] ASC,

[START_DATETIME] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

) ON [PRIMARY]

GO

SET

ANSI_PADDING OFF

GO

ALTER

TABLE [dbo].[ITEM_EVENT] ADD CONSTRAINT [DF_ITEM_EVENT_LAST_UPDT_DATE] DEFAULT (getdate()) FOR [LAST_UPDT_DATE]

GO

ALTER

TABLE [dbo].[ITEM_EVENT] ADD CONSTRAINT [DF_ITEM_EVENT_VERSION] DEFAULT (replace(CONVERT([varchar](50),newid(),0),'-','')) FOR [VERSION]

GO

ALTER

TABLE [dbo].[ITEM_EVENT] ADD CONSTRAINT [DF_ITEM_EVENT_ROW_ID] DEFAULT (replace(CONVERT([varchar](50),newid(),0),'-','')) FOR [ROW_ID]

GO

Anoop S replied to farrukh on 14-Apr-12 05:53 AM
Encryption is an optional parameter only, if you want create it with encryption then general  syntax will be

--Transact-SQL Stored Procedure Syntax
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
  [ { @parameter [ type_schema_name. ] data_type } 
    [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
  ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
  [ ENCRYPTION ]
  [ RECOMPILE ]
  [ EXECUTE AS Clause ]

Refer this for more details
http://msdn.microsoft.com/en-us/library/ms176105.aspx
http://msdn.microsoft.com/en-us/library/ms189762.aspx
dipa ahuja replied to farrukh on 14-Apr-12 05:59 AM
Once you have saved it , you cannot get the previous code back.
kalpana aparnathi replied to farrukh on 14-Apr-12 02:29 PM
hi,

After saving record in the database ,you are not able to exceute previous code but one silly idea for that use varibles to store and after that save it to the database.

Regards,
farrukh replied to kalpana aparnathi on 14-Apr-12 02:45 PM
Anoop,Kalpana and dipa,


Thank you for your advises an comments , the main thing i should remember always take backup while execting stored proc or use encrypted in the proc.


Thank you all

regards,
Somesh Yadav replied to farrukh on 16-Apr-12 01:13 AM

Check your full code of SP, may be somewhere there is a RETURN statement, so - the SP will not return any data (pay attention on IF SomeContition IS TRUE RETURN):

CREATE PROCEDURE [dbo].[GetMetaData]
    @@Assembly Sql_Variant = NULL,
    @@Namespace Varchar(30) = NULL,
    @@ParameterName Varchar(40) = NULL,
    @@Standard Bit = 0,
    @@Timestamp DateTime = NULL
AS

    SET NOCOUNT ON

    DECLARE @ResultTable TABLE (AssemblyId Int, Namespace Varchar(30), ParameterName Varchar(40), Value Varchar(100))

    -- Does loads of stuff and then inserts into @ResultTable table...

    IF SomeContition IS TRUE RETURN

    SET NOCOUNT OFF

    -- Return the result
    SELECT AssemblyId, Namespace, ParameterName, [Value]
        FROM @ResultTable

    RETURN