Using XML to pass dynamic paramters to SQL stored procedure.

You can use an XML string to pass any number of options to a SQL stored procedure like so


Use a scalar valued function to get one value at a time, which is still very fast unless you have hundreds of key-value pairs with XML like MyValue

Use like this

USE [SaveOn]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetClubUserRenewals]    Script Date: 5/6/2015 12:37:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Alt
-- Create date: 2015-4-30
-- Description: Gets count of users and total dollar value from user renewals per club.
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetClubUserRenewals]
      @Filters NVARCHAR(MAX)
AS

     BEGIN
         SET NOCOUNT ON;

         --Declare variables
         DECLARE @BeginDate DATETIME

         --Parse out values from xml object
         SELECT @BeginDate = dbo.GetFilterValue(@Filters,'BeginDate');

         --Select statement
         SELECT * FROM SOMETABLE  WHERE(@BeginDate IS NULL OR DateField >= @BeginDate)
         

     END


And here is the scalar valued function.


GO
/****** Object:  UserDefinedFunction [dbo].[GetFilterValue]    Script Date: 5/6/2015 12:35:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Alt
-- Create date: 2015-4-30
-- Description: Gets the value of a single node in a Filters xml object defined like Value
-- =============================================
ALTER FUNCTION [dbo].[GetFilterValue](
               @Filters XML,
               @FilterName NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
     BEGIN
         -- Declare the return variable here
         DECLARE @RetVal NVARCHAR(MAX);

         -- Add the T-SQL statements to compute the return value here
         SELECT @RetVal = NULLIF(LTRIM(RTRIM(node.value('.','nvarchar(max)'))),'')
           FROM @Filters.nodes('/Filters/*') AS Nodes(node)
           WHERE node.value('local-name(.)','nvarchar(max)') = @FilterName;

         -- Return the result of the function
         RETURN @RetVal;

     END;

Or get them all at once with a table valued function:
USE [SaveOn]
GO
/****** Object:  UserDefinedFunction [dbo].[ParseFilters]    Script Date: 5/4/2015 3:18:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Alt
-- Create date: 2015-4-30
-- Description: Takes xml like MyValue and returns all name-value pairs in a table, removing empty entries.
-- =============================================
ALTER FUNCTION [dbo].[ParseFilters](
               @XML XML)
RETURNS @RtnTable TABLE(ID INT IDENTITY(1,1),
                        Name NVARCHAR(MAX),
                        Value NVARCHAR(MAX))
AS
     BEGIN
         INSERT INTO @RtnTable(Name,
                               [Value])
         SELECT node.value('local-name(.)','nvarchar(max)') AS Name,
                NULLIF(LTRIM(RTRIM(node.value('.','nvarchar(max)'))),'') AS Value
           FROM @XML.nodes('/Filters/*') AS Nodes(node);

         RETURN;
     END;