SQL Server exec keyword

SQL Server exec keyword

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

2020-03-29