Execute dynamic SQL in MSSQL

I hit this need when I was developing own custom module into Commerce Server. Commerce Server have all tables separated into catalogs. Catalog is group of tables which contains records for particular client (site), but have same structure like all other catalogs. Lets say that we have two clients ‘Client1’, ‘Client2’. Each client has tables for products ‘Client1_Products’, ‘Client2_Products’. Now we can create two procedures for retrieving data from both tables or better we can create one procedure, which will have one input parameter – Catalog name.

We can create the procedure:

CREATE PROCEDURE [dbo].[GetProducts]
(
  @CatalogName nvarchar(85),
  @Language nvarchar(85) = 'en-US',
  @ProductID nvarchar(255)
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(4000)
  SET @sql = 'SELECT
      *
    FROM [dbo].[' + @CatalogName + '_Products] AS [Products]
    INNER JOIN [dbo].[' + @CatalogName + '_Products_' + @Language + '] AS [ProductsLang] ON
      [Products].[ProductID] = [ProductsLang].[ProductID]
    IF(@ProductID IS NOT NULL)
      SET @sql = @sql + ' WHERE [Products].[ProductID] = @ProductID '

  EXEC sp_executesql @sql,
    N'@ProductID nvarchar(85)',
    @ProductID
END

Now we are able to call this procedure for any client and as bonus get language related data for products. EXEC [dbo].[GetProducts] ‘Client1’, ‘en-US’; will return all products for ‘Client1’.

We can specify product ID if we want to receive only one product. Product ID condition will be added to where.

IF(@ProductID IS NOT NULL)
  SET @sql = @sql + ' WHERE [Products].[ProductID] = @ProductID '

At the end of stored procedure we will execute generated SQL.

EXEC sp_executesql @sql,
  N'@ProductID nvarchar(85)',
  @ProductID

First parameter of sp_executesql is a Unicode string that contains a Transact-SQL statement or batch. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed at this point. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Second parameter is list of strings that contains the definitions of all parameters that have been embedded in first parameter. Every parameter must be defined in @params.

Third parameter are values for the parameters that is defined in the second parameter.

OUT or OUTPUT indicates that the parameter is an output parameter. By this, you can get last inserted sequence number for example.