February 3, 2012 at 6:19 am
A convetion from monochrome monitors age, from sure 😀
identation, syntax highlight, alignment, casing...
Its all "personal taste".
February 3, 2012 at 7:15 am
tsceurman (2/3/2012)
Until recently, I was using AS. I switched to using =, exactly because of the readability. Of course, I am also rather..... particular when it comes to formatting anyway.For me, when writing INSERT statements, I alias every column in the SELECT statement with the column name it corresponds to.
INSERT INTO [schema].
(
col1
, col2
)
SELECT
col1 = t.colA
, col2 = t.colB
FROM
[schema].[table2] AS t
;
I am also the sort of person who has to align all the variable names and data types, and put them all together under one DECLARE statement at the beginning of the procedure, and has to align the CASE & END, WHEN & ELSE, and each of the THEN statements.
I know, too picky probably, but it makes things much easier for me to scan through.
Nice formatting but that makes it difficult to squeeze into one line when assigning your SQL to a executable variable when programming with .Net or other languages.
What would you do?
Declare SQLCode as string
SQLCode = "INSERT INTO [schema].
"
SQLCode = SQLCode & "("
SQLCode = SQLCode & " col1"
SQLCode = SQLCode & " , col2"
etc.
That code would end up like this for me:
SQLCode = "INSERT INTO [schema].
(col1, col2) SELECT t.colA, t.colB FROM [schema].[table2] AS t;"
February 3, 2012 at 9:41 am
cengland0 (2/3/2012)
tsceurman (2/3/2012)
Until recently, I was using AS. I switched to using =, exactly because of the readability. Of course, I am also rather..... particular when it comes to formatting anyway.For me, when writing INSERT statements, I alias every column in the SELECT statement with the column name it corresponds to.
INSERT INTO [schema].
(
col1
, col2
)
SELECT
col1 = t.colA
, col2 = t.colB
FROM
[schema].[table2] AS t
;
I am also the sort of person who has to align all the variable names and data types, and put them all together under one DECLARE statement at the beginning of the procedure, and has to align the CASE & END, WHEN & ELSE, and each of the THEN statements.
I know, too picky probably, but it makes things much easier for me to scan through.
Nice formatting but that makes it difficult to squeeze into one line when assigning your SQL to a executable variable when programming with .Net or other languages.
What would you do?
Declare SQLCode as string
SQLCode = "INSERT INTO [schema].
"
SQLCode = SQLCode & "("
SQLCode = SQLCode & " col1"
SQLCode = SQLCode & " , col2"
etc.
That code would end up like this for me:
SQLCode = "INSERT INTO [schema].
(col1, col2) SELECT t.colA, t.colB FROM [schema].[table2] AS t;"
What? Not using stored procedures!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 3, 2012 at 9:49 am
Gary Varga (2/3/2012)
cengland0 (2/3/2012)
tsceurman (2/3/2012)
Until recently, I was using AS. I switched to using =, exactly because of the readability. Of course, I am also rather..... particular when it comes to formatting anyway.For me, when writing INSERT statements, I alias every column in the SELECT statement with the column name it corresponds to.
INSERT INTO [schema].
(
col1
, col2
)
SELECT
col1 = t.colA
, col2 = t.colB
FROM
[schema].[table2] AS t
;
I am also the sort of person who has to align all the variable names and data types, and put them all together under one DECLARE statement at the beginning of the procedure, and has to align the CASE & END, WHEN & ELSE, and each of the THEN statements.
I know, too picky probably, but it makes things much easier for me to scan through.
Nice formatting but that makes it difficult to squeeze into one line when assigning your SQL to a executable variable when programming with .Net or other languages.
What would you do?
Declare SQLCode as string
SQLCode = "INSERT INTO [schema].
"
SQLCode = SQLCode & "("
SQLCode = SQLCode & " col1"
SQLCode = SQLCode & " , col2"
etc.
That code would end up like this for me:
SQLCode = "INSERT INTO [schema].
(col1, col2) SELECT t.colA, t.colB FROM [schema].[table2] AS t;"
What? Not using stored procedures!!!
...not speaking about the absence of += and string.Format() which would make it easier to read. 😉
February 3, 2012 at 10:00 am
I dislike using dynamic SQL but for .net I sugest using @
String sqlcode = @" /*this
ill keep
the break lines
no need to fancy concatenations */
select *
from
mytable
where
myfavoritesqlresource = 'SSC';
";
😎
February 3, 2012 at 10:40 am
jcb (2/3/2012)
I dislike using dynamic SQL but for .net I sugest using @String sqlcode = @" /*this
ill keep
the break lines
no need to fancy concatenations */
select *
from
mytable
where
myfavoritesqlresource = 'SSC';
";
😎
How embarrassing. I've been using .net for so many years now and didn't know you could do that. This makes all the difference in the world. I plan on trying this early next week.
February 3, 2012 at 10:47 am
Gary Varga (2/3/2012)
cengland0 (2/3/2012)
What? Not using stored procedures!!!
Good question and one I'm glad you asked.
I'm the DBA on several databases, others I'm not. For those that I'm not the DBA, I have to fill out change control tickets, wait 10 days, and pay another department to make the changes to a stored procedure for me.
If I embed the SQL into the .Net code, I can avoid having to go through the change control process to get the stored procedure updated. When you update a stored procedure that requires additional parameters, you need to also update your .net application at the same time or the new version of the software will fail. These are two different groups that manage two separate sets of servers and it's not always easy to coordinate those promotions from development to production when talking about both a web server and SQL server update at the same time.
So, to make this go smoothly, it's easier to coordinate the promotion of the .Net application and not have to worry about other database dependencies.
February 3, 2012 at 10:55 am
jcb (2/3/2012)
I dislike using dynamic SQL but for .net I sugest using @String sqlcode = @" /*this
ill keep
the break lines
no need to fancy concatenations */
select *
from
mytable
where
myfavoritesqlresource = 'SSC';
";
😎
I just discovered this is a C# feature -- not VB.net. Do you know of a way to do this with VB.net too?
February 3, 2012 at 11:21 am
You are right, no verbatim literal strings to VB.net...sorry
But you can use:
Dim s as String _
= "string1" _
& "string2" _
& "string3"
😛
February 3, 2012 at 11:57 am
cengland0 (2/3/2012)
Gary Varga (2/3/2012)
cengland0 (2/3/2012)
What? Not using stored procedures!!!Good question and one I'm glad you asked.
I'm the DBA on several databases, others I'm not. For those that I'm not the DBA, I have to fill out change control tickets, wait 10 days, and pay another department to make the changes to a stored procedure for me.
If I embed the SQL into the .Net code, I can avoid having to go through the change control process to get the stored procedure updated. When you update a stored procedure that requires additional parameters, you need to also update your .net application at the same time or the new version of the software will fail. These are two different groups that manage two separate sets of servers and it's not always easy to coordinate those promotions from development to production when talking about both a web server and SQL server update at the same time.
So, to make this go smoothly, it's easier to coordinate the promotion of the .Net application and not have to worry about other database dependencies.
This is nearly the perfect example of the 'other side of the fence' that most DBAs have to deal with when they are trying to get their developers onto Procs and to STAY there. You're lucky the DBAs haven't shut off datareader. Of course it takes coordination and the like. Ease <> Security!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2012 at 12:36 pm
cengland0 (2/3/2012)
Gary Varga (2/3/2012)
cengland0 (2/3/2012)
What? Not using stored procedures!!!Good question and one I'm glad you asked.
I'm the DBA on several databases, others I'm not. For those that I'm not the DBA, I have to fill out change control tickets, wait 10 days, and pay another department to make the changes to a stored procedure for me.
If I embed the SQL into the .Net code, I can avoid having to go through the change control process to get the stored procedure updated. When you update a stored procedure that requires additional parameters, you need to also update your .net application at the same time or the new version of the software will fail. These are two different groups that manage two separate sets of servers and it's not always easy to coordinate those promotions from development to production when talking about both a web server and SQL server update at the same time.
So, to make this go smoothly, it's easier to coordinate the promotion of the .Net application and not have to worry about other database dependencies.
You do not have to change .NET code if your new sproc parameters have default values that make them compatible with the old version.
If you need to use the new parameters - which is why they were added, I guess - you have to make the change, anyway.
February 3, 2012 at 12:46 pm
jcb (2/3/2012)
You are right, no verbatim literal strings to VB.net...sorryBut you can use:
Dim s as String _
= "string1" _
& "string2" _
& "string3"
😛
if you are using 3.5 Framework or above(or maybe if you include a reference to teh xml namespace, not sure offhand?), you can use an xml tag:
Dim sql as String
sql = <xml>
DECLARE @cmd VARCHAR(max)
SET @cmd = ''
SELECT @cmd = @cmd + 'IF NOT EXISTS(SELECT object_id(''[dbo].' + QUOTENAME(o.Name)
+ ''')) ALTER SCHEMA dbo TRANSFER ' + QUOTENAME(s.Name) + '.' + QUOTENAME(o.Name)
+ ' ELSE RAISERROR (''Cannot Change Owner on Object ' + QUOTENAME(s.Name) + '.' + QUOTENAME(o.Name)
+ ' Because the destination object ' + QUOTENAME('dbo') + '.' + QUOTENAME(o.Name)
+ ' Already Exists.'', 16, 1) ;'
+ CHAR(13) + CHAR(10)
FROM sys.Objects o
INNER JOIN sys.Schemas s
ON o.schema_id = s.schema_id
WHERE s.Name NOT IN ( 'dbo', 'sys' )
</xml>
only wierdness: you have to html escape lessthan/greater than symbols.
Lowell
February 6, 2012 at 5:39 am
cengland0 (2/3/2012)
jcb (2/3/2012)
I dislike using dynamic SQL but for .net I sugest using @String sqlcode = @" /*this
ill keep
the break lines
no need to fancy concatenations */
select *
from
mytable
where
myfavoritesqlresource = 'SSC';
";
😎
I just discovered this is a C# feature -- not VB.net. Do you know of a way to do this with VB.net too?
Obviously, I don't have to live your development life but one thing makes me shudder: SQL Injection!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 6, 2012 at 5:57 am
SQL Injection and Performance are 2 items from a wide list why I hate dynamic sql.
Some app use, at least, parameterized dynamic sql and it blocks most problems with SQL inject.
A example for C#:
conn.Open();
cmd.CommandText = @"UPDATE mytable SET
mycolumn = @myvariable
WHERE myid = @myid";
cmd.Parameters.AddWithValue("@myid", myobject.myid);
cmd.Parameters.AddWithValue("@mvariable", myobject.myproperty);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
February 6, 2012 at 6:12 am
jcb (2/6/2012)
SQL Injection and Performance are 2 items from a wide list why I hate dynamic sql.Some app use, at least, parameterized dynamic sql and it blocks most problems with SQL inject.
A example for C#:
conn.Open();
cmd.CommandText = @"UPDATE mytable SET
mycolumn = @myvariable
WHERE myid = @myid";
cmd.Parameters.AddWithValue("@myid", myobject.myid);
cmd.Parameters.AddWithValue("@mvariable", myobject.myproperty);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
Yes, that's exactly how I pass my parameters. It takes care of the SQL injection problems automatically. I'm not aware of any current methods for injecting SQL code by using this method. If anyone else is, please show me how and I might change the way I create applications in the future.
Another benefit of putting your code into the .Net application instead of the stored procedures is that you can obfuscate the code so it cannot be stolen as easily. Imagine selling an expensive enterprise application and then having it easily copied because all your SQL code is embedded in easy to read stored procedures.
Viewing 15 posts - 46 through 60 (of 92 total)
You must be logged in to reply to this topic. Login to reply