Writing SQL strings - style and clarity

[Guest]

Hello,

Just wondering how you all deal with writing large SQL strings when coding
database functionailty. Some of the SQL strings can get quite long and it
looks a bit messy and hard to read in the code. Is there a good way to write
the SQL so it remains neat, clear, readable and manageable?

Also, being new to VS 2005, is there a way in which SQL commands can be
tested in the Query browser and then 'parcelled up' automatiallt as
individual functions available for use throughout the app?

Thanks for any views,
Best regards,
David Ross

 

[Hans Kesting]

Hello,

Just wondering how you all deal with writing large SQL strings when coding
database functionailty. Some of the SQL strings can get quite long and it
looks a bit messy and hard to read in the code. Is there a good way to write
the SQL so it remains neat, clear, readable and manageable?

Also, being new to VS 2005, is there a way in which SQL commands can be
tested in the Query browser and then 'parcelled up' automatiallt as
individual functions available for use throughout the app?

Thanks for any views,
Best regards,
David Ross

A better coding practice would be to put all those "sql strings" into
stored procedures, and call them (using parameters of course).
This way SqlServer can even precompile the sql-code, for better
performance.

Hans Kesting

 

[Guest]

A better coding practice would be to put all those "sql strings" into
stored procedures, and call them (using parameters of course).
This way SqlServer can even precompile the sql-code, for better
performance.

Hans Kesting

Thanks Hans!

That sounds like a good approach. I'll give it a shot!

Cheers,
David

 

[Stephen Ahn]

David++,

Hans' idea of using stored procs is a good one.
However, if you still must use long SQL from your app, one idea is to add
the SQL into a text file (eg. YourSQL.sql), then add the text file as an
embedded resource to your project. Then you can read the resource from your
code.

HTH,
Stephen

 

[Guest]

David++,

Hans' idea of using stored procs is a good one.
However, if you still must use long SQL from your app, one idea is to add
the SQL into a text file (eg. YourSQL.sql), then add the text file as an
embedded resource to your project. Then you can read the resource from your
code.

HTH,
Stephen

Thanks Stephen,

I'll look into that option as well.

Cheers,
David

 

[Chris Dunaway]

A better coding practice would be to put all those "sql strings" into
stored procedures, and call them (using parameters of course).
This way SqlServer can even precompile the sql-code, for better
performance.

You might wish to hold off on automatically putting all SQL queries in
stored procedures. There is some debate about this practice. I'm
still kind of middle of the road on it, but look at this article:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

As an alternative, what we have done is create a shared class called
SqlQuery with shared string constants that contain parameterized SQL
queries and then use that in our code (watch for typos):

Shared Class SqlQuery
Shared Const Query1 As String = "SELECT * FROM Table WHERE Column =
'ABC'"
End Class