I'm studying for the MCTS 70-433 "Database Design" cert, and in the text that I'm studying, one of the self-tests has this question. You have a stored procedure named Get_NewProducts. You wish to insert the results of this stored procedure into the Production.Product table and output the INSERTED.* values using the OUTPUT ...
Hi All, I want to restore mysql database through stored procedure? Is it possible? Or can I copy dbfile and rename that folder? Please let me know if anyone needs more information to answer this question. Thanks in advance. Regards, Manasi ...
I have a string property that may or may not be null. I am passing it to the SP using this call: db.AddInParameter(InsertMessageDetailCommand, "MyParam", System.Data.DbType.String, this.myParam); the field is defined in the SP like this: @MyParam nvarchar(50) How can I change the SP to allow for null values, and if param value is...
SQL Newbie here, and I'm having a hell of a time finding what should be a simple code example to answer what I think is a simple question. I need to write a stored procedure that does three things in order: 1) Select rows from one table 2) Update rows in another table, using values from the results table in #1 3) Return the results tabl...
I want a SQL query to check if a given timestamp falls within the open hours of a business. These open hours change depending on the time of year (seasonal hours) and the business is closed for certain holidays. I have a list of these dates, although they are subject to change. What I want is a good structure for storing this informat...
Hi. So I want to retrieve data using stored procedures. Preferably via Linq2SQL. So I can do something like this (example): var productHistory = dbname.StoredProcedureMethod(value); foreach(var product in productHistory) { //stuff } This is something I'd like to be able to do in Visual Studio 2008 in .Net 3.5. It is possible wit...
I am working on a web project where I have to retrieve (let's say) employee records. In some cases I have to retrieve a single record by providing an EmployeeID. In other cases, I have to retrieve multiple employee records by providing a SectorID. This logic could be expanded to cover additional scenarios: get all employee records, get e...
I am trying to use a stored procedure in the entity framework that returns nothing. The stored procedure is purely for logging. I added a function (right click -> add -> function import) which works ONLY when the return value is set to one of the entities. When I change the return type to be Int32, Bool or nothing, or any other value ...
Should queries live inside the classes that need the data? Should queries live in stored procedures in the database so that they are reusable? In the first situation, changing your queries won't affect other people (either other code or people generating reports, etc). In the second, the queries are reusable by many and only exist in o...
Hi, This is a bit of code in one of the pages I am reviewing and trying to update. What I am trying to do is swap the inline query for stored procedures. This is the bit of code: numOfDelRec = 1 Do While (numOfDelRec > 0) cm.CommandText = "DELETE Folder WHERE FolderID IN (SELECT ch.FolderID FROM Folder ch LEFT JOIN Folder p...
I'm using asp.net and sql server. i have 2 tables: Categories and Products. in the products table i have categoryId as FK. what i want to do is: when i delete category from the category table, i want that all the products from that category will be deleted in the products table. how can this be done ( i prefer with store procedure but it...
In responses to this question, KM said if you are on or above SQL Server 2005, you can use IFs to have multiple queries in the same procedure and each will have an query plan saved for it (equiv to a procedure for each on older versions), see the article in my answer or this link to proper section: sommarskog.se/dyn-search-2005.html#...
Looking at all the answers to the question: "Are the days of stored procedures numbered". It appears as if some people are jumping in with strong opions based on what I perceive to be a narrow range of software development experiences. I would like to re-submit the question, but first I need help putting together a list of qualificatio...
I have written a complex query that will return me a list of IDs. Now I want to re-use this query so that I join the results with another query. For that I plan to put this into a Stored Proc or an UDF and then use it to insert into a temp table. Something like below 1) Put the query in a Stored Proc and insert it into temp table INSE...
Hi there, I have an old MySQL 4.1 database with a table that has a few millions rows and an old Java application that connects to this database and returns several thousand rows from this this table on a frequent basis via a simple SQL query (i.e. SELECT * FROM people WHERE first_name = 'Bob'. I think the Java application uses client si...
I am executing this EntityConnection entityConnection = (EntityConnection)context.Connection; EntityCommand command = entityConnection.CreateCommand(); command.CommandText = "Genesis.AL_Insert"; command.CommandType = CommandType.StoredProcedure; //Parametro de NombrePlantilla EntityPa...
I want to write an SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter. How to do this ? I can pass the user ids as a string separated by comma. So that i can use the select * from users where userid in (userids) ...
When executing: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 1; END; DECLARE EXIT HANDLER FOR SQLWARNING BEGIN ROLLBACK; SELECT 1; END; -- delete all users in the main profile table that are in the MaineU18 by email address DELETE FROM ap_form_1 WHERE element_5 ...
I am working with ASP.NET and SQL Server 2005. I know how to create a stored proceudre but i do not know what to place in a stored procedure when i need to make use of a QueryString. My SQL Statement in CODE = "SELECT * FROM TableName WHERE ID = '" + Request.QueryString("ID") + "'" Now what must i place in my stored procedure to get...
Given the following function: create or replace FUNCTION "GETADDRESSES" RETURN sys_refcursor IS address_cursor sys_refcursor; BEGIN OPEN address_cursor FOR SELECT * FROM Address; RETURN address_cursor; END; I would like to be able to make changes to this result set in Java and post the changes back to the data...