Database

30-Apr-15 12:24

I have a SQL query that is returning

modified 3-May-15 13:07pm.

PIEBALDconsult30-Apr-15 20:33

PIEBALDconsult

30-Apr-15 20:33

You probably have an operator-precedence problem due to combining OR with AND -- I can never get it right myself, so I always use parentheses.

I also recommend not using sub-queries, especially with IN -- try a JOIN instead.

Stephen Holdorf1-May-15 3:54

1-May-15 3:54

The only problem I see is how do I add the user and rights tables in to the query? There are no keys to access those tables.

Stephen Holdorf20-May-15 4:27

20-May-15 4:27

I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code:

The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code:

Class with the parameters broken out:

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>

Stephen Holdorf20-May-15 4:39

Re: Help with a SQL query with all inappropriate information removed

20-May-15 4:39

I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code:

The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code:

Class with the parameters broken out:

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>

Richard Deeming1-May-15 4:39

Richard Deeming

1-May-15 4:39

Try something like this:

SQL

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
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Test sol

Eddy Vluggen4-May-15 9:20

Eddy Vluggen

4-May-15 9:20

holdorf wrote:

I have a SQL query that is returning

From where?

This looks weird like this; where did the rest of the question go?

Bastard Programmer from Hell Suspicious | :suss:

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

Dynamically Add Column Name

MadDashCoder29-Apr-15 4:44

MadDashCoder

29-Apr-15 4:44

Hi all, I have a function that receives a column name and it will return this column name when the function is called, if the column name is not null.

This function will be called in a stored proc inside of the Select statement. The function will return the name of a column entered as input and it will be used inside of the Select statement.

The following is my function

Create function GetName(@C nvarchar(50))
Returns nvarchar(50)
As
Begin 
   Declare @Col_Name nvarchar(50)<br />
     IF (@Col IS NOT null) 
    Begin 
    Set @ColumName = @Col<br />
     END 
        Return @Col_Name
End

I expect this function to return any input a user enters but it doesn't return anything and I'm not getting any errors. Also how do I call this function in my stored proc so that the column name is added to the select statement automatically. Any help is great, thanks.

modified 29-Apr-15 10:34am.

Sascha Lefèvre29-Apr-15 6:11

Sascha Lefèvre

29-Apr-15 6:11

You can't call that function in a select-statement in order to modify the column-list of the select-statement.

You need to build your query string dynamically. See here:
https://msdn.microsoft.com/en-us/library/ms188001.aspx[^]
http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

MadDashCoder29-Apr-15 14:43

MadDashCoder

29-Apr-15 14:43

Hi, thank you all for responding. I decided to use a stored proc instead of a function and used Dynamic SQL to write my query.

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;

I get no errors but it does not work. Any help will be greatly appreciated, thanks.

modified 29-Apr-15 19:53pm.

Corporal Agarn30-Apr-15 2:48

Corporal Agarn

30-Apr-15 2:48

The statement "EXEC sp_executesql;" should include your variable (@MyQuery)

Mongo: Mongo only pawn... in game of life.

Richard Deeming30-Apr-15 2:52

Richard Deeming

30-Apr-15 2:52

Try this:

SQL

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

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Corporal Agarn30-Apr-15 7:38

Corporal Agarn

30-Apr-15 7:38

Now that is a good explination

Mongo: Mongo only pawn... in game of life.

Corporal Agarn29-Apr-15 6:37

Corporal Agarn

29-Apr-15 6:37

You made a typo in your explanation code, at least I hope it is not in your actual function.
@Col_Name is not defined. @ColumName is.

Mongo: Mongo only pawn... in game of life.