Dynamic SQL instead of stored procs?

[Ronald S. Cook]

What do you guys think of abandoning stored procedures and writhing the SQL
in code?

I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?

Is there a list of pros/cons someone can forward?

I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does not
concern LINQ.

Thanks very much,
Ron

 

[Jon Skeet [C# MVP]]

What do you guys think of abandoning stored procedures and writhing the
SQL in code?

It's been discussed quite a lot - most recently in this thread:

http://groups.google.com/group/micr..._frm/thread/caa539ce6f765f9f/b48da4f408f69806

Jon

 

[Nicholas Paldino [.NET/C# MVP]]

Ron,

In addition to the thread that Jon pointed out, I would say that in the
case of some sort of persistence layer, where you have a defined way of
getting values to be stored to the database, dynamic sql would be a good
call in this case, as it would be tedious to write all the stored procedures
for all the different types you are persisting in your database.

However, if you are going to perform well-defined operations in your
database, beyond the simple CRUD operations, I would say to go with stored
procedures, as writing out the sql to perform these operations in code is
just redundant, and you don't get any checking against the statement until
runtime when you try to execute it. At least if you have the stored
procedures in SQL Server, you can see if you have some sort of error before
you run the code.

 

[Ronald S. Cook]

I just did a lttle test from code against a 3 million record database.

Test 1: calling proc
Test 2: text "SELECT * FROM MyTable"

Test 1 took 55 seconds. Test 2 gave me a System.OutOfMemoryException error
after 55 seconds.

Any thoughts on that? Everything was running local on my PC.

Thanks,
Ron

Ron,

In addition to the thread that Jon pointed out, I would say that in the
case of some sort of persistence layer, where you have a defined way of
getting values to be stored to the database, dynamic sql would be a good
call in this case, as it would be tedious to write all the stored
procedures for all the different types you are persisting in your
database.

However, if you are going to perform well-defined operations in your
database, beyond the simple CRUD operations, I would say to go with stored
procedures, as writing out the sql to perform these operations in code is
just redundant, and you don't get any checking against the statement until
runtime when you try to execute it. At least if you have the stored
procedures in SQL Server, you can see if you have some sort of error
before you run the code.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

What do you guys think of abandoning stored procedures and writhing the
SQL in code?

I'm a little new to the debate and not sure I totally understand. From
my command object, I can just select the type to be "text" instead of
"stored procedure", type my SQL there and it performs just the same?
When did writing the SQL in code become just as good as using procs?

Is there a list of pros/cons someone can forward?

I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does
not concern LINQ.

Thanks very much,
Ron

 

[Jon Skeet [C# MVP]]

I just did a lttle test from code against a 3 million record database.

Test 1: calling proc
Test 2: text "SELECT * FROM MyTable"

Test 1 took 55 seconds. Test 2 gave me a System.OutOfMemoryException error
after 55 seconds.

Any thoughts on that? Everything was running local on my PC.

Did the proc actually return the data to the client?

 

[Nicholas Paldino [.NET/C# MVP]]

Ronald,

How are you calling the stored procedure and the dynamic sql from the
client?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I just did a lttle test from code against a 3 million record database.

Test 1: calling proc
Test 2: text "SELECT * FROM MyTable"

Test 1 took 55 seconds. Test 2 gave me a System.OutOfMemoryException
error after 55 seconds.

Any thoughts on that? Everything was running local on my PC.

Thanks,
Ron

Ron,

In addition to the thread that Jon pointed out, I would say that in
the case of some sort of persistence layer, where you have a defined way
of getting values to be stored to the database, dynamic sql would be a
good call in this case, as it would be tedious to write all the stored
procedures for all the different types you are persisting in your
database.

However, if you are going to perform well-defined operations in your
database, beyond the simple CRUD operations, I would say to go with
stored procedures, as writing out the sql to perform these operations in
code is just redundant, and you don't get any checking against the
statement until runtime when you try to execute it. At least if you have
the stored procedures in SQL Server, you can see if you have some sort of
error before you run the code.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

What do you guys think of abandoning stored procedures and writhing the
SQL in code?

I'm a little new to the debate and not sure I totally understand. From
my command object, I can just select the type to be "text" instead of
"stored procedure", type my SQL there and it performs just the same?
When did writing the SQL in code become just as good as using procs?

Is there a list of pros/cons someone can forward?

I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does
not concern LINQ.

Thanks very much,
Ron

 

[Frank Rizzo]

What do you guys think of abandoning stored procedures and writhing the SQL
in code?

I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?

Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does not
concern LINQ.

In addition to what the other fellas said, an extra thing to consider is
whether your shop is using some type of ORM (such as NHibernate or
Ibatis.NET). In that case, it is typical to use inline SQL.

I prefer stored procedures, especially for medium to large projects. The
reason is maintanance. 1 times out of 2, you can fix a bug by sending
the customer a modified SQL script. You'd have to send a modified
binary, if your SQL was inline.

For projects where I am forced to use inline SQL, I typically have a
Queries subfolder in my application folder where I store all/most
queries each in a separate file. At runtime, I just read in the
particular file and execute the SQL within. If a bug crops up in the
SQL code, I can just change the file without having to recompile the
application.

Regards

 

[Samuel R. Neff]

I prefer sql in code because we often have many statements that are
similar but not exactly the same. In code it's easy to build a SQL
statement with conditional parts. In a proc you can do it, but it's
more confusing and you lose most of the advantages of using a stored
proc.

HTH,

Sam

 

[Mr. Arnold]

What do you guys think of abandoning stored procedures and writhing the
SQL in code?

I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?

Is there a list of pros/cons someone can forward?

Whether one uses a SQL Stored Procedure or in-line Dynamic SQL is based on
the needs of the solution. I have used both in a solution based on the needs
of the environment said application was to be executed. Or I have used one
or the other in a solution based on the environment said application was to
be executed.

https://msdn2.microsoft.com/en-us/library/ms973918.aspx
http://decipherinfosys.wordpress.co...d-procedures-vs-dynamic-sql-generated-by-orm/

 

[Moty Michaely]

Hi,

For projects where I am forced to use inline SQL, I typically have a
Queries subfolder in my application folder where I store all/most
queries each in a separate file. At runtime, I just read in the
particular file and execute the SQL within. If a bug crops up in the
SQL code, I can just change the file without having to recompile the
application.

You risk code injection calls. You must had security layer to this
folder or otherwise bad things can happen.

I would suggest using Stored Procesdures since it's good for
maintanance (Deployment, optimization etc.) and the main reason (for
me) is security. Using inline SQL (or dynamic) you can't force
execution denial. Stored Procedures security (In SQL Server 2000 and
up) can be managed quite roughly, and you can modulate the calls for
security modulation.

Moty.