Search Results

Search found 1907 results on 77 pages for 'emergency procedures'.

Page 26/77 | < Previous Page | 22 23 24 25 26 27 28 29 30 31 32 33  | Next Page >

  • Pass in Dynamic number of parameters to a stored procedure

    - by xrum
    Hi, I have a function in my .NET application, that needs to do a search of an unknown number of parameters. for example: select * from tbl where x=1 or x=2 or x=3 or x=4 is it possible to do in .NEt and SQL? how do i go about creating dynamic parameters in .NET (I was thinking doing it with a loop) but then how do i declare them in my stored procedure? does sql have arrays? please help. thank you!

    Read the article

  • SQL Server stored procedure in multi threaded environments

    - by Shamika
    Hi, I need to execute some Sql server stored procs in a thread safe manner. At the moment I'm using software locks (C# locks) to achieve this but wonder what kind of features provided by the Sql server itself to achieve thread safety. It seems to be there are some table and row locking features built in to Sql server. Also from a performance perspective what is best approach? Software locks? Or Sql Server built in locks? Thanks, Shamika

    Read the article

  • MySQL stored procedure to INSERT DELAYED but CREATE TABLE first if needed?

    - by dkamins
    I'm planning on doing a lot of INSERT DELAYED into MyISAM tables. But the tables might not exist yet. Let's say e.g. a new table will exist for each day. So instead of detecting absence of table in my client and creating then retrying the insert, it seems like a good case for a stored procedure ("function"). Is this possible, and what would it look like? Are there any downsides to this approach?

    Read the article

  • create procedure fails!?

    - by Mark
    Hi, when trying to create a simple procedure in mysql 5.1.47-community it fails everytime i've tried everything! even simple things like this! DELIMITER // CREATE PROCEDURE two () begin SELECT 1+1; end; //

    Read the article

  • Does Anyone Still Prefer N-Tier Architecture After Having *Shipped* an MVC Application?

    - by Jim G.
    Other SO threads have asked people if they prefer N-Tier or MVC architecture. I'm not looking to continue that debate on this thread. I'm looking for something more specific. My Question: Does Anyone Still Prefer N-Tier Architecture After Having Shipped an MVC Application? Reason for My Question: Before I shipped an MVC web application, I wasn't convinced that it was superior to N-Tier Architecture. Specifically, if better unit testing was the only obvious benefit of MVC, then I saw no reason to switch gears and adopt a new architecture. But after having shipped an MVC application, I can see many benefits (which have been enumerated on other threads).

    Read the article

  • MySql stored procedure not found in PHP

    - by kaupov
    Hello, I have a trouble with MySql stored procedure that calls itself recursively using PHP (CakePHP). Calling it I receive following error: SQL Error: 1305: FUNCTION dbname.GetAdvertCounts does not exist The procedure itself is following: delimiter // DROP PROCEDURE IF EXISTS GetAdvertCounts// CREATE PROCEDURE GetAdvertCounts(IN category_id INT) BEGIN DECLARE no_more_sub_categories, advert_count INT DEFAULT 0; DECLARE sub_cat_id INT; DECLARE curr_sub_category CURSOR FOR SELECT id FROM categories WHERE parent_id = category_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_sub_categories = 1; SELECT COUNT(*) INTO advert_count FROM adverts WHERE category_id = category_id; OPEN curr_sub_category; FETCH curr_sub_category INTO sub_cat_id; REPEAT SELECT advert_count + GetAdvertCounts(sub_cat_id) INTO advert_count; FETCH curr_sub_category INTO sub_cat_id; UNTIL no_more_sub_categories = 1 END REPEAT; CLOSE curr_sub_category; SELECT advert_count; END // delimiter ; If I remove or comment out the recursive call, the procedure is working. Any idea what I'm missing here? The categories are 2 level deep.

    Read the article

  • Stored procedure or function expects parameter which is not supplied

    - by user2920046
    I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error Procedure or function 'SHOWuser' expects parameter '@userID', which was not supplied. My stored procedure is called "SHOWuser". I have checked it thoroughly and no parameters is missing. My code is: public void SHOWuser(string userName, string password, string emailAddress, List preferences) { SqlConnection dbcon = new SqlConnection(conn); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = dbcon; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "SHOWuser"; cmd.Parameters.AddWithValue("@userName", userName); cmd.Parameters.AddWithValue("@password", password); cmd.Parameters.AddWithValue("@emailAddress", emailAddress); dbcon.Open(); int i = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Parameters.Clear(); cmd.CommandText = "tbl_pref"; foreach (int preference in preferences) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i)); cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference)); cmd.ExecuteNonQuery(); } } catch (Exception) { throw; } finally { dbcon.Close(); } and the stored procedure is: ALTER PROCEDURE [dbo].[SHOWuser] -- Add the parameters for the stored procedure here ( @userName varchar(50), @password nvarchar(50), @emailAddress nvarchar(50) ) AS BEGIN INSERT INTO tbl_user(userName,password,emailAddress) values(@userName,@password,@emailAddress) select tbl_user.userID,tbl_user.userName,tbl_user.password,tbl_user.emailAddress, stuff((select ',' + preferenceName from tbl_pref_master inner join tbl_preferences on tbl_pref_master.preferenceID = tbl_preferences.preferenceID where tbl_preferences.userID=tbl_user.userID FOR XML PATH ('')),1,1,' ' ) AS Preferences from tbl_user SELECT SCOPE_IDENTITY(); END Pls help, Thankx in advance...

    Read the article

  • SQL Stored Procedure fired from C# Code-Behind not working on UPDATE

    - by CSSHell
    I have a stored procedure called from a C# code-behind. The code fires but the update command does not get performed. The stored procedure, if run directly, works. I think I am having a brain fart. Please help. :) CODEBEHIND protected void btnAbout_Click(object sender, EventArgs e) { SqlConnection myConnection = new SqlConnection(strConnection); SqlCommand myCommand = new SqlCommand("spUpdateCMSAbout", myConnection); myConnection.Open(); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@AboutText", SqlDbType.NVarChar, -1).Value = txtAbout.Text.ToString(); myCommand.ExecuteNonQuery(); myConnection.Close(); } STORED PROCEDURE ALTER PROCEDURE fstage.spUpdateCMSAbout ( @AboutText nvarchar(max) ) AS BEGIN SET NOCOUNT ON; UPDATE fstage.staticCMS SET About = @AboutText; END HTML <asp:Button ID="btnAbout" runat="server" Text="Save" CausesValidation="False" onclick="btnAbout_Click" UseSubmitBehavior="False" /> C# .NET 4.0

    Read the article

  • Unknown Column 'template' in where clause, while using a stored procedure.

    - by sai
    Hello, I have a stored procedure, which has got executed without any errors, but gives me an error "#1054: Unknown column 'templateName' in where clause" when I run it. The stored procedure is delimiter // DROP PROCEDURE getData// CREATE DEFINER=root@localhost PROCEDURE getData(IN templateName VARCHAR(45),IN templateVersion VARCHAR(45),IN userId VARCHAR(45)) BEGIN set @version = CONCAT("SELECT 'saveOEMsData_answersVersion' FROM saveOEMsData where 'saveOEMsData_templateName' = ",templateName," and 'saveOEMsData_templateVersion' = ",templateVersion," and 'saveOEMsData_userId'= ",userId); PREPARE s1 from @version; EXECUTE S1; END // delimiter ; Now I call it using "call getData('templateName','1','285');". And whenever I call it, I get the mentioned error. What could the problem be?? It surely is syntactical, I have been reading the mysql manuals for 2 days and have come out without!! Any help would be great!! Thanks

    Read the article

  • how to solve syntax error in procedure in mysql?

    - by Shooter
    Hi all, i am executing a procedure in mysql, procedure is- delimiter $$ create procedure getFacility (in id int, out MyFacility VARCHAR(200)) begin select Facility into MyFacility from facilities where FacilityID = id ; end $$ delimiter ; and it is giving error below- Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end $$ delimiter' at line 1 (0 ms taken) how to solve this error? Thanks, Yugal

    Read the article

  • Querying using table-valued parameter

    - by antmx
    I need help please with writing a sproc, it takes a table-valued parameter @Locations, whose Type is defined as follows: CREATE TYPE [dbo].[tvpLocation] AS TABLE( [CountryId] [int] NULL, [ResortName] [nvarchar](100) NULL, [Ordinal] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [Ordinal] ASC )WITH (IGNORE_DUP_KEY = OFF) ) @Locations will contain at least 1 row. Each row WILL have a non-null CountryId, and MAY have a non-null ResortName. Each row will have a unique Ordinal, the first being 0. The combinations of CountryId and ResortName in @Locations will be unique. The sproc needs to search against the following table structure. The image can be seen better by right-clicking it and View Image, or similar depending on your browser. Now this is where I'm stuck, the sproc should be able to find Tours where: The Tour's 1st TourHotel (Ordinal 0) has the same CountryId (and ResortName if specified) of the 1st row of @Locations (Ordinal 0). And also if @Locations has 1 row, the Tour must have additional TourHotels, ALL of which must be in the remaining CountryIds (and ResortNames if specified) of these remaining @Locations rows. Edit This is the code I finally used, based on Anthony Faull's suggestion. Thank you so much Anthony: select distinct T.Id from tblTour T join tblTourHotel TH on TH.TourId = T.Id join tblHotel H ON H.Id = TH.HotelId JOIN @Locations L ON ( ( L.Ordinal = 0 AND TH.Ordinal = 0 ) OR ( L.Ordinal > 0 AND TH.Ordinal > 0 ) ) AND L.CountryId = H.CountryId AND ( L.ResortName = H.ResortName OR L.ResortName IS NULL ) cross apply( select COUNT(TH2.Id) AS [Count] FROM tblTourHotel TH2 where TH2.TourId = TH.TourId ) TourHotelCount where TourHotelCount.[Count] = @LocationCount group by T.Id, T.TourRef, T.Description, T.DepartureDate, T.NumNights, T.DepartureAirportId, T.DestinationAirportId, T.AirlineId, T.FEPrice having COUNT(distinct TH.Id) = @LocationCount

    Read the article

  • Get Multiple Values From Database ASP.NET/C#

    - by user1043177
    I am trying to get/return multiple values from an SQL-Server database using and display them on an ASP.NET page. I am using a stored procedure to perform the SELECT command on the Database side. I am able to return the first value that matches the variable @PERSON but only one row is returned each time. Any help would be much appreciated. Database handler class public MainSQL() { _productConn = new SqlConnection(); _productConnectionString += "data source=mssql.database.co.uk;InitialCatalog=test_data;User ID=username;Password=password"; _productConn.ConnectionString = _productConnectionString; } public string GetItemName(int PersonID) { string returnvalue = string.Empty; SqlCommand myCommand = new SqlCommand("GetItem", _productConn); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@PERSON", SqlDbType.Int)); myCommand.Parameters[0].Value = PersonID; _productConn.Open(); returnvalue = (string)myCommand.ExecuteScalar(); _productConn.Close(); return (string)returnvalue; } Stored Procedure USE [test_data] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ppir].[GetItem] ( @PERSON int ) AS /*SET NOCOUNT ON;*/ SELECT Description FROM [Items] WHERE PersonID = @PERSON RETURN return.aspx namespace test { public partial class Final_Page : System.Web.UI.Page { MainSQL GetInfo; protected void Page_Load(object sender, EventArgs e) { int PersonId = (int)Session["PersonID"]; GetInfo = new MainSQL(); string itemname = GetInfo.GetItemName(PersonId); ReturnItemName.Text = itemname; } // End Page_Load } // End Class } // End Namespace

    Read the article

  • oracle call stored procedure inside select

    - by CC
    Hi everybody. I'm working on a query (a SELECT) and I need to insert the result of this one in a table. Before doing the insert I have some checking to do, and if all columns are valid, I will do the insert. The checking is done in a stored procedure. The same procedure is used somewhere else too. So I'm thinking using the same procedure to do my checks. The procedure does the checkings and insert the values is all OK. I tryied to call the procedure inside my SELECT but it does not works. SELECT field1, field2, myproc(field1, field2) from MYTABLE. This kind of code does not works. I think it can be done using a cursor, but I would like to avoid the cursors. I'm looking for the easiest solution. Anybody, any idea ? Thanks alot. C.C.

    Read the article

  • How to pass XML from C# to a stored procedure in SQL Server 2008?

    - by Geetha
    I want to pass xml document to sql server stored procedure such as this: CREATE PROCEDURE BookDetails_Insert (@xml xml) I want compare some field data with other table data and if it is matching that records has to inserted in to the table. Requirements: How do I pass XML to the stored procedure? I tried this, but it doesn’t work:[Working] command.Parameters.Add( new SqlParameter("@xml", SqlDbType.Xml) { Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml, XmlNodeType.Document, null)) }); How do I access the XML data within the stored procedure? Edit: [Working] String sql = "BookDetails_Insert"; XmlDocument xmlToSave = new XmlDocument(); xmlToSave.Load("C:\\Documents and Settings\\Desktop\\XML_Report\\Books_1.xml"); SqlConnection sqlCon = new SqlConnection("..."); using (DbCommand command = sqlCon.CreateCommand()) { **command.CommandType = CommandType.StoredProcedure;** command.CommandText = sql; command.Parameters.Add( new SqlParameter("@xml", SqlDbType.Xml) { Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml , XmlNodeType.Document, null)) }); sqlCon.Open(); DbTransaction trans = sqlCon.BeginTransaction(); command.Transaction = trans; try { command.ExecuteNonQuery(); trans.Commit(); sqlCon.Close(); } catch (Exception) { trans.Rollback(); sqlCon.Close(); throw; } Edit 2: How to create a select query to select pages, description based on some conditions. <booksdetail> <isn_13>700001048</isbn_13> <isn_10>01048B</isbn_10> <Image_URL>http://www.landt.com/Books/large/00/7010000048.jpg</Image_URL> <title>QUICK AND FLUPKE</title> <Description> PRANKS AND JOKES QUICK AND FLUPKE </Description> </booksdetail>

    Read the article

  • MySql returning multiple rows from stored procedure / function

    - by pistacchio
    Hi, I need to make a stored procedure or function that returns a set of rows. I've noted that in a stored procedure i can SELECT * FROM table with success. If i fetch rows in a loop and SELECT something, something_other FROM table once per loop execution, I only get one single result. What I need to do is looping, doing some calculations and returning a rowset. What's the best way to do this? A temporary table? Stored functions? Any help appreciated.

    Read the article

  • How do I select from a stored procedure in Sybase?

    - by Nick Fortescue
    My DBA has constructed me a stored procedure in a Sybase database, for which I don't have the definition. If I run it, it returns a resultset with a set of columns and values. I would like to SELECT further to reduce the rows in the result set. Is this possible? From this question it seems like I could insert the results into a temporary table, but I'm not sure I've got permissions to do this. Is there any way I can SELECT certain rows, or if not, can someone give me example code for simulating with a temporary table?

    Read the article

  • Guilty of unsound programming

    - by TelJanini
    I was reading Robert Rossney's entry on "What's the most unsound program you've had to maintain?" found at: (What's the most unsound program you've had to maintain?) when I realized that I had inadvertently developed a near-identical application! The app consists of an HTTPListener object that grabs incoming POST requests. Based on the information in the header, I pass the body of the request to SQL Server to perform the appropriate transaction. The requests look like: <InvoiceCreate Control="389> <Invoice> <CustomerNumber>5555</CustomerNumber> <Total>300.00</Total> <RushOrder>1</RushOrder> </Invoice> </InvoiceCreate> Once it's received by the HTTPListener object, I perform the required INSERT to the Invoice table using SQL Server's built-in XML handling functionality via a stored procedure: INSERT INTO Invoice (InvoiceNumber, CustomerNumber, Total, RushOrder) SELECT @NEW_INVOICE_NUMBER, @XML.value('(InvoiceCreate/Invoice/CustomerNumber)[1]', 'varchar(10)'), @XML.value('(InvoiceCreate/Invoice/Total)[1]', 'varchar(10)'), @XML.value('(InvoiceCreate/Invoice/Total)[1]', 'varchar(10)') I then use another SELECT statement in the same stored procedure to return the value of the new Invoice Number that was inserted into the Invoices table: SELECT @NEW_INVOICE_NUMBER FOR XML PATH 'InvoiceCreateAck' I then read the generated XML using a SQL data reader object in C# and use it as the response of the HTTPListener object. My issue is, I'm noticing that Robert is indeed correct. All of my application logic exists inside the stored procedure, so I find myself having to do a lot of error-checking (i.e. validating the customer number and invoicenumber values) inside the stored procedure. I'm still a midlevel developer, and as such, am looking to improve. Given the original post, and my current architecture, what could I have done differently to improve the application? Are there any patterns or best practices that I could refer to? What approach would you have taken? I'm open to any and all criticism, as I'd like to do my part to reduce the amount of "unsound programming" in the world.

    Read the article

  • SQL Server concurrency and generated sequence

    - by Goyuix
    I need a sequence of numbers for an application, and I am hoping to leverage the abilities of SQL Server to do it. I have created the following table and procedure (in SQL Server 2005): CREATE TABLE sequences ( seq_name varchar(50) NOT NULL, seq_value int NOT NULL ) CREATE PROCEDURE nextval @seq_name varchar(50) AS BEGIN DECLARE @seq_value INT SET @seq_value = -1 UPDATE sequences SET @seq_value = seq_value = seq_value + 1 WHERE seq_name = @seq_name RETURN @seq_value END I am a little concerned that without locking the table/row another request could happen concurrently and end up returning the same number to another thread or client. This would be very bad obviously. Is this design safe in this regard? Is there something I can add that would add the necessary locking to make it safe? Note: I am aware of IDENTITY inserts in SQL Server - and that is not what I am looking for this in particular case. Specifically, I don't want to be inserting/deleting rows. This is basically to have a central table that manages the sequential number generator for a bunch of sequences.

    Read the article

  • Is there a free tool which can help visualize the logic of a stored procedure in SQL Server 2008 R2?

    - by Hamish Grubijan
    I would like to be able to plot a call graph of a stored procedure. I am not interested in every detail, and I am not concerned with dynamic SQL (although it would be cool to detect it and skip it maybe or mark it as such.) I would like the tool to generate a tree for me, given the server name, db name, stored proc name, a "call tree", which includes: Parent stored procedure. Every other stored procedure that is being called as a child of the caller. Every table that is being modified (updated or deleted from) as a child of the stored proc which does it. Hopefully it is clear what I am after; if not - please do ask. If there is not a tool that can do this, then I would like to try to write one myself. Python 2.6 is my language of choice, and I would like to use standard libraries as much as possible. Any suggestions? EDIT: For the purposes of bounty Warning: SQL syntax is COMPLEX. I need something that can parse all kinds of SQL 2008, even if it looks stupid. No corner cases barred :) EDIT2: I would be OK if all I am missing is graphics.

    Read the article

  • MYSQL -Incorrect Syntax

    - by user1854392
    WHILE x > 1 DO SET x = x - 1; SET totalTime = SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24), ' days ', MOD(HOUR(TIMEDIFF(end_time,start_time)), 24), ' hrs ', MINUTE(TIMEDIFF(end_time,start_time)), ' minutes ') AS total_Time I don't see why I am having a syntax error? It is part of a bigger procedure but is pointing to this aas being incorrect Error message: SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24,' days',' at line 11 and totalTime is declared as a VARCHAR(50)

    Read the article

  • Get return values from a stored procedure in c# (login process)

    - by Jin
    Hi all, I am trying to use a Stored Procedure which takes two parameters (login, pw) and returns the user info. If I execute the SP manually, I get Session_UID User_Group_Name Sys_User_Name ------------------------------------ -------------------------------------------------- - NULL Administrators NTMSAdmin No rows affected. (1 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[p_SYS_Login]. But with the code below, I only get the return value. do you know how to get the other values shown above like Session_UID, User_Group_Name, and Sys_User_Name ? if you see the commented part below code. I tried to add some output parameters but it doesn't work with incorrect number of parameters error. string strConnection = Settings.Default.ConnectionString; using (SqlConnection conn = new SqlConnection(strConnection)) { using (SqlCommand cmd = new SqlCommand()) { SqlDataReader rdr = null; cmd.Connection = conn; cmd.CommandText = "p_SYS_Login"; //cmd.CommandText = "p_sys_Select_User_Group"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter paramReturnValue = new SqlParameter(); paramReturnValue.ParameterName = "@RETURN_VALUE"; paramReturnValue.SqlDbType = SqlDbType.Int; paramReturnValue.SourceColumn = null; paramReturnValue.Direction = ParameterDirection.ReturnValue; //SqlParameter paramGroupName = new SqlParameter("@User_Group_Name", SqlDbType.VarChar, 50); //paramGroupName.Direction = ParameterDirection.Output; //SqlParameter paramUserName = new SqlParameter("@Sys_User_Name", SqlDbType.VarChar, 50); //paramUserName.Direction = ParameterDirection.Output; cmd.Parameters.Add(paramReturnValue); //cmd.Parameters.Add(paramGroupName); //cmd.Parameters.Add(paramUserName); cmd.Parameters.AddWithValue("@Sys_Login", textUserID.Text); cmd.Parameters.AddWithValue("@Sys_Password", textPassword.Text); try { conn.Open(); object result = cmd.ExecuteNonQuery(); int returnValue = (int)cmd.Parameters["@RETURN_VALUE"].Value; if (returnValue == 0) { Hide(); Program.MapForm.Show(); } else if (returnValue == 1) { MessageBox.Show("The username or password you entered is incorrect", "NTMS Login", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else if (returnValue == 2) { MessageBox.Show("This account is disabled", "NTMS Login", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { MessageBox.Show("Database error. Please contact administrator", "NTMS Login", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } catch (Exception ex) { string message = ex.Message; string caption = "MAVIS Exception"; MessageBoxButtons buttons = MessageBoxButtons.OK; MessageBox.Show( message, caption, buttons, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } } } Thanks for your help.

    Read the article

< Previous Page | 22 23 24 25 26 27 28 29 30 31 32 33  | Next Page >