Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql Expand / Collapse
Author
Message
Posted Monday, April 9, 2012 10:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
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?
Post #1280599
Posted Tuesday, April 10, 2012 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1280789
Posted Tuesday, April 10, 2012 7:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 4,421, Visits: 10,739
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
Post #1280792
Posted Tuesday, April 10, 2012 7:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 4,421, Visits: 10,739
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
Post #1280797
Posted Tuesday, April 10, 2012 7:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1280803
Posted Tuesday, April 10, 2012 8:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 4,421, Visits: 10,739
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
Post #1280809
Posted Tuesday, April 10, 2012 8:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
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?

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


----------------------------------------------------
"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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1280820
Posted Tuesday, April 10, 2012 8:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 4,421, Visits: 10,739
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?

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


It wouldn't hurt...



--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1280844
Posted Friday, May 10, 2013 6:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:33 AM
Points: 1,325, Visits: 2,591
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
Post #1451552
Posted Friday, May 10, 2013 7:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Spam. Reported.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1451575
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse