Metadata for the result set can be defined by using the WITH RESULT SETS options. Executes a command string or character string within a Transact-SQL batch, or one of the following modules, system stored procedure, CLR stored procedure, scalar-valued user-defined function, user-defined stored procedure, or extended stored procedure. The EXECUTE statement can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set.
IF EXISTS (SELECT 1 FROM SYS.TABLES where name ='Locations')
BEGIN
DROP TABLE Locations
END
CREATE TABLE [dbo].[Locations](
[LocationID] [int] NULL,
[LocationName] [varchar](100) NULL
)
GO
INSERT INTO Locations values (1,'Delhi'),(2,'Brigade Road') ,(3,'House')
GO
IF EXISTS (SELECT 1 FROM SYS.procedures where name ='GetLocations')
BEGIN
DROP PROCEDURE GetLocations
END
GO
CREATE PROCEDURE [GetLocations]
(@LocID int)
AS
BEGIN
select LocationID,LocationName from Locations where LocationID =@LocID
END
GOTo execute a stored procedure using EXEC pass the procedure name and parameters if any
EXEC GetLocations @LocID = 12020-03-29