Store Locations and Retrieve Dropdown Data with Stored Procedure

Introduction

SQL Server, creating tables and stored procedures is essential for organizing data and executing repetitive tasks efficiently. This article guides you through the process of creating a table for storing locations and a stored procedure to retrieve dropdown data from that table.

Creating the Locations Table

To begin, let's create a table named Locations to store information about various locations.

code

CREATE TABLE Locations (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Inserting Data into the Locations Table.

INSERT INTO Locations (id, name)
VALUES 
    ('Tamil Nadu'),
    ('Kerala'),
    ('Mumbai'),
    ('Delhi');

Explanation

INSERT INTO Locations (id, name) VALUES: Insert rows into the Locations table with specified id and name values.

Creating the GetDropdownData Stored Procedure

Now, let's create a stored procedure named GetDropdownData to fetch data from the Locations table, suitable for populating a dropdown list.

USE TraineeDB; -- Specify the database context
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE PROCEDURE dbo.GetDropdownData
AS
BEGIN
    SELECT id, name FROM Locations;
END;

Explanation

• USE TraineeDB: Sets the database context to TraineeDB where the Locations table resides.
• SET ANSI_NULLS ON; and SET QUOTED_IDENTIFIER ON: These settings ensure proper SQL Server behavior during procedure execution.
• CREATE PROCEDURE dbo.GetDropdownData: Defines a stored procedure named GetDropdownData within the dbo schema.
• SELECT id, name FROM Locations: Retrieves id and name columns from the Locations table, providing data suitable for dropdown lists.

Conclusion

By following these steps, you've created an SQL table to store location data and a stored procedure to efficiently retrieve that data for dropdown lists or other purposes. Customizing the table structure and procedure logic allows you to adapt these examples to meet specific database requirements in your applications.