Execute a stored procedure in another stored procedure in SQL server

Question

How can i execute a stored procedure in another stored procedure in SQL server   How will I pass the parameters of the second procedure

User · Answer

Your sp test  Return fullname  USE  MY DB  GO  IF  OBJECT ID   dbo   sp test     P   IS NOT NULL  DROP PROCEDURE  dbo  sp test  GO  CREATE PROCEDURE  dbo  sp test   name VARCHAR 20    last name VARCHAR 30    full name VARCHAR 50  OUTPUT AS  SET  full name    name    last name   GO   In your sp main      DECLARE  my name VARCHAR 20   DECLARE  my last name VARCHAR 30   DECLARE  my full name VARCHAR 50        EXEC sp test  my name   my last name   my full name OUTPUT

User · Answer

Procedure example    Create  PROCEDURE SP Name       UserName nvarchar 200         Password nvarchar 200  AS BEGIN     DECLARE   loginID  int      --Statements for this Store Proc   --   --    --    --execute second store procedure   --below line calling sencond Store Procedure Exec is used for execute Store Procedure        Exec SP Name 2  params    if any     END

User · Answer

Yes  you can do that like this   BEGIN    DECLARE  Results TABLE  Tid INT PRIMARY KEY       INSERT  Results     EXEC Procedure2  parameters      SET  total 1   END SELECT  total

User · Answer

If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do   Exec secondSPName   anyparams   Else  if you need values returned by your second SP inside your first one  then create a temporary table variable with equal numbers of columns and with same definition of column return by second SP  Then you can get these values in first SP as   Insert into  tep table Exec secondSPName  anyparams   Update   To pass parameter to second sp  do this   Declare  id ID Column datatype  Set  id  Select id from table 1 Where yourconditions   Exec secondSPName  id   Update 2   Suppose your second sp returns Id and Name where type of id is int and name is of varchar 64  type   now  if you want to select these values in first sp then create a temporary table variable and insert values into it   Declare  tep table table     Id int    Name varchar 64    Insert into  tep table Exec secondSP  Select   From  tep table   This will return you the values returned by second SP   Hope  this clear all your doubts

User · Answer

You can call User-defined Functions in a stored procedure alternately   this may solve your problem to call stored procedure

User · Answer

Yes   Its easy to way we call the function inside the store procedure   for e g  create user define Age function and use in select query   select dbo GetRegAge R DateOfBirth  r RegistrationDate  as Age R DateOfBirth r RegistrationDate from T Registration R

User · Answer

Suppose you have one stored procedure like this   First stored procedure   Create  PROCEDURE LoginId       UserName nvarchar 200         Password nvarchar 200  AS BEGIN     DECLARE   loginID  int      SELECT  loginID   LoginId      FROM UserLogin      WHERE UserName    UserName AND Password    Password      return  loginID END   Now you want to call this procedure from another stored procedure like as below  Second stored procedure   Create  PROCEDURE Emprecord           UserName nvarchar 200             Password nvarchar 200             Email nvarchar 200             IsAdmin bit            EmpName nvarchar 200             EmpLastName nvarchar 200             EmpAddress nvarchar 200             EmpContactNo nvarchar 150             EmpCompanyName nvarchar 200       AS     BEGIN         INSERT INTO UserLogin VALUES  UserName  Password  Email  IsAdmin           DECLARE  EmpLoginid int            exec  EmpLoginid  LoginId  UserName  Password            INSERT INTO tblEmployee VALUES  EmpName  EmpLastName  EmpAddress  EmpContactNo  EmpCompanyName  EmpLoginid      END   As you seen above  we can call one stored procedure from another

[sql] Execute a stored procedure in another stored procedure in SQL server

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures