I would like to create a stored procedure in MySQL that took a list as argument. For example, say that I would like to be able to set multiple tags for an item in one call, then what I want to do is to define a procedure that takes the ID of the item and a list of tags to set. However, I can't seem to find any way to do this, there is no...
I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this: update myTable set Col1=@col1, Col2=@col2 where ID=@ID if @@rowcount = 0 insert into myTable (Col1, Col2) values (@col1, @col2) My logic behind writing it in this way is that the update will perform an im...
I took a look at the "Beginner's Guide to LINQ" post here on StackOverflow (http://stackoverflow.com/questions/8050/beginners-guide-to-linq), but had a follow-up question: We're about to ramp up a new project where nearly all of our database op's will be fairly simple data retrievals (there's another segment of the project which already...
What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to ...
I was looking for a good free debugger for STP (Stored Procedures) as I need to make changes to a few of them as a part of my recent project. The stored procedures are stored in independent '.sql' files and not in the code. A debugger would speed things up quite a it in my case. Could anyone recommend any tools that can be used for this...
I'm not quite sure if this is possible, or falls into the category of Pivot tables, but I figured I'd go to the pros to see. I have three basic tables: Card, Property, and CardProperty. Since cards do not have the same properties, and often multiple values for the same property, I decided to use the union table approach to store data in...
Assuming you can't use Linq for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hoc queries against the database (say, Sql Server for argument's sake)? ...
I'm running a long process stored procedure . I'm wondering if in a case of a timeout or any case of dis-connection with the database after initiating the call to the stored procedure, Is it still working and implementing the changes on the server? ...
Is it possible to call a stored prodcedure from another stored procedure asynchronously? Edit: Specifically I'm working with a DB2 database. ...
I currently work with an Oracle database and we use stored procedures for all our SQL queries. The problem I have is that we do not really having a coding standard for our packages. So what happens is that every developer has a different style (or in some cases no sense of style) in how they format there packages. Making them difficult t...
It is generally accepted that the use of cursors in stored procedures should be avoided where possible (replaced with set based logic etc). If you take the cases where you need to iterate over some data, and can do in a read only manner, are fast forward (read only forward) cursor more or less inefficient than say while loops? From my in...
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure. For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it. SQL Server 2005 is my only applicable limitatio...
When we create stored procedures in Visual Studio (with the extension .sql) the file encoding is automatically set to 'Unicode (UTF-8 with signature) - Codepage 65001'. This causes ours DBA's problems as their scripts don't like it. We have to manually go in and change them to 'Western European (windows) - Codepage 1252'. This only se...
Documentation is severely lacking on anything to do with stored procedures in mysql with PHP. I currently have a stored procedure that I call via PHP, how can I get the value of an out parameter? ...
One of my co-workers claims that even though the execution path is cached, there is no way parameterized SQL generated from an ORM is as quick as a stored procedure. Any help with this stubborn developer? Edit: I won't pick an answer but instead vote up all the great replies ...
As part of my integration strategy, I have a few SQL scripts that run in order to update the database. The first thing all of these scripts do is check to see if they need to run, e.g.: if @version <> @expects begin declare @error varchar(100); set @error = 'Invalid version. Your version is ' + convert(varchar, @version) +...
We're in the process of setting up a new framework and way of doing business for our new internal apps. Our current design dictates that all security logic should be handled by our database, and all information (and I mean all) will be going in and out of the database via stored procedures. The theory is, the data access layer requests ...
I have a web page that has I'm hooking up a stored procedure on. In this SQL Data Source, I have a parameter that I'm passing back to the SP of type int. ASP seems to want to default to int32 but the number won't get higher than 6. Is it ok to override the ASP default and put in 16 or will there be a conflict somewhere down the road...
Conventional wisdom states that stored procedures are always faster. So, since they're always faster, use them ALL THE TIME. I am pretty sure this is grounded in some historical context where this was once the case. Now, I'm not advocating that Stored Procs are not needed, but I want to know in what cases stored procs are necessary in m...
How do I call MySQL stored procedures from Perl? Stored procedure functionality is fairly new to MySQL and the MySQL modules for Perl don't seem to have caught up yet. ...