Do the following scheme for my database: create sequence data_sequence; create table data_table { id integer primary key; field varchar(100); }; create view data_view as select id, field from data_table; create function data_insert(_new data_view) returns data_view as $$declare _id integer; _result data_view%rowtype; ...
Hi, I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead. I don't think the problem is with the procedure because if I enter the values manually it runs properly. What am I doing wrong? VBA Code: Private Sub intCustomerNo_Click() Dim cmdCommand A...
Hi I have a product table which simplifies to this: create table product(id int primary key identity, productid int, year int, quarter int, price money) and some sample data: insert into product select 11, 2010, 1, 1.11 insert into product select 11, 2010, 2, 2.11 insert into product select 11, 2010, 3, 3.11 insert into product sele...
Hi All! Say I have a stored procedure consisting of several separate SELECT, INSERT, UPDATE and DELETE statements. There is no explicit BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS logic. How will SQL Server handle this stored procedure transaction-wise? Will there be an implicit connection for each statement? Or will there be one tran...
I need to iterate over a recordset from a stored procedure and execute another stored procedure using each fields as arguments. I can't complete this iteration in the code. I have found samples on the internets, but they all seem to deal with a counter. I'm not sure if my problem involved a counter. I need the T-SQL equivalent of a forea...
Hi! I want to know what is best practice for using db views, db tables, stored proc. and objects in tables... Which of these is more flexible and why, can You explain? Best regards! ...
Hi all, I would like to know how to measure/know that one's stored procedure is optimum in performance. That stored procedure can be inserting/deleting/updating or manipulating data in that procedure. Please describe the way/approach how to know performance in SQL Server. Thanks in advance! ...
Using an edmx version of EF. My returned data is a join between tables that has a COMPOUND filter on the primary table. In essence this query is going to return a SEGMENT of Law codes and descriptions that a user can tie to a Sex Offender report. I have a complex SP because Linq2SQL cannot pass in a between statement, or at least th...
Based on a previous question and with a lot of help from Damir Sudarevic (thanks) I have the following sql code which works great but is very slow. Can anyone suggest how I can speed this up and optimise for speed. I am now using SQL Server Express 2008 (not 2005 as per my original question). What this code does is retrieves paramet...
Hi All, I've been using the ADO.NET Strogly-Typed DataSet model for about 2 years now for handling CRUD and stored procedure executions. This past year I built my first MVC app and I really enjoyed the ease and flexibility of LINQ. Perhaps the biggest selling point for me was that with LINQ I didn't have to create "Insert" stored proced...
I use the following mysql procedure, DROP PROCEDURE IF EXISTS `allied`.`GetRegistrationData`$$ CREATE DEFINER=`allied`@`%` PROCEDURE `GetRegistrationData`() BEGIN select aboutUsId,name from aboutus where isDeleted=0 select ageId,name from age where isDeleted=0 select desiredsetId,name from desiredsetting where isDeleted=0 select di...
I could see nothing wrong with the execution plan. Besides, as I understand it, SQL Server 2000 extended many of the performance benefits of stored procedures to all SQL statements by recognising new T-SQL statements against T-SQL statements of existing execution plans (by retaining execution plans for all SQL statements in the procedure...
I work with Delphi 6 and SQL Server 2008. With Windows Vista everything runs ok. But since i change my OS to Windows 7 all my projects started to show a message when i'm trying to compile them that says "Stored procedure (SPname). Doesn't found or doesn't exist in the server. I look my server and it has the Sp with the correct name. i u...
Hi! I call stored function with such signature: FUNCTION ADDDAYS (city VARCHAR2, startDate DATE, numDays INTEGER) from java code: JdbcTemplate jt = getJdbcTemplate(); Object o = jt.execute("{? = call ADDDAYS(?, ?, ?)}", new CallableStatementCallback() { public Object doInCallableStatement(CallableStat...
We wanted to call a .NET Managed code (deployed as dll) from a Stored Procedure in (SQL Server 2005/2008) We found couple of solutions, but couldn't get it working: Following steps mentioned in the article at this place http://www32.brinkster.com/srisamp/sqlArticles/article_33.htm - leaves us with following error code: 0x80131700 ...
I am working on a project that requires me to hop into to separate DB's. So I have figured that I need to have multiple functions inside of my VB page. The only problem I am having,is I am not to sure how to get this all accomplished. So far I have figured out the overall structure, just need help implementing that structure. Here is...
Please recommend some websites with a quick guide. MSDN is good but it's hard to pick up some basic stuff in a short time. Thanks. ...
I've got a child procedure which returns more than table. child: PROCEDURE KevinGetTwoTables AS BEGIN SELECT 'ABC' Alpha, '123' Numeric SELECT 'BBB' Alpha, '123' Numeric1, '555' Numeric2 END example: PROCEDURE KevinTesting AS BEGIN DECLARE @Table1 TABLE ( Alpha varchar(50), Numeric1 int ) DECLARE...
Hello everyone, Basically, we have an ASP website right now that I'm converting to PHP. We're still using the MSSQL server for the DB -- it's not moving. In the ASP, now, there's an include file with a giant sql query that's being executed. This include sits on a lot of pages and this is a simple version of what happens. Pages A, B and...
Ok let me try this again. query("CALL getemployee('$eml')"); $result = $sql->fetch_array(); ?> This is my stored procedure: Delimiter // Create procedure getemployee(in eml varchar(50)) Begin Select * from employees where email = eml; End// Delimiter ; The error i get from browser: "Fatal error: Call to a member function fetch_ar...