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 GO
To execute a stored procedure using EXEC pass the procedure name and parameters if any
EXEC GetLocations @LocID = 1