Store procedure defination update

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

SQL Server 2008 Forums

Transact-SQL (2008)

Store procedure defination update

Author

Topic

abbikhan
Starting Member

26 Posts

Posted - 2011-08-30 : 03:21:48

Can we update SPs' Definition either in Routine_definition (information_schema.routines) or in SysObjects through SQl command

like i want to replace 'testtable' used in SP to testtable2
and then execute it

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-30 : 06:53:54

Using SSMS, find your stored proc in the object explorer details (database, programmability, stored procedures), right click your proc and choose "Script stored proc as Alter to new window/tab". You can change it from there, run the script, and it will update your proc.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:37:14

or simply type sp_helptext 'your proc name' and it will give proc text body. copy and paste it to new window and replace CREATE with ALTER in beginning. then do your change and execute it to create modified proc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abbikhan
Starting Member

26 Posts

Posted - 2011-09-05 : 07:01:24

quote:
Originally posted by visakh16

or simply type sp_helptext 'your proc name' and it will give proc text body. copy and paste it to new window and replace CREATE with ALTER in beginning. then do your change and execute it to create modified proc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for Your reply,but in it

1. declare @var nvarchar(MAX)
2. exec @var=sp_helptext 'test'
3. SELECT @var
line 2 returns sps' text and line 3 returns 0 as output why line 3 returns 0 instead of sps' text, i think this is because i am not using SET statement so whats the solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 08:17:35

why are you taking in a variable? you just need to do below

sp_helptext 'test'

and paste it onto a new window and do modifications sugested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-05 : 08:19:00

sp_helptext 'your proc name'

has a limit of 255 characters per line, and will introduce an extraneous linebreak for lines longer than that.

exec @var=sp_helptext 'test'

will set @var to the "result" of the Sproc - which will be 0=No error, or <>0 for an error

If you want the results from the resultset, gnereated by the Sproc, you need

INSERT INTO MyTable
EXEC MySproc

and then

SELECT * FROM MyTable

MyTable can be a normal table, a #Temp temporary table, but cannot (I think) be an @TableVar table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources