I've written a stored procedure that takes in a date parameter. My concern is that there will be confusion between American and British date formats. What is the best way to ensure that there is no ambiguity between dates such as 02/12/2008. One possibility would be for users to enter a date in a format such as 20081202 (yyyymmdd). Is th...
In my project I have two separate DBs on the same server. The DBs are self-sufficient except for three columns in DB "B" that need to be accessed in DB "A". Are there performance considerations if I were to have a stored proc in A that accessed three columns from B directly? Currently, we run a nightly job to import the needed data fro...
Consider the following table: create table temp ( name int, a int, b int ) insert into temp (name, a, b) values (1, 2, 3) insert into temp (name, a, b) values (1, 4, 5) insert into temp (name, a, b) values (2, 6, 7) I want to select *(all fields) with distinct [name]. In the case of two or more rows having the ...
I have a "copy" stored procedure for a database. During the copy, I backup the database with something like: exec('backup database [' + @source + '] ' + 'to disk = N''' + @backupdir + '\copybackup'' ' + 'with noformat, noinit, name=N''copybackup-full'', ' + 'SKIP, NOREWIND, NOUNLOAD, STATS = 10;'); And then create and emp...
Hi, I have some SP that takes year and month: Create PROCEDURE Report( @targetYear int, @targetMonth int ) select sum(col) where year(dateTime) = @targetYear and month(dateTime) = @targetMonth Then I have the same thing for year only Create PROCEDURE Report( @targetYear int ) select sum(col) where year(dateTime) = @ta...
From my experience with DB2 on Z/OS there is no difference between embedded SQL and stored procedures as both get compiled to native code. On the other hand I know that in Oracle there is a huge difference - is it true and how is it in other DBs? Please provide some links to support your claims. ...
I have a situation where I need to add an arbitrary unique id to each of a group of records. It's easier to visualize this below. Edited 11:26 est: Currently the lineNum field has garbage. This is running on sql server 2000. The sample that follows is what the results should look like but the actual values aren't important, the numbers...
Hello all, Somebody please help me by modying this code.when i retrieve the Login value through stored procedure call, i am getting this error message "Procedure or function 'GetUserLogin' expects parameter '@UserName', which was not supplied." Here is my code: public int GetLogin(string UserName, string Password) { SqlConne...
We currently have a search on our website that allows users to enter a date range. The page calls a stored procedure that queries for the date range and returns the appropriate data. However, a lot of our tables contain 30m to 60m rows. If a user entered a date range of a year (or some large range), the database would grind to a halt....
here's what I'd like to do in mySQL... I'm getting the feeling that this is simply not feasible, but would love to be wrong... create procedure foo(IN MYTABLE varchar(50) , IN COLNAME varchar (50), IN MYTYPE varchar(50)) begin IF (select count(*) from information_schema.columns where table_name =MYTABLE and column_name = COLNAME) = 0 ...
I'd like to use . to call sql script from inside a stored proc like so... delimiter /// create procedure append_procedure() BEGIN \. test.sql; END; /// delimiter ; I'm getting a "failed to open 'test.sql;' " error when I run it this way. I've also tried ! but then I get a permission denied error. However, I can't eliminate the ;...
Hello all, Somebody please help me by modying this code.when i retrieve the Login value through stored procedure call,though i give the correct user name and password i am getting the error message "Invalid UserName or Password" means always checking the false condition only.pls help me somebody......Here is my code. Login.cs: public ...
I'm creating parameterized queries in Access and would like to call them from ADO code in a VBA module in Word. I'm not sure of the syntax, however, to call parameterized queries. Can't seem to find a good code reference online. If I have qryGetRecordByFirstLast that accepts Firstname and Lastname as parms, how do I code this execute in...
I have a stored procedure that takes a comma-delimited string of IDs. I split them and put them into a temporary table and pull out records from another table using where id IN [table] Is it ok to use this same procedure when only one id is passed in for the param? I could write a second stored procedure that would do exactly the sameth...
Hi all. I'd like to call a Stored Procedure from a crystal report and assign retrieved value to a field in report? Any suggestions? ...
I'm perverted in the sense that I hate exploring code and database structures in a tree view and I'd much prefer using something like the Powershell for that. Most of the stuff I need to do in SQL is exploring, i.e. looking at what columns does a table have or what does a particular stored procedure do. Looking at table columns is as e...
DISCLAIMER: Let's just say I have a pet project to satisfy my insane desire to write a decent Oracle client that isn't based on Java, while appeasing my coding hobby. END OF DISCLAIMER What I'd like to be able to do is retrieve the schema information for subprograms, functions, package specifications and package bodies from an Oracle 9i...
With newer versions of DB2 you can write stored procedures in SQL or you can create procedures in Java (or other languages) that can then be added to the database and called just like SQL procedures. I'm wondering what the the pros and cons of each method are. I'm specifically interested in comparing those two types of procedures, not ...
This is new to me. I have a new boss at work who is insisting that every query we do from now on be a sproc with XML serialized parameters and return types. I've not run any tests yet but this strikes me as overkill and possibly a performance killer in many ways. What is your experience? ...
Hi, I am adding "Tagging" functionality in my web app. My applications table structures are as following; Tag: (TagId INT IDENTITY, TagName VARCHAR(60)) TaggedRecords: (TaggedId INT IDENTITY, TagId, TaggedRecordId) Now, I want when anyone adds a tag to any record then following action should be performed using a single sql query...