Question related to Stored procedure

Question

0.00/5 (No votes)

See more:

Sir/mam,
If we create a stored procedure or function in SQL SERVER and it returns a table and again if we wish to create another stored procedure or function that will make a call to the previous procedure or function between the code, how shall we handle the return type that is returned by first procedure or function. sir, I mean to say 1st procedure or function returns a table that should be hold in a "table" type varriable in the same way as integer is hold in "integer" type variable. But i am unable to get that type of variable. So what should i do to hold the table in a varriable ?

Posted 7-Mar-10 1:44am

rahul_singh430

Add a Solution

2 solutions

Solution 1

You can create a function that returns a table then use it. Something like this:

Create Function SomeFunction
returns table as

return (select * from someTable)

in the stored procedure, you can have something like this:

select * from SomeFunction

Here, you can make use to into and temporary tables.

Posted 7-Mar-10 2:55am

dan!sh

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jayantbramhankar · Accepted Answer · 2010-03-07T03:32:00

Create your first Stored Procedure which returns a table
The code for the stored procedure might look like this-

SQL

CREATE PROCEDURE dbo.GetDetailByFname
    (
    @Fname nvarchar(50)
    )
AS
    Select pid, Fname, Mname, Lname from Personal where fname=@Fname;

If you call this stored Procedure as

SQL

EXEC dbo.GetPeopleByLastName @FName = 'Rahul'

Result will look like

pid                FName    MName      LNAme
uniquidentifier    Rahul     .          Singh
uniquidentifier    Rahul     abc        Bachchan
--------           Rahul     XYZ        Rathod

If you want to use this result you'll need a place to store it, that is Temporary Tables . That code might look something like this-

SQL

CREATE PROCEDURE dbo.CountByLname
As
DECLARE  @Personal TABLE (
    Pid uniqueidentifier,
    FName NVARCHAR(50),
    Mname NVARCHAR(50),
    LName NVARCHAR(50)
)

INSERT @Personal (Pid, FName, Mname, LName)
EXEC dbo.GetDetailByFname @Fname = 'Rahul'

SELECT COUNT(*) AS Counts FROM @Personal

If you call this stored Procedure as

SQL

EXEC CountByLname

Result will look like

Counts
3

This is how we can call 1st procedure or function that returns a table in 2nd stored procedure or function
If you want Integer as return type
Try this one as 1st stored procedure

CREATE PROCEDURE dbo.GetDetailByFname
	(
	@Fname nvarchar(50),
              @Age INT  OUTPUT
	)
AS
Select Age from Personal where Fname=’Rahul’

Then use this output variable in 2nd stored procedure

I hope it's helped you..

Regards,
jayant