sp_helptext - 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

SQL Server 2008 Forums

Transact-SQL (2008)

sp_helptext

Author

Topic

nitin1111
Starting Member

13 Posts

Posted - 2010-03-25 : 06:02:14

Hi,

I am very much new for sql server.I used to work as oracle dba to support development env.but now onwards I have to support for sql server 2008 dev environment as well.There I may require to write procedures,function(programs),I have started basic programming learning.
Please suggest book.

question:--
sp_helptext (proc name) system stored procedure returns text of the proc.
but return type is integer.
with integer data type can we return text value to calling environment?

Thanks

M.N.Dani

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 06:06:44

quote:
Originally posted by nitin1111

Hi,

I am very much new for sql server.I used to work as oracle dba to support development env.but now onwards I have to support for sql server 2008 dev environment as well.There I may require to write procedures,function(programs),I have started basic programming learning.
Please suggest book.

question:--
sp_helptext (proc name) system stored procedure returns text of the proc.
but return type is integer.
with integer data type can we return text value to calling environment?

Thanks

M.N.Dani

<<
but return type is integer.
>>
Did you mean the (proc name) has output parameter of type interger?
It has nothing to do with what sp_helptext returns

Madhivanan

Failing to plan is Planning to fail

nitin1111
Starting Member

13 Posts

Posted - 2010-03-25 : 06:32:26

"Did you mean the (proc name) has output parameter of type interger?"

There are only two input parameters.No output parameter.

Thanks

M.N.Dani

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 06:37:10

quote:
Originally posted by nitin1111

question:--
sp_helptext (proc name) system stored procedure returns text of the proc.
but return type is integer.
with integer data type can we return text value to calling environment?

sorry,Your question din't clear.

nitin1111
Starting Member

13 Posts

Posted - 2010-03-25 : 06:59:03

exec sp_helptext (procedure name)
this procedure returns text of the procedure.
there are only two inputs parameters.
How this procedure can pass text of the procedure whitout having output parameters to calling environment?
sorry..If I am confusing you.

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 07:56:13

exec sp_helptext (procedure name) returns an INT (presumably to indicate Success or Failure). It also outputs a ResultSet which contains the text of the Sproc

You can save the ResultSet from an Sproc into a Table (but only the first ResultSet - if the Sproc returns multiple resultsets


INSERT INTO MyTable(SomeTextColumn)
EXEC sp_helptext (procedure name)

nitin1111
Starting Member

13 Posts

Posted - 2010-03-25 : 08:44:28

Thanks

M.N.Dani

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 08:59:01

<<
You can save the ResultSet from an Sproc into a Table (but only the first ResultSet - if the Sproc returns multiple resultsets
>>

If all the resultsets return same number of columns with identical datatypes, all resultsets would be saved to the table

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 09:25:15

"If all the resultsets return same number of columns with identical datatypes, all resultsets would be saved to the table"

Didn't know that, thanks. That would REALLY spoil my day not knowing which ResultSet each row had come from!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 09:36:46

<<
That would REALLY spoil my day not knowing which ResultSet each row had come from!
>>

Until you process resultsets in Front end application using NextRecordSet property

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 09:39:29

Yeah ... in reality I would add a column to say which ResultSet the row belonged to (if it was going to be inserted into a working-table), but then I might as well UNION ALL them together and return a single ResultSet

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources