Primer Dynamic sp:

CREATE PROCEDURE sp_example

@Language varchar(3) = NULL,
@SOrders integer = NULL ,
@Q varchar(3)
etc etc ...
---every parameter may be implemented as check box in Front End ( user interface )
---put here all your conditional parameters ------

AS

DECLARE @SQLQuery varchar(8000)
----eta peremennaia bydet codershat" vec" zaproc , kak char stroka

SELECT @SQLQuery = '
SELECT DISTINCT u.UserID as UserID,
u.LanguageID' +
CASE WHEN @SOrders IS NOT NULL THEN ',
count( DISTINCT uo.OrderID ) AS NumberOrders' ELSE '' END +
CASE WHEN @QuantityItemPurchased IS NOT NULL THEN ',
count( DISTINCT sli.ShoppingListItemId ) AS NbItems' ELSE '' END +
'
FROM tbUser u

IF( @SOrders IS NOT null)
BEGIN
IF( @SubmittedNbOrders IS NOT NULL )
BEGIN
SELECT @SQLQuery = @SQLQuery + '
HAVING COUNT( DISTINCT uo.OrderID ) ' + CASE @SubmittedCompare WHEN 1 THEN '= ' WHEN 2 THEN '> ' WHEN 3 THEN '< ' ELSE '= ' END + CONVERT( VARCHAR, @SubmittedNbOrders )

IF( @Q IS NOT NULL )
BEGIN
SELECT @SQLQuery = @SQLQuery + ' AND COUNT( DISTINCT sli.ShoppingListItemId ) ' + CASE @QuantityCompare WHEN 1 THEN '= ' WHEN 2 THEN '> ' WHEN 3 THEN '< ' ELSE '= ' END + CONVERT( VARCHAR, @QuantityItemPurchased )
END
END
ELSE
BEGIN
SELECT @SQLQuery = @SQLQuery + '
HAVING COUNT( DISTINCT sli.ShoppingListItemId ) ' + CASE @QuantityCompare WHEN 1 THEN '= ' WHEN 2 THEN '> ' WHEN 3 THEN '< ' ELSE '= ' END + CONVERT( VARCHAR, @QuantityItemPurchased )
END
END


etc etc.....
---pishesh" stol"ko yslovii ckol"ko tebe trebyrtcia chtobi proanalizirovat" vce tvoi peremennie , opredelennie v nachale.

PRINT( @SQLQuery )----you can see the result of your dynamic built query and can check it ;

EXEC( @SQLQuery )



ecli ect" voproci cprashivai