Search Results

Search found 1740 results on 70 pages for 'procedures'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

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

  • 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

  • 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

  • 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

  • Comma separated values in a database field

    - by John Doe
    I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example: Id | Code ---------------------- 0001 | IN,ON,ME,OH 0002 | ON,VI,AC,ZO 0003 | QA,PS,OO,ME What I'm trying to do is create a stored procedure so that I can pass in a codes like "ON,ME" and have it return every product that contains the "ON" or "ME" code. Since the codes are comma separated, I don't know how I can split those and search them. Is this possible using only TSQL? Edit: It's a mission critical table. I don't have the authority to change it.

    Read the article

  • Passing an array of structures to an Oracle stored procedure (CFMX)

    - by Patti
    I'm looking to write a Oracle stored procedure where I would pass in (from ColdFusion) an array of structures and loop over each iteration to insert the bits and pieces within the structures to the DB., I haven't written this type of procedure / package before. I am planning to do an sp / package similar to what is sketched out in the second reply to this thread: How to pass a array of object to oracle stored procedure Assuming I do, how can I call the procedure from ColdFusion (I'm using MX) and pass in my array? As far as I can see, none of the CF_SQL_Types make sense.

    Read the article

  • Capture Stored Procedure print output in .NET (Different model!)

    - by Workshop Alex
    Basically, this question with a difference... Is it possible to capture print output from a TSQL stored procedure in .NET, using the Entity Framework? The solution in the other question doesn't work for me. It works with the connection type from System.Data.SqlClient but I'm using the one from System.Data.EntityClient which does not have an InfoMessage event. (Of course, I could just create an SQL connection based on the Entity connection settings, but prefer to do it directly.)

    Read the article

  • Dynamic SQL and Funtions

    - by Unlimited071
    Hi all, is there any way of accomplishing something like the following: CREATE FUNCTION GetQtyFromID ( @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @acttip char(2), @unisim varchar(15) ) AS BEGIN DECLARE @Result decimal(18,8) DECLARE @SQLString nvarchar(max); DECLARE @ParmDefinition nvarchar(max); --I need to execute a query stored in a cell which returns the calculated qty. --i.e of AcuQry: select @cant = sum(smt) from table where oricod = @oricod and ... SELECT @SQLString = AcuQry FROM OinActUni WHERE (OriCod = @oricod) AND (ActTipCod = @acttip) AND (UniSim = @unisim) AND (AcuEst > 0) SET @ParmDefinition = N' @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @cant decimal(18,8) output'; EXECUTE sp_executesql @SQLString, @ParmDefinition, @oricod = @oricod, @ccocod = @ccocod, @ocmnum = @ocmnum, @oinnum = @oinnum, @acmnum = @acmnum, @cant = @result OUTPUT; RETURN @Result END The problem with this approach is that it is prohibited to execute sp_excutesql in a function... What I need is to do something like: select id, getQtyFromID(id) as qty from table The main idea is to execute a query stored in a table cell, this is because the qty of something depends on it's unit. the unit can be days or it can be metric tons, so there is no relation between the units, therefore the need of a specific query for each unit.

    Read the article

  • C#: ExecuteNonQuery() returns -1 when execute the stored procedure

    - by user1122359
    I'm trying to execute stored procedure in Visual Studio. Its given below. CREATE PROCEDURE [dbo].[addStudent] @stuName varchar(50), @address varchar(100), @tel varchar(15), @etel varchar(15), @nic varchar (10), @dob date AS BEGIN SET NOCOUNT ON; DECLARE @currentID INT DECLARE @existPerson INT SET @existPerson = (SELECT p_ID FROM Student WHERE s_NIC = @nic); IF @existPerson = null BEGIN INSERT INTO Person (p_Name, p_RegDate, p_Address, p_Tel, p_EmergeNo, p_Valid, p_Userlevel) VALUES (@stuName, GETDATE(), @address, @tel, @etel, 0, 'Student' ); SET @currentID = (SELECT MAX( p_ID) FROM Person); INSERT INTO Student (p_ID, s_Barcode, s_DOB, s_NIC) VALUES (@currentID , NULL, @dob, @nic); return 0; END ELSE return -1; END Im doing so by using this code below. SqlConnection con = new SqlConnection(); Connect conn = new Connect(); con = conn.getConnected(); con.Open(); cmd = new SqlCommand("addStudent", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@stuName", SqlDbType.VarChar).Value = nameTxt.Text.ToString(); cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = addressTxt.Text.ToString(); cmd.Parameters.Add("@tel", SqlDbType.VarChar).Value = telTxt.Text.ToString(); cmd.Parameters.Add("@etel", SqlDbType.VarChar).Value = emerTxt.Text.ToString(); cmd.Parameters.Add("@nic", SqlDbType.VarChar).Value = nicTxt.Text.ToString(); cmd.Parameters.Add("@dob", SqlDbType.DateTime).Value = dobTime.Value.ToString("MM-dd-yyyy"); int n = cmd.ExecuteNonQuery(); MessageBox.Show(n.ToString()); But it returns me -1. I tried this stored procedure by entering the same values I captured from debugging. It was successful. What can be the possible error? Thanks a lot!

    Read the article

  • Stored Procedure in postgresql, multiple queries w/ agreggates.

    - by fenix
    I'm trying to write a store procedure that can take some input parameters (obviously), run multiple queries against those, taking the output from those and doing calculations, and from those calculations and the original queries, outputting a formatted text string like: Number of Rows for max(Z) matching condition x and y of total rows matching x (x&y/x*100). To explain the max(Z) bit, this will be the username field, it won't matter which actual entry is picked, because the where clause will filter the results by user id, is there a saner way to do this?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

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