Hello I'm building a shopping cart website and using SQL tables CATEGORY Id int, Parent_Id, Description varchar(100) Data: 1 0 Electronics 2 0 Furniture 3 1 TVs 4 3 LCD 5 4 40 inches 6 4 42 inches PRODUCTS Id int, Category_Id int Description... Data: 1 5 New Samsung 40in LCD TV 2 6 Sony 42in L...
I'd like to write stored procedures in pgSQL that dynamically generate web-ready data. I need a pure SQL to HTML or SQL to XML gateway. Oracle has OWA. In Oracle you can setup a RAC frontend to a SAN and connect a large set of OWA hosts to your RAC so you layer your web requests and spread your queries. What is the PostgresSQL or MyS...
Hi, I have a Candidate entity that is read/write to a Candidate table under EF 4.0. This all works great but I need to load another type of Candidate from our existing membership table via a stored proc and Function Import. From the applications POV they are also a Candidate they just happen to have an additional attribute of Grade. ...
I have a stored Procedure that do some operation and return 1 or 0 as below CREATE PROCEDURE dbo.UserCheckUsername11 ( @Username nvarchar(50) ) AS BEGIN SET NOCOUNT ON; IF Exists(SELECT UserID FROM User WHERE username =@Username) return 1 ELSE return 0 END and in C# i want to get this returned value i try ExcuteScalar b...
Hi, I want to compare the individual words from the user input to individual words from a column in my table. For example, consider these rows in my table: ID Name 1 Jack Nicholson 2 Henry Jack Blueberry 3 Pontiac Riddleson Jack Consider that the user's input is 'Pontiac Jack'. I want to assign weights/ranks for each match, so I ...
Problem I am trying to write a stored procedure to select a list of NewsItem records where each NewsItem has all of the Categories that are in a list. If the list is empty then it should return all news items. NewsItem NewsItemCategories Category -------- ------------------ -------- NewsID NewsID C...
I have been using MySQL for a while, but only with basic queries and some joins. I want to start using Stored Procedures but I am having trouble finding a good resource to help me with what I want to know. The main thing I want to know is how to check the value of any field in the entire database before returning anything. For example: ...
I have created a stored procedure in Mysql, as : DELIMITER // CREATE PROCEDURE test() BEGIN SELECT * FROM buyers; END // DELIMITER ; but when i call it using, call test() it returns an error saying : #1312 - PROCEDURE ticketninja.test1 can't return a result set in the given context ...
I am getting this error: DataError: (DataError) invalid input syntax for integer: "1.50" CONTEXT: PL/pgSQL function "sp_aggregate_cart" line 82 at FOR over EXECUTE statement 'SELECT total_items, subtotal, is_shipping_required, discount_other, is_shipping_discount FROM sp_aggregate_cart(8135)' {} When running my application code. When...
I'm receiving an error for the following query: EXEC dbo.sp_Sproc_Name @Param1=@ParamValue1 ,@Param2='lorem ipsum "' + @ParamValue2 + '" dolor' I get the error: Incorrect syntax near '+'. Therefore, how can I pass a variable as part of my parameter value like I'm trying to do above? Many Thanks. ...
Here is what I have as VBScript Subroutine: sub buildChildAdminStringHierarchical(byval pAdminID, byref adminString) set rsx = conn.execute ("select admin_id from administrator_owners where admin_id not in (" & adminString & ") and owner_id = " & pAdminID) do while not rsx.eof adminString = adminString & "," & rsx(0) ...
* EDIT6: * This is what ended up working for me (from accepted answer): var ret1 number var tran_cnt number var msg_cnt number var rc refcursor exec :tran_cnt := 0 exec :msg_cnt := 123 exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => :rc) print :tran_cnt print :msg_cnt pr...
Hi, I am sure the title of my question doesn't make much sense. But here is what I'm trying to achieve. I have table with different stories(columns are StoryID, StoryTitle, StoryDesc, StoryCategory), so each story can belong to a category. for example category1, category2,.... category10. I am in the need of a SP, where i specify the ca...
I am in the process of revising code to use TVP to send data from our VB.NET app to the SQL 2008 DB and try to keep all the writes atomic. Using this page as a general guide: http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters I am in the process of creating all the in-code datatables to be sent to the SQL stored pro...
I have created a stored procedure in Mysql, as : delimiter $$ drop procedure if exists test9$$ Create procedure test9(test_type varchar(20)) Reads sql data begin Declare 1_id int; Declare 1_date varchar(20); Declare done int default 0; Declare cur1 cursor for select id,name from buyers where ticket_type='test_t...
Task: Write timestamps into MS SQL database table every second. Solutions: External application which writes timestamps by schedule (Sql agent for example). Stored procedure, which writes timestamps in infinite loop. Questions. Which of the solutions is best? Is there any drawbacks of running infinite loop in stored procedure? ...
I created a dataset (.xsd) from a stored procedure which takes 2 parameters. The .xsd file is linked to a crystal report. When I load the report no data is displayed. Also I dont want the user to be prompted for the parameters as I know the values in code depending on page that requested the report load. How do I link the parameters to t...
I want to export the results from a sproc to Excel. Thus, between the exec and SELECT statements I insert the following: INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Temp\testing.xls;', ' SELECT Field1, Field2, Field3 FROM [Sheet1$] ') Which returns the following error: OLE DB provider 'Microsoft.Jet.OL...
Hi, I would like to know if it's possible to extract the categories and sub-categories in a single DB fetch. My DB table is something similar to that shown below table cat_id parent_id 1 0 2 1 3 2 4 3 5 3 6 1 i.e. when the input is 3, then all the rows with parent_id as 3 AND the row 3 itself AND all t...
When you insert a record into a table with an identity column, you can use SCOPE_IDENTITY() to get that value. Within the context of a stored procedure, which would be the recommended way to return the identity value: As an output parameter SET @RETURN_VALUE = SCOPE_IDENTITY() As a scalar SELECT SCOPE_IDENTITY() Another way? Any pro...