CODEWITHSUNDEEP
sql-servert-sql
John
John

Reputation: 15

Calling a Stored Procedure from inside a function a using it

I have a function called

openCases where i am trying to call another stored procedure and use its values, but not sure if i can use it this way, please guide if i am wrong way

can i do this in stored procedure, calling in other stored procedure

DECLARE @b int
EXEC dbo.info @id, @b OUTPUT;
SET @Eemail = @b.emailaddress

is it correct ?

Update#1

DECLARE @bTable
        ,@Eemail varchar(max)
EXEC dbo.info @id, @b OUTPUT;
    SET @Eemail = @b.emailaddress

got an error:

Must declare the scalar variable @Eemail

Upvotes: 0

Views: 64

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7960

Your stored procedure should return a table including email address like below:

BEGIN
   select Id, EmailAddress, ..., from myTable;
END

Then, you can use it like:

DECLARE @bTable table (Id int,Email varchar(max),...)

EXEC dbo.info @id, bTable  OUTPUT;
SET @Eemail = select Email from @bTable

If your @bTable includes multiple rows then you will get an error (subquery returned more than 1 rows). To fix it if it happens, use ID in where clause like: where Id = @Id

Upvotes: 1

Related Questions