April 9, 2012 at 10:42 pm
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?
April 10, 2012 at 7:44 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2012 at 7:44 am
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
April 10, 2012 at 7:47 am
Grant, your "Depends" answers made me curious.
CLR tricks aside, how can you call a procedure inside a function?
-- Gianluca Sartori
April 10, 2012 at 7:53 am
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
April 10, 2012 at 8:00 am
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
April 10, 2012 at 8:15 am
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). :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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2012 at 8:50 am
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). :w00t:
It wouldn't hurt... 😛
-- Gianluca Sartori
May 10, 2013 at 6:34 am
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?
May 10, 2013 at 7:17 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply