SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql


Author
Message
asranantha
asranantha
SSC Eights!
SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)

Group: General Forum Members
Points: 964 Visits: 4717
1)can we call sp into another sp if yes plz give small example?if no plz tell me reason?
2)can we call functionn into another function if yes plz give small example?if no plz tell me reason?
3)can we call sp in function?if yes plz give small exmp?if no plz tell me reason?
4)can we call function in sp?if yes plz give small examp?if no plz tell me reason?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94145 Visits: 33010
asranantha (4/9/2012)
1)can we call sp into another sp if yes plz give small example?if no plz tell me reason?
2)can we call functionn into another function if yes plz give small example?if no plz tell me reason?
3)can we call sp in function?if yes plz give small exmp?if no plz tell me reason?
4)can we call function in sp?if yes plz give small examp?if no plz tell me reason?


Sounds like interview questions. If you don't the answer in an interview, just say "I don't know." If I'm interviewing you, I'll feel a lot better about an honest answer than to have you reading an answer from a web site that may or may not be true.

You can answer all these on your own with a tiny bit of experimentation.
1) Yes
2) Depends
3) Depends
4) Yes

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22795 Visits: 13361
1) Yes

CREATE PROCEDURE usp_test_1
AS
BEGIN
PRINT '1'
END
GO

CREATE PROCEDURE usp_test
AS
BEGIN
EXEC usp_test_1
END



2) Yes

CREATE FUNCTION ufn_test_1()
RETURNS int
AS
BEGIN
RETURN 1
END
GO

CREATE FUNCTION ufn_test()
RETURNS int
AS
BEGIN
RETURN dbo.ufn_test_1()
END



3) No. The EXEC command is not allowed in functions.

4) Yes


CREATE PROCEDURE usp_test_2
AS
BEGIN
SELECT dbo.ufn_test()
END
GO



--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22795 Visits: 13361
Grant, your "Depends" answers made me curious.
CLR tricks aside, how can you call a procedure inside a function?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212813 Visits: 46259
Gianluca Sartori (4/10/2012)
CLR tricks aside, how can you call a procedure inside a function?


OPENROWSET

Doesn't make it good practise, can have some really fun effects (functions aren't allowed to change data so that the optimiser is free to run it as many times as it likes)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22795 Visits: 13361
GilaMonster (4/10/2012)
Gianluca Sartori (4/10/2012)
CLR tricks aside, how can you call a procedure inside a function?


OPENROWSET

Doesn't make it good practise, can have some really fun effects (functions aren't allowed to change data so that the optimiser is free to run it as many times as it likes)


HA! That's a good one.
I even have a blog post on that trick and I didn't think of it!

Thanks

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94145 Visits: 33010
Gianluca Sartori (4/10/2012)
Grant, your "Depends" answers made me curious.
CLR tricks aside, how can you call a procedure inside a function?


Why can't we use CLR tricks? :-P

And I hope you get the job Gianluca (since you're answering the questions). w00t

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22795 Visits: 13361
Grant Fritchey (4/10/2012)
Gianluca Sartori (4/10/2012)
Grant, your "Depends" answers made me curious.
CLR tricks aside, how can you call a procedure inside a function?


Why can't we use CLR tricks? :-P

And I hope you get the job Gianluca (since you're answering the questions). w00t


It wouldn't hurt... :-P

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Divine Flame
Divine Flame
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4231 Visits: 2816
chetan.aegis (5/10/2013)
SQL is a special-purpose programming language designed for managing data held in a relational database management system. Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition language and a data manipulate language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often described as, and to a great extent is, a declarative language, it also includes procedural elements.


What's the point here?


Sujeet Singh
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212813 Visits: 46259
Spam. Reported.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search