Tuesday, November 23, 2010

Generating Dynamic Search with Help of CodeSmith

Recently I was wondering how I can create a Search Stored Procedure for each table in my database since I have fairly long list of tables ,I give it a thought and classified search stored procedure into two type one that need to have join with other tables ,and other which don't need to join with any other table.
   So I created a code-smith template for second and easiest type of search stored procedure, I will like to share it with community so that someone can use it to his benefits, improve it further.

Here It Is

<%------------------------------------------------------------------------------------------
* Author: Sangram S. Desai
* Description: This template will generate Search
*   database table.
------------------------------------------------------------------------------------------%>
<%@ CodeTemplate Debug="True" Language="C#" Inherits="CodeSmith.BaseTemplates.SqlCodeTemplate" TargetLanguage="T-SQL"
    Description="Generates standard CRUD procedures based on a database table schema." %>
<%-- Context --%>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="1. Context"
    Description="Table that the stored procedures should be based on." %>
<%-- Options --%>
<%@ Property Name="IncludeDropStatements" Type="System.Boolean" Default="True" Category="2. Options"
    Description="If true drop statements will be generated to drop existing stored procedures." %>
<%@ Property Name="IsolationLevel" Type="TransactionIsolationLevelEnum" Default="ReadCommitted" Category="2. Options"
    Description="Isolation level to use in the generated procedures." %>
<%@ Property Name="ProcedurePrefix" Type="System.String" Default="usp_" Category="2. Options"
    Description="Prefix to use for all generated procedure names." %>
<%@ Property Name="TablePrefix" Type="System.String" Default="tbl_" Category="2. Options"
    Description="If this prefix is found at the start of a table name, it will be stripped off." %>
<%@ Property Name="AutoExecuteScript" Type="System.Boolean" Default="False" Category="2. Options"
    Description="Whether or not to immediately execute the script on the target database." %>
<%@ Property Name="OrderByExpression" Type="System.String" Default="" Optional="True" Category="2. Options"
    Description="If supplied, this expression will be used to apply an order to the results on SELECT statements." %>
<%@ Property Name="ExcludedColumns" Type="StringCollection" Default="" Optional="True" Category="2. Options"
    Description="If supplied, any columns in this list will be excluded from all stored procedures unless the column is part of the primary key. (* is treated as a wildcard)" %>
<%@ Property Name="ReadOnlyColumns" Type="StringCollection" Default="" Optional="True" Category="2. Options"
    Description="If supplied, any columns in this list will be treated as read only. (* is treated as a wildcard)" %>
  
<%-- Assembly References --%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="CodeSmith.CustomProperties" %>
<%@ Assembly Name="System.Data" %>
<%-- Namespace Imports --%>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.CustomProperties" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%
this.Progress.MaximumValue = 13;
this.Progress.Step = 1;

// this template requires a primary key on the source table
if (!SourceTable.HasPrimaryKey) throw new ApplicationException("SourceTable does not contain a primary key.");
%>
/*added by sangram*/
<%------------------------------------------------------------------------------------------
*
* Search Procedure
*
------------------------------------------------------------------------------------------%>
<%
    GenerateProcedureHeader(GetSearchProcedureName());
%>

CREATE PROCEDURE <%= GetSearchProcedureName() %>
    @SearchUsingOR bit=null,
<% GenerateParameters(SourceTable.Columns, 1); %>
AS
DECLARE @QueryString nvarchar(4000)
DECLARE @HasWhere bit

--Search Using OR/AND
DECLARE @SearchUsing varchar(5)
IF (@SearchUsingOR=1)
    SET @SearchUsing = ' OR ';
ELSE
    SET @SearchUsing=' AND ';

<%
    GenerateSelectList(SourceTable.Columns);
%>
SET @HasWhere=0

<%
    GenerateConditions(SourceTable.Columns);
%>

IF(@HasWhere=1)  
    BEGIN
        SET @QueryString = @QueryString + ' )';
    END

PRINT @QueryString

<%
    GenerateDynamicExecute(SourceTable.Columns);  
    GenerateProcedureFooter(GetSearchProcedureName());
    this.Progress.PerformStep();
%>
<script runat="template">
#region Member Variables
private StringCollection _droppedProcedureNames = new StringCollection();
private StringCollection _generatedProcedureNames = new StringCollection();
#endregion

#region Isolation Level
public enum TransactionIsolationLevelEnum
{
    ReadCommitted,
    ReadUncommitted,
    RepeatableRead,
    Serializable
}

public void GenerateSetTransactionIsolationLevelStatement(TransactionIsolationLevelEnum isolationLevel)
{
    Response.Write("SET TRANSACTION ISOLATION LEVEL ");
  
    switch (isolationLevel)
    {
        case TransactionIsolationLevelEnum.ReadUncommitted:
        {
            Response.WriteLine("READ UNCOMMITTED");
            break;
        }
        case TransactionIsolationLevelEnum.RepeatableRead:
        {
            Response.WriteLine("REPEATABLE READ");
            break;
        }
        case TransactionIsolationLevelEnum.Serializable:
        {
            Response.WriteLine("SERIALIZABLE");
            break;
        }
        default:
        {
            Response.WriteLine("READ COMMITTED");
            break;
        }
    }
}
#endregion

#region Code Generation Helpers
public string GetTableOwner()
{
    return GetTableOwner(true);
}

public string GetTableOwner(bool includeDot)
{
    if (SourceTable.Owner.Length > 0)
    {
        if (includeDot)
        {
            return "[" + SourceTable.Owner + "].";
        }
        else
        {
            return "[" + SourceTable.Owner + "]";
        }
    }
    else
    {
        return "";
    }
}

public void GenerateDropStatement(string procedureName)
{
    // check to see if this procedure has already been dropped.
    if (!_droppedProcedureNames.Contains(procedureName))
    {
        Response.WriteLine("IF OBJECT_ID(N'{0}') IS NOT NULL", procedureName);
        GenerateIndent(1);
        Response.WriteLine("DROP PROCEDURE {0}", procedureName);
        Response.WriteLine("");
      
        // add this procedure to the list of dropped procedures
        _droppedProcedureNames.Add(procedureName);
    }
}

public void GenerateProcedureHeader(string procedureName)
{
    Response.WriteLine("--region {0}", procedureName);
    Response.WriteLine("");
    Response.WriteLine("------------------------------------------------------------------------------------------------------------------------");
    Response.WriteLine("-- Generated By:   {0} using CodeSmith {1}", System.Environment.UserName, typeof(CodeTemplate).Assembly.GetName().Version.ToString());
    Response.WriteLine("-- Template:       {0}", this.CodeTemplateInfo.FileName);
    Response.WriteLine("-- Procedure Name: {0}", procedureName);
    Response.WriteLine("-- Date Generated: {0}", DateTime.Now.ToLongDateString());
    Response.WriteLine("------------------------------------------------------------------------------------------------------------------------");
}

public void GenerateProcedureFooter(string procedureName)
{
    Response.WriteLine("--endregion");
    Response.WriteLine("");
    Response.WriteLine("GO");
    Response.WriteLine("");
}

public void GenerateIndent(int indentLevel)
{
    for (int i = 0; i < indentLevel; i++)
    {
        Response.Write('\t');
    }
}

public void GenerateParameter(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
    GenerateParameter(column, indentLevel, isFirst, isLast, false);
}

public void GenerateParameter(ColumnSchema column, int indentLevel, bool isFirst, bool isLast, bool isOutput)
{
    GenerateIndent(indentLevel);
    Response.Write(GetSqlParameterStatement(column, isOutput));
    Response.Write("= null ");
    if (!isLast) Response.Write(",");
    if (indentLevel >= 0)
    {
        Response.WriteLine("");
    }
    else if (!isLast)
    {
        Response.Write(" ");
    }
}

public void GenerateParameters(ColumnSchemaCollection columns, int indentLevel)
{
    GenerateParameters(columns, indentLevel, false);
}
public void GenerateSelectList(ColumnSchemaCollection columns)
{
    string StrQuery="SET @QueryString = 'SELECT ";
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        if(i != filteredColumns.Count-1)
        {
            StrQuery = StrQuery + "a." + filteredColumns[i].Name + ",";
        }else
        {
            StrQuery = StrQuery + "a." + filteredColumns[i].Name;
        }
    }
    StrQuery = StrQuery + " FROM " +  filteredColumns[0].Table.Name;
    StrQuery = StrQuery + " a '";
    Response.WriteLine(StrQuery);
}
public void GenerateConditions(ColumnSchemaCollection columns)
{
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateCondition(filteredColumns[i].Name);
    }
}
public string GenerateParamDeclarations(ColumnSchemaCollection columns)
{
    string StrParamList="";
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        if(i!=filteredColumns.Count-1)
        {
            StrParamList = StrParamList + GetDummySqlParameterStatement("@Dummy",filteredColumns[i],false)+ ",";
        }else
        {
            StrParamList = StrParamList + GetDummySqlParameterStatement("@Dummy",filteredColumns[i],false);
        }
    }
    return StrParamList;
}
public void GenerateDynamicExecute(ColumnSchemaCollection columns)
{
    string StrQuery="EXECUTE SP_EXECUTESQL @QueryString,";
    string ParamDeclartion = GenerateParamDeclarations(columns);
    string Assignment="";
  
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        if(i != filteredColumns.Count-1)
        {
            Assignment = Assignment + "@Dummy"+ filteredColumns[i].Name + "="  + "@"+ filteredColumns[i].Name + ",";
        }else
        {
            Assignment = Assignment + "@Dummy"+ filteredColumns[i].Name + "="  + "@"+ filteredColumns[i].Name + ";";
        }
    }
    ParamDeclartion = "N'"+ParamDeclartion +"',";

    Response.WriteLine(StrQuery);
    Response.WriteLine(ParamDeclartion);
    Response.WriteLine(Assignment);
}

public void GenerateCondition(string ColName)
{
    Response.WriteLine("IF(@" + ColName + " IS NOT NULL)");
    Response.WriteLine("BEGIN");
    Response.WriteLine("IF(@HasWhere=0)");
    Response.WriteLine("BEGIN");
    Response.WriteLine("SET @QueryString = @QueryString + ' WHERE ( " + ColName + " = @Dummy" + ColName + "';" );
    Response.WriteLine("SET @HasWhere =1;" );
    Response.WriteLine("END");
    Response.WriteLine("ELSE");
    Response.WriteLine("SET @QueryString = @QueryString +  @SearchUsing + '  " + ColName + " = @Dummy" + ColName + "';");
    Response.WriteLine("END");
    Response.WriteLine();
}

public void GenerateParameters(ColumnSchemaCollection columns, int indentLevel, bool includeTrailingComma)
{
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateParameter(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1 && !includeTrailingComma);
    }
}

public void GenerateColumn(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
    GenerateIndent(indentLevel);
    Response.Write("[");
    Response.Write(column.Name);
    Response.Write("]");
    if (!isLast) Response.Write(",");
    if (indentLevel >= 0)
    {
        Response.WriteLine("");
    }
    else if (!isLast)
    {
        Response.Write(" ");
    }
}

public void GenerateColumns(ColumnSchemaCollection columns, int indentLevel)
{
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateColumn(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
    }
}
/*added by sangram*/
public void GenerateCustomUpdate(ColumnSchema column, int indentLevel, bool isFirst, bool isLast,SchemaExplorer.ColumnSchemaCollection pri_col)
{
    GenerateIndent(indentLevel);
    Response.WriteLine("IF(@"+column.Name+" = NULL) ");
    Response.WriteLine("BEGIN");
    string str= " SELECT @" + column.Name + "=" + column.Name + " FROM " + GetTableOwner() + "[" + SourceTable.Name + "] WHERE " + SourceTable.PrimaryKey.MemberColumns + " =@" + SourceTable.PrimaryKey.MemberColumns + "";
    Response.WriteLine(str);
    Response.WriteLine("END");
    if (indentLevel >= 0)
    {
        Response.WriteLine("");
    }
    else if (!isLast)
    {
        Response.Write(" ");
    }
}
/*added by sangram*/
public void GenerateUpdate(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
    GenerateIndent(indentLevel);
    Response.Write("[");
    Response.Write(column.Name);
    Response.Write("] = @");
    Response.Write(column.Name);
    if (!isLast) Response.Write(",");
    if (indentLevel >= 0)
    {
        Response.WriteLine("");
    }
    else if (!isLast)
    {
        Response.Write(" ");
    }
}

public void GenerateUpdates(ColumnSchemaCollection columns, int indentLevel)
{
    ColumnSchemaCollection filteredColumns = FilterReadOnlyAndExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateUpdate(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
    }
}

public void GenerateCustomUpdates(ColumnSchemaCollection columns, int indentLevel,ColumnSchemaCollection prim_columns)
{
    ColumnSchemaCollection filteredColumns = FilterReadOnlyAndExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateCustomUpdate(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1,prim_columns);
    }
}

public void GenerateCondition(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
    GenerateIndent(indentLevel);
    if (!isFirst) Response.Write("AND ");
    Response.Write("[");
    Response.Write(column.Name);
    Response.Write("] = @");
    Response.Write(column.Name);
    if (indentLevel >= 0)
    {
        Response.WriteLine("");
    }
    else if (!isLast)
    {
        Response.Write(" ");
    }
}

public void GenerateConditions(ColumnSchemaCollection columns, int indentLevel)
{
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateCondition(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
    }
}

public void GenerateVariable(ColumnSchema column, int indentLevel, bool isFirst, bool isLast)
{
    GenerateIndent(indentLevel);
    Response.Write("@");
    Response.Write(column.Name);
    if (!isLast) Response.Write(",");
    if (indentLevel >= 0)
    {
        Response.WriteLine("");
    }
    else if (!isLast)
    {
        Response.Write(" ");
    }
}

public void GenerateVariables(ColumnSchemaCollection columns, int indentLevel)
{
    ColumnSchemaCollection filteredColumns = FilterExcludedColumns(columns);
    for (int i = 0; i < filteredColumns.Count; i++)
    {
        GenerateVariable(filteredColumns[i], indentLevel, i == 0, i == filteredColumns.Count - 1);
    }
}

public void GenerateOrderByClause()
{
    if (OrderByExpression != null && OrderByExpression.Trim().Length > 0)
    {
        Response.WriteLine("ORDER BY");
        GenerateIndent(1);
        Response.WriteLine(OrderByExpression);
    }
}

public ColumnSchemaCollection FilterReadOnlyColumns(ColumnSchemaCollection columns)
{
    ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
  
    for (int i = 0; i < columns.Count; i++)
    {
        if (!ColumnIsReadOnly(columns[i])) filteredColumns.Add(columns[i]);
    }
  
    return filteredColumns;
}

public ColumnSchemaCollection FilterExcludedColumns(ColumnSchemaCollection columns)
{
    ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
  
    for (int i = 0; i < columns.Count; i++)
    {
        if (!ColumnIsExcluded(columns[i])) filteredColumns.Add(columns[i]);
    }
  
    return filteredColumns;
}

public ColumnSchemaCollection FilterReadOnlyAndExcludedColumns(ColumnSchemaCollection columns)
{
    ColumnSchemaCollection filteredColumns = new ColumnSchemaCollection();
  
    for (int i = 0; i < columns.Count; i++)
    {
        if (!ColumnIsExcludedOrReadOnly(columns[i])) filteredColumns.Add(columns[i]);
    }
  
    return filteredColumns;
}

private Regex excludedColumnRegex = null;

public bool ColumnIsExcluded(ColumnSchema column)
{
    if (column.IsPrimaryKeyMember) return false;
  
    if (excludedColumnRegex == null)
    {
        if (ExcludedColumns != null && ExcludedColumns.Count > 0)
        {
            string excluded = String.Empty;
            for (int i = 0; i < ExcludedColumns.Count; i++)
            {
                if (ExcludedColumns[i].Trim().Length > 0)
                {
                    excluded += "(" + Regex.Escape(ExcludedColumns[i]).Replace("\\*", ".*?") + ")|";
                }
            }
          
            if (excluded.Length > 0)
            {
                excluded = excluded.Substring(0, excluded.Length - 1);
                excludedColumnRegex = new Regex(excluded, RegexOptions.IgnoreCase);
            }
        }
    }
  
    if (excludedColumnRegex != null && excludedColumnRegex.IsMatch(column.Name)) return true;
  
    return false;
}

private Regex readOnlyColumnRegex = null;

public bool ColumnIsReadOnly(ColumnSchema column)
{
    if (column.IsPrimaryKeyMember) return false;
  
    if (readOnlyColumnRegex == null)
    {
        if (ReadOnlyColumns != null && ReadOnlyColumns.Count > 0)
        {
            string readOnly = String.Empty;
            for (int i = 0; i < ReadOnlyColumns.Count; i++)
            {
                if (ReadOnlyColumns[i].Trim().Length > 0)
                {
                    readOnly += "(" + Regex.Escape(ReadOnlyColumns[i]).Replace("\\*", ".*?") + ")|";
                }
            }
          
            if (readOnly.Length > 0)
            {
                readOnly = readOnly.Substring(0, readOnly.Length - 1);
                readOnlyColumnRegex = new Regex(readOnly, RegexOptions.IgnoreCase);
            }
        }
    }
  
    if (readOnlyColumnRegex != null && readOnlyColumnRegex.IsMatch(column.Name)) return true;
  
    return false;
}

public bool ColumnIsExcludedOrReadOnly(ColumnSchema column)
{
    return ColumnIsExcluded(column) || ColumnIsReadOnly(column);
}
#endregion

#region Procedure Naming

public string GetSearchProcedureName()
{
    return GetEntityName(false) + "_Search";
}
/*Added By Sangram*/
public string GetCustomInsertUpdateProcedureName()
{
    return String.Format("{0}[{1}{2}_Insert_Update]", GetTableOwner(), ProcedurePrefix, GetEntityName(false));
}
public string GetEntityName(bool plural)
{
    string entityName = SourceTable.Name;
  
    if (entityName.StartsWith(TablePrefix))
    {
        entityName = entityName.Substring(TablePrefix.Length);
    }
  
    if (plural)
    {
        entityName = StringUtil.ToPlural(entityName);
    }
    else
    {
        entityName = StringUtil.ToSingular(entityName);
    }
  
    return entityName;
}

public string GetBySuffix(ColumnSchemaCollection columns)
{
    System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
    for (int i = 0; i < columns.Count; i++)
    {
        if (i > 0) bySuffix.Append("And");
        bySuffix.Append(columns[i].Name);
    }
  
    return bySuffix.ToString();
}
#endregion

#region Template Overrides
// Assign an appropriate file name to the output.
public override string GetFileName()
{
    if (this.SourceTable != null)
    {
        return this.SourceTable.Name + "_Procedures.sql";
    }
    else
    {
        return base.GetFileName();
    }
}

// Override the OutputFile property and assign our specific settings to it.
[Category("2. Options")]
[FileDialog(FileDialogType.Save, Title="Select Output File", Filter="Query Files (*.sql)|*.sql|All Files (*.*)|*.*", DefaultExtension=".sql")]
public override string OutputFile
{
    get {return base.OutputFile;}
    set {base.OutputFile = value;}
}

protected override void OnPostRender(string result)
{
    if (this.AutoExecuteScript)
    {
        // execute the output on the same database as the source table.
        CodeSmith.BaseTemplates.ScriptResult scriptResult = CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.SourceTable.Database.ConnectionString, result, new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage));
        Trace.WriteLine(scriptResult.ToString());
    }
  
    base.OnPostRender(result);
}

private void cn_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
    Trace.WriteLine(e.Message);
}
public string GetDummySqlParameterStatement(string Prefix,ColumnSchema column,bool isOutput)
{
    string OriginalString = GetSqlParameterStatement(column, isOutput);
    OriginalString = OriginalString.Replace("@",Prefix);
    return OriginalString;
}
#endregion

</script>The Table On Which I Operated Is: 
CREATE TABLE [dbo].[CustomerFeedback](
            [CustomerFeedbackId] [int] IDENTITY(1,1) NOT NULL,
            [CustomerName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [CustomerFeedback] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [CustomerLocation] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [CustomerEmail] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [UserId] [int] NULL,
            [CreatedDatetime] [datetime] NULL,
            [LastAccessDatetime] [datetime] NULL,
            [Deleted] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CustomerFeedback_Deleted]  DEFAULT ('N'),
 CONSTRAINT [PK_CustomerFeedback] PRIMARY KEY CLUSTERED
(
            [CustomerFeedbackId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The Output Is:

CREATE PROCEDURE CustomerFeedback_Search
            @SearchUsingOR bit=null,
            @CustomerFeedbackId int= null ,
            @CustomerName varchar(100)= null ,
            @CustomerFeedback varchar(5000)= null ,
            @CustomerLocation varchar(250)= null ,
            @CustomerEmail varchar(100)= null ,
            @UserId int= null ,
            @CreatedDatetime datetime= null ,
            @LastAccessDatetime datetime= null ,
            @Deleted char(1)= null
AS
DECLARE @QueryString nvarchar(4000)
DECLARE @HasWhere bit

--Search Using OR/AND
DECLARE @SearchUsing varchar(5)
IF (@SearchUsingOR=1)
            SET @SearchUsing = ' OR ';
ELSE
            SET @SearchUsing=' AND ';

SET @QueryString = 'SELECT a.CustomerFeedbackId,a.CustomerName,a.CustomerFeedback,a.CustomerLocation,a.CustomerEmail,a.UserId,a.CreatedDatetime,a.LastAccessDatetime,a.Deleted FROM CustomerFeedback a '
SET @HasWhere=0

IF(@CustomerFeedbackId IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( CustomerFeedbackId = @DummyCustomerFeedbackId';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  CustomerFeedbackId = @DummyCustomerFeedbackId';
END

IF(@CustomerName IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( CustomerName = @DummyCustomerName';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  CustomerName = @DummyCustomerName';
END

IF(@CustomerFeedback IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( CustomerFeedback = @DummyCustomerFeedback';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  CustomerFeedback = @DummyCustomerFeedback';
END

IF(@CustomerLocation IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( CustomerLocation = @DummyCustomerLocation';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  CustomerLocation = @DummyCustomerLocation';
END

IF(@CustomerEmail IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( CustomerEmail = @DummyCustomerEmail';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  CustomerEmail = @DummyCustomerEmail';
END

IF(@UserId IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( UserId = @DummyUserId';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  UserId = @DummyUserId';
END

IF(@CreatedDatetime IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( CreatedDatetime = @DummyCreatedDatetime';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  CreatedDatetime = @DummyCreatedDatetime';
END

IF(@LastAccessDatetime IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( LastAccessDatetime = @DummyLastAccessDatetime';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  LastAccessDatetime = @DummyLastAccessDatetime';
END

IF(@Deleted IS NOT NULL)
BEGIN
IF(@HasWhere=0)
BEGIN
SET @QueryString = @QueryString + ' WHERE ( Deleted = @DummyDeleted';
SET @HasWhere =1;
END
ELSE
SET @QueryString = @QueryString +  @SearchUsing + '  Deleted = @DummyDeleted';
END


IF(@HasWhere=1)      
            BEGIN
                        SET @QueryString = @QueryString + ' )';
            END

PRINT @QueryString

EXECUTE SP_EXECUTESQL @QueryString,
N'@DummyCustomerFeedbackId int,@DummyCustomerName varchar(100),@DummyCustomerFeedback varchar(5000),@DummyCustomerLocation varchar(250),@DummyCustomerEmail varchar(100),@DummyUserId int,@DummyCreatedDatetime datetime,@DummyLastAccessDatetime datetime,@DummyDeleted char(1)',
@DummyCustomerFeedbackId=@CustomerFeedbackId,@DummyCustomerName=@CustomerName,@DummyCustomerFeedback=@CustomerFeedback,@DummyCustomerLocation=@CustomerLocation,@DummyCustomerEmail=@CustomerEmail,@DummyUserId=@UserId,@DummyCreatedDatetime=@CreatedDatetime,@DummyLastAccessDatetime=@LastAccessDatetime,@DummyDeleted=@Deleted;
--endregion

No comments:

Post a Comment