Execute Dynamic SQL in SQL Server

Problem: In some applications, we need to take value from the user at run time. So for that, there is a need to dynamically generate a query and then run it.

Solution: There are multiple approaches to solve this but "Stored Procedure" is the most beneficial approach. Create a "Stored Procedure" and execute it when need it.

Example: Following is the Customer table.

If we need to fetch customers who live in California (CA). Then use the following query.

USE [demo]
GO

CREATE PROCEDURE [dbo].[sp_execSQL] @state varchar(10)
AS

DECLARE @sql varchar(max)

SET @sql = 'select * from Customers where State = ''' + @state + '''';

EXEC(@sql)

This query creates a "Stored Procedure" and it's reusable. After creating SP, use below code to execute it. We are passing 'CA' as a parameter and it will fetch the Customers who are in California.

EXEC [dbo].[sp_execSQL] 'CA'