Dynamic Query Question

[PIEBALDconsult]

That would be another reason not to use stored procedures. Things like that are so much simpler when the SQL is in your DAL.

[Mycroft Holmes]

PIEBALDconsult wrote:

That would be another reason not to use stored procedures

Boo, hissss, brrrppp and other sundry rude noises. Oh wait we've been round this tree before.

Never underestimate the power of human stupidity RAH

[S Douglas]

PIEBALDconsult wrote:

That would be another reason not to use stored procedures. Things like that are so much simpler when the SQL is in your DAL.

I'll bite, this is easy in a proc. Seems like it would be way more difficult embedded in the DAL. I'm interested in how you would accomplish it?

EXEC sp_executesql @DYNA_SQL, N'@NUM_ROWS int out'

---

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

[PIEBALDconsult]

What does it do? :confused:

[S Douglas]

Pretty much the same thing that smcnulty2000 suggested doing in the second suggestion. sp_executesql allows for parameters to be fed in / out the dynamic SQL being executed. http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

---

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

[PIEBALDconsult]

Here are a couple of simple examples. Not using my usual data access classes. I prefer the ExecuteScalar, it was designed for this sort of thing.

        System.Data.IDbConnection dbc = new System.Data.SqlClient.SqlConnection
        (
            @"Server='localhost\\SQLEXPRESS'; Database='Rubbish'; Trusted\_Connection='True'"
        ) ;

        System.Data.IDbCommand cmd = dbc.CreateCommand() ;

        cmd.CommandText = "SELECT @voter\_id=Id FROM Account WHERE " + "Name = 'Two'" ;

        System.Data.IDbDataParameter prm = cmd.CreateParameter() ;

        prm.ParameterName = "@voter\_id" ;
        prm.DbType = System.Data.DbType.Int32 ;
        prm.Direction = System.Data.ParameterDirection.Output ;

        cmd.Parameters.Add ( prm ) ;

        dbc.Open() ;

        cmd.ExecuteNonQuery() ;

        int id = (int) prm.Value ;

        cmd.Parameters.Clear() ;
        cmd.CommandText = "SELECT Id FROM Account WHERE " + "Name = 'Three'" ;

        object o = cmd.ExecuteScalar() ;

        if ( o != System.DBNull.Value )
        {
            id = (int) o ;
        }

        dbc.Close() ;

[PIEBALDconsult]

Here's another take on it:

    public static T
    GetAccountId<T>
    (
        this System.Data.IDbConnection dbc
    ,
        string Filter
    ,
        params System.Tuple<string,object>\[\] Parameters
    )
    {
        T result = default(T) ;
        
        System.Data.IDbCommand cmd = dbc.CreateCommand() ;
        cmd.CommandText = "SELECT Id FROM Account WHERE " + Filter ;
        if ( Parameters != null )
        {
            foreach ( System.Tuple<string,object> p in Parameters )
            {
                System.Data.IDbDataParameter prm = cmd.CreateParameter() ;
                prm.ParameterName = p.Item1 ;
                prm.Value = p.Item2 ;
                cmd.Parameters.Add ( prm ) ;
            }
        }
        dbc.Open() ;
        object o = cmd.ExecuteScalar() ;
        if ( o != System.DBNull.Value )
        {
            result = (T) o ;
        }
        dbc.Close() ;
        return ( result ) ;
    }

[S Douglas]

Interesting, those are not very far off from I have used in code. Though most of my work is all database level work now (data warehousing, stored procs, SSIS, SSAS and SSRS), so I've come to use stored procs for everything. Just seems easier to me.

---

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

[PIEBALDconsult]

S Douglas wrote:

I've come to use stored procs for everything

They are rarely the right tool for the job.

[S Douglas]

PIEBALDconsult wrote:

They are rarely the right tool for the job.

No other tool suits the needs, :). I don't disagree with you sediments. However, in my world its all that exists.

---

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.