I'm working through a wide variety of procs that have a WHERE clauses that look like this: WHERE ... AND ( ( myTbl.myValue = 1234) or (myTbl.myValue = 1235) )-- Value = No I've talked this over with some colleagues and this sort of code seems unavoidable. I think it's a good idea to put this sort of code into one (and only one)...
I need to locate the index position of a record in a large database table in order to preset a pager to that item's page. I need an efficient SQL query that can give me this number. Sadly SQL doesn't offer something like: SELECT INDEX(*) FROM users WHERE userid='123' Any bright ideas? EDIT: Lets assume there is an ORDER BY clause app...
I have the following very large table in SQL Server 2005: create table Blah ( FirstName varchar(30), Rank int, Position int, ... ) I will run the following query on it: declare @PassedInFirstName varchar(30) set @PassedInFirstName = 'SomeName' select TOP 1 Position from Blah where FirstName = @PassedInFirstName order by Ran...
I need to store a couple of money related fields in the database but I'm not sure which data type to use between money and decimal. ...
I have two tables : Bill : create table Bill( BillDate datetime, Amount decimal(10,2) , BillCurrency varchar(3) , ReportingCurrency decimal(10,2)) FxRate : create table FxRate( RateDate datetime, SourceCurrency varchar(3), Targ...
I am having problems executing a prepared statement via mysqli. First I was getting Command out of sync errors. I am storing the result and closing the connection, and I have stopped getting this error, so hopefully the problem has stopped. However, the error in my sql syntax error, which was working fine while the commands were not in...
This is just a general discussion on what is the best occasion to use stored procedure! Personnally i have very low opinion on stored procs becouse; 1. they tie you to one particular database enviroment and 2. the idea of shuffling back and fourth between your interface code and the back-end enviroment for the stored procs its a nightmar...
We can wrap a call to a stored procedure in a transaction and specify an isolation level. Or we can put the transaction inside the stored procedure specify an isolation level there. Which is it better to do? ...
Hello, guys. Need your advice very much. We'are working on the PHP project, which was in development for 2+ years, and now the team is ready and feel willingness to switch the development on ORM rails. Because it realy fasts the development and allow you to operate by Objects and not think in terms of SQL code and database tables in mos...
I have a function, which is to return an array of rows containg records from a database, selected based on a LIKE query. I want this query to be a prepared statement for security reasons. Apparently I can not use bound parameters and the query function as I am doing. I am unsure then, of how to keep me query as a prepared statement, and ...
This is a significant edit from the original question, making it more concise and covering the points raised by existing answers... Is it possible to have mulitple changes made to multiple tables, inside a single transaction, and rollback only some of the changes? In the TSQL below, I would NOT want any of the changes made by "myLogSP"...
I'm working now for a while on a reporting applications where I use hibernate to define my queries. However, more and more I get the feeling that for reporting use cases this is not the best approach. 1) The queries only result partial columns, and thus not typed objects (unless you cast all fields in java). 2) It is hard to express que...
I'm working on a search stored procedure for our existing forums. I've written the following code which uses standard SQL full text indexes, however I'm sure there is a better way of doing it and would like a point in the right direction. To give some info on how it needs to work, The page has 1 search text box which when clicked will ...
I have a simple problem here using SQL views. I just can't seem to figure it out at the moment. I have 2 tables, TableA and TableB. I want to retrieve FieldA in TableA and FieldB in TableB. The two tables are linked using an INNER JOIN. I only want rows where TableA.FieldA are distinct. The returned values should be of the top 10 items...
Hi Is there a way to list all sqlce database table indexes, or at least for individual tables? ...
CurrentMonth = Month(CurrentDate) CurrentYear = Year(CurrentDate) SQL = "SELECT Spent, MONTH(Date) AS InvMonth, YEAR(Date) As InvYear FROM Invoices WHERE YEAR(Date) = '" & CurrentYear & "' AND MONTH(Date) = '" & CurrentMonth & "'" RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText Do Until RecordSet.EO...
What is the best way to manage connection strings in a web application, from a security standpoint? I've done it several different ways. I've stored them as plain text web.config setting keys. I've also created a "Constants" class that has public read-only string properties for each connection string. Does anybody have any recommenda...
I have SQL Server 2000, it doesn't support MultipleActiveResults. I have to do multiple inserts, and it's done with one connection per insertion. I want to begin a transaction before all insertions and finish it after all insertions. How do I do it? ...
I have a text field that can be 30 characters in length, however it can achieve around 2.000 characters. For "future safety", it would be nice if this new column could support until 3.000 characters. I think the average size of this field is 250 characters. Which SQL Server 2000 data type is better to increase performance and disk spac...
I've an odd problem: SELECT a.uid, b.*, c.liveId FROM a INNER JOIN b ON (a.uid=b.uid AND a.versionNo=b.versionNo) LEFT JOIN c ON (a.uid=c.uid) WHERE a.memberId=1; I call that from a query browser and it returns 3 rows. I call it from within a stored procedure and it gives 2 rows (the LEFT JOIN becomes ineffective). DELIMITER // ...