Database

Mycroft Holmes wrote:

I meant Tim's response...

V. wrote:

he DBA and developer and analyst and tester and ...

public class MyParam
    {
        public string name { get; set; }
        public string value { get; set; }
    }
    /// 
    /// Summary description for QueryContainer SGH
    /// 
    public class QueryContainer
    {
 
        string _query;
 
        public List parameterList = new List(); 
 
        public QueryContainer(string query) { _query = query; }
 
        public string Query
        {
            get
            {
                return _query;
            }
 
            set { _query = value;  }
        }
    }<pre>
 
The calling code:
 

<pre>
        public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");
 
            MyParam myParam = new MyParam();
 
            myParam.name = "@account";
            myParam.value = account.ToString();
 
            Instance.parameterList.Add(myParam);
 
            return Convert.ToInt32(ExecuteScaler(Instance, 1));
        } 
<pre>
 
The implementing code:
 
<pre>
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }
 
                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;
 
                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }
 
                    if (_useTransaction) { command.Transaction = _transaction; }
 
                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();
 
                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);
 
                        _iserror = true;
                    }
<pre>

public class MyParam
    {
        public string name { get; set; }
        public string value { get; set; }
    }
    /// <summary>
    /// Summary description for QueryContainer SGH
    /// </summary>
    public class QueryContainer
    {

        string _query;

        public List<myparam> parameterList = new List<myparam>(); 

        public QueryContainer(string query) { _query = query; }

        public string Query
        {
            get
            {
                return _query;
            }

            set { _query = value;  }
        }
    }

The calling code:

<pre>
        public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");

            MyParam myParam = new MyParam();

            myParam.name = "@account";
            myParam.value = account.ToString();

            Instance.parameterList.Add(myParam);

            return Convert.ToInt32(ExecuteScaler(Instance, 1));
        } 
<pre>

The implementing code:

<pre>
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }

                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }

                    if (_useTransaction) { command.Transaction = _transaction; }

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }

<pre>

SELECT DISTINCT 
    dw.we_System_Key, 
    w.we_System
FROM 
    dod
    INNER JOIN do_we_systems dw on d.dod = dw.dod
    INNER JOIN lkup_we_systems w on dw.we_System_Key = w.we_System_Key
WHERE 
    dw.is_deleted = 0 
AND 
(
    w.we_system != '' 
OR
    EXISTS
    (
        SELECT 1
        FROM users_rights ur
        INNER JOIN rights r ON r.rights_key = ur.rights_key
        INNER JOIN users usr ON usr.username = ur.username
        WHERE usr.username = 'RHale1'
        AND r.rights_code = 'Non-Standard Test'
    )
)
ORDER BY
    w.we_system
;

holdorf wrote:

I have a SQL query that is returning

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

Create Porocedure GetName
(
  @column nvarchar(50)
  @columnData nvarchar(50)
)
As
Begin 
   Declare @MyQuery nvarchar(300)

  set @column=   IF (@column IS NOT null AND @column ='TableA.SomeColumn') 
    Begin 
    Set @MyQuery = N'Select'+ TableA.[@column] + 'from dbo.TableA
        (where TableA.Column1 LIKE' + @columnData + '+ ''%'' OR' + @columnData + 'IS null)' 
     END         
End
EXEC sp_executesql;

CREATE PROC dbo.GetName
(
    @column        sysname,
    @columnData    nvarchar(50)
)
As
BEGIN
DECLARE @MyQuery nvarchar(max), @RealColumnName nvarchar(130);
    
    SET NOCOUNT ON;
    
    -- Verify that the column name exists in the table:
    SELECT
        @RealColumnName = QuoteName(name)
    FROM
        sys.columns
    WHERE
        object_id = OBJECT_ID('dbo.TableA')
    And
        name = @column
    ;
    
    If @RealColumnName Is Null
    BEGIN
        RAISERROR('Invalid column name: "%s"', 16, 1, @column);
        Return;
    END;
    
    -- Use sp_executesql to pass the parameter to the dynamic query as a parameter:
    -- https://msdn.microsoft.com/en-gb/library/ms188001.aspx
    SET @MyQuery = N'SELECT ' + @RealColumnName + N' FROM dbo.TableA WHERE (' + @RealColumnName + N' Like @columnData + N''%'' Or @columnData Is Null)';
    EXEC sp_executesql @MyQuery, N'@columnData nvarchar(50)', @columnData = @columnData;
END