How do YOU write sql statements?

[kebo]

This is not about the syntax in writing sql, rather the coding techniques.

In a typical sql statement column names and table names are required. Two possible way to write the statement would be by hardcoding the names in the statement text or by using constanst....

SELECT thisColumn, thatColumn FROM myTable WHERE anotherColumn = something

OR

Const THIS_COLUMN = "thisColumn"
Const THAT_COLUMN = "thatColumn"
Const MY_TABLE = "myTable"
Const ANOTHER_COLUMN = "anotherColumn"
SelectStatement = string.format("SELECT {0}, {1} FROM {2} WHERE {3} = something",THIS_COLUMN, THAT_COLUMN, MY_TABLE, ANOTHER_COLUMN)

I'm fairly very young when it comes to database programming, and am curious about how YOU handle specifying the names in the sql statements. I don't see that either method is not correct, but one way (or another) may have its benefits. What say you?

Again, this is not a post about how something gets done, or how to do something, I'm just curious about popular methods is all.
thanks

kevin

[weirddemon]

Asking this in the Database forum might get some better feedback.

I've only used SQL databases on occasion, but I always type the column name into the SELECT statement, instead of using variables.

I can't think of a reason why doing the opposite would be beneficial. Hm...

[jmcilhinney]

The only reason that you might use the second option would be if you wanted to use the same value in multiple places.

[weirddemon]

The only reason that you might use the second option would be if you wanted to use the same value in multiple places.

That's what I mean. It wouldn't really make anything easier or have a specific purpose, other than what was mentioned.

[stateofidleness]

Was scolded.. err, I mean "taught" very early on this forum to use parameters and it actually is much nicer to code:

Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee WHERE FirstName = @fn", con)
        cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value = "Joe"

It allows for better dynamics and less margin for error. As for the SELECT part (the table names) I usually use variable names that hold the table names. It lets me/the user define them much easier than a hard-coded value.

[gautamshaw]

everyone has got their own style of coding.....

[jmcilhinney]

Was scolded.. err, I mean "taught" very early on this forum to use parameters and it actually is much nicer to code:

Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee WHERE FirstName = @fn", con)
        cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value = "Joe"

It allows for better dynamics and less margin for error. As for the SELECT part (the table names) I usually use variable names that hold the table names. It lets me/the user define them much easier than a hard-coded value.

This question isn't about the values though; it's about the identifiers, i.e. the table and column names rather than the field values.

[stateofidleness]

This question isn't about the values though; it's about the identifiers, i.e. the table and column names rather than the field values.

right, right.

just giving me some insight on how I've been doing my sql statements as a whole

SELECT variableField1, variableField2 FROM variableTable WHERE ....

as far as using constants though, seems like it would just "bloat" the code. since it won't change anyway, you might as well just hard-code it into the statement. at least that way you know what the field name is right away instead of referencing your list of constants at the top of your code.

[Hack]

Moved To Database Development

[GaryMazzone]

Personally I write out the column name implicitly every time. When someone is looking at my SQL statements I want to ensure they know exactly what I'm looking for in the statement.

[IanS]

I could see a benefit in using constant declaration BUT ONLY if you expect to be changing the table names in future versions of your application. For example if you referred to a table many times in your code then if you changed the table name then you'd only need to edit the const.

But I can't think of any case where I've ever changed the name of a table or field. So putting the real table names in the actual sql is easier to read. Why make source harder to read.