|
Deepasubramanian wrote: 1.In what scenarios we prefer to use UDFs instead of Stored Procedures?
2.UDFs won't allow out paramaters like Stored Procedures.Other than that how really an UDF differ from Stored Procedure.
Some things that pop into my head:
UDFs cannot do many things that a stored procedure can. They have to be deterministic (which means that given the same set of inputs the same output always happens). A stored procedure doesn't have that restriction. e.g. GETDATE() is a non-deterministic function because its result is different each time it is called. You can use GETDATE() in a stored procedure but not in a UDF.
UFDs can be used in a SELECT statement, Stored Procedures cannot. e.g.
SELECT a, b, dbo.MyUDF(c, d) FROM MyTable
A UDF returns something, a stored procedure doesn't have to return anything.
You cannot modidfy the database in a UDF
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hello gurus. I am hoping that one of you will know the answer to this question, as I am at a complete and total loss (even google, ultimate mensa of the net, seems to have a hole in its memory). So, without further ado, here is the problem:
I have a simple web app. Its currently 2 pages, written in ASP.NET 2.0 using C# and ADO.NET 2.0. The data store is SQL Server 2000, with a meager 10 tables. All of the tables except one have integer identity PK columns, and the one unique one has a uniqueidentifier (guid) PK column. If I query any table by ID directly from SQL Server (enterprise manager or query analyzer) I can query all of the tables without problems. HOWEVER, when I query the tables from ADO.NET 2.0 (or directly from Visual Studio 2005's server explorer, which I beleive uses ADO.NET 2.0), I am getting odd behavior from the table with the uniqueidentifier PK. Oh, and BTW, all of the queries are in stored procedures, which are called from C# code.
When I query the uniqueidentifier table with a simple stored proc containing one parameter (for the guid ID of the row I want to retrieve) and a SELECT statement, ADO.NET 2.0 can't seem to handle this properly. Instead of getting a single row for the record that has a matching ID, I get a result set that looks valid, except it has no data. When I try to use a SqlDataReader to read the fields of the row thats returned, everything seems to work right, but I ultimately get an InvalidCastException. An example (or rather, actual) stored procedure is:
CREATE PROCEDURE dbo.spDSB_GetSuperbillHeader
(
@AppointmentID uniqueidentifier
)
AS
BEGIN
SELECT
a.AppointmentID,
a.ApptDate,
a.PatientID,
a.DoctorID,
a.FacilityID,
a.ApptReason,
a.ApptComments,
a.RefDocName,
a.RespPartyName,
a.FeeSchedule,
a.PriInsName,
a.SecInsName,
a.Copay,
a.PtBalance,
a.AcctComments,
d.DoctorName,
f.FacilityName,
f.Address AS FacilityAddress,
f.CityStateZip AS FacilityCSZ,
f.PhoneNo AS FacilityPhone,
f.TaxID,
p.PatientName,
p.Address AS PatientAddress,
p.CityStateZip AS PatientCSZ,
p.HomePhone,
p.BusinessPhone,
p.MobilePhone,
p.BirthDate,
p.SSN
FROM
tblAppointments a
INNER JOIN tblDoctors d ON a.DoctorID = d.DoctorID
INNER JOIN tblFacilities f ON a.FacilityID = f.FacilityID
INNER JOIN tblPatients p ON a.PatientID = p.PatientID
WHERE
a.AppointmentID = @AppointmentID
END
The resulting error details are:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Specified cast is not valid.
Line 364: return defVal;
Line 365: else
Line 366: return m_reader.GetInt32(idx);
Line 367: }
Line 368: catch (IndexOutOfRangeException ex)
[InvalidCastException: Specified cast is not valid.]
System.Data.SqlClient.SqlBuffer.get_Int32() +121
System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) +39
MedfordMS.DigitalSuperBill.QuickDataReader.GetInt32(String fieldName, Int32 defVal) in F:\Programming\MedfordMDS\Current\DigitalSuperBill\MedfordMS.DigitalSuperBill\MedfordMS.DigitalSuperBill\_Database\QuickDataReader.cs:366
And, for reference purposes, here is what is returned if I execute that procedure with a valid GUID directoy from VS2005's Server Explorer (Note that it returned the column headers but no data, and normally it never returns column headers unless there is data, too):
Running [dbo].[spDSB_GetSuperbillHeader] ( @AppointmentID = d1f880bf-dcf6-4563-a01c-0153df0f16ea ).
AppointmentID ApptDate PatientID DoctorID FacilityID ApptReason ApptComments RefDocName RespPartyName FeeSchedule PriInsName SecInsName Copay PtBalance AcctComments DoctorName FacilityName FacilityAddress FacilityCSZ FacilityPhone TaxID PatientName PatientAddress PatientCSZ HomePhone BusinessPhone MobilePhone BirthDate SSN
-------------------------------------- ----------------------- -------------------- ----------- ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------- --------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------- --------------- --------------- ----------------------- ----------
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[spDSB_GetSuperbillHeader].
Thanks for any help.
|
|
|
|
|
Well, for anyone who runs into the same problem, the solution is simple, if odd. Seems you can't directly pass a Guid to a stored proc using an ADO.NET parameter. To get the stored procedure to work, I had to do the following:
cmd.Parameters.Add("@param", SqlDbType.VarChar, 40);
cmd.Parameters["@param"].Value = myGuid.ToString("D");
This sends the guid as a string value in the form {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}. You must be sure to explicityly set the varchar length to 40, as setting it to 38 (which I did when I originally tried this, since I thought the bounding { and } would be invalid) will still cause the call to fail.
You can keep the type of the parameter in your stored procedure as uniqueidentifier. You do not have to change it to varchar, and in fact, if you do, the stored procedure will fail since varchar(40) won't match the uniqueidentifier type of the column.
|
|
|
|
|
I have Parent child table as decribe below:
Parent Table name = TESTPARENT
1. counter bigint isIdentity=Yes Increment=1 Seed=1
2. customer nChar(10)
Child Table name = TESTCHILD
1. counter bigint
2. qty numeric(3,0)
I want to insert new record with code below:
void button1_Click(object sender, EventArgs e)
{
// Define object to catch @@indentity
object myCounter;
// Connect to database & open
myConnection = new SqlConnection("Data Source=54ND1\\SQL2005;Initial Catalog=Axioma;User ID=sa; Password=sandi");
myConnection.Open();
// define transaction
SqlTransaction myAtom = myConnection.BeginTransaction();
SqlCommand myAtomCmd = myConnection.CreateCommand();
myAtomCmd.Transaction = myAtom;
// Start insert to database with transaction mode
try
{
// Insert parent new record
myAtomCmd.CommandText = string.Format("insert into TESTPARENT (customer) values ('{0}')", tbCustomer.Text);
myAtomCmd.ExecuteNonQuery();
// Get Indentity
myAtomCmd.CommandText = "SELECT @@identity from testParent";
myCounter = myAtomCmd.ExecuteScalar();
// insert child new record
myAtomCmd.CommandText = string.Format("insert into TESTCHILD (counter, qty) values ('{0}', {1})",
Convert.ToInt64(myCounter.ToString()), tbQty.Value);
myAtomCmd.ExecuteNonQuery();
// Commit transaction
myAtom.Commit();
}
catch
{
myAtom.Rollback();
MessageBox.Show("Data not inserted");
}
}
I already try with 2 workstation and 1 server, that code working well (not duplicate in parent and insert right relation
child parent record in child table ).
If, i run with many many user, I am not sure that code will stay stable.
Please advice, that code is the right way to archieve parent child relation insert table??
I using C# and SQl Server 2005
Thank,s and regards 
|
|
|
|
|
|
thank for your advice Mackay 
|
|
|
|
|
wow!, i am amazed at how micro$oft can charge for sql licences. My qusetion is pretty simple but no one can answer it!
If i am using asp.net via sql server 2005, so imagine 20 people via the intranet or lan accessing the same page. How many CAL's do i need?
It should be pretty trival, but i can't find the answer anywhere?
Microsoft say you need a cal for every user or device making a direct connection to sql server or even indirect connections, like pooling or multiplexing. See this page http://www.microsoft.com/sql/howtobuy/multiplexing.mspx[^]
I know of certain application like Cimplicity by GE Fanuc that do this, ok they are running scripts as part of a user action but still 20 people could be making the same connection using the same CAL, surely this is illegal?
Why it is ok for some and not other -- are GE in bed with Microsoft!!
thanks -- just standing on my soap box
can anyone give my an answer?
|
|
|
|
|
|
But intranet is not internet, its an internal network. Yes you could say that for big companies having customers connect to their website and using the database via asp.net. But what about internal customers and employees?
What about the small that can't afford to pay for the processor licensing model!
|
|
|
|
|
Use SQL Express or move to one of the other free alternatives (MySql, etc.)
Having used SQL Server for many years, I found it fairly cheap for what it does. It is the only server I have that has never 'burped'. We used to buy CALs many years back but, switched to per processor when SQL 2000 came out. At 2 processors running for 6 years with 30+ active users/devices that comes out to about $50 bucks per user per year with no license managment headaches. I am not even counting our Internet hits. Granted, coming up with the $10K in the beginning was tough.
I think you will find the pricing competitive with Oracle and other mature datbases.
|
|
|
|
|
Hi all,
I have a problem regarding data bindings. I am using Ms Access database and I have an application which loads the tables in datagrids and textboxes.
I used joins since there are relations between tables. For example I have a table called "Alterations" and in this table there is a field called "Client" which brings the name of the client from the table "Clients" .
When running the application an error occurs stating that "no value given for one or more required parameters"
Some one knows what this error is please??
Thanks alot
|
|
|
|
|
check syntax or can u display ur problem here??
|
|
|
|
|
My problem is that when I run the application an error occurs stating that "no value given for one or more required parameters"
And I am not understanding the error!
|
|
|
|
|
check syntax if any parapmeter is without value.
when u call method and possiblity u forgot to give value for parameter.
|
|
|
|
|
Can u pls tell me, wether following SQL string format is correct or not.
textWord.text,comboCat.text are string variables of C#.
indexInCategoryTable and SubCategoryNo are integer variables.
"select * from Words where
word = N'"+textWord.Text+"' and
CategoryTableName = '"+comboCat.Text+"' and
IndexInCategoryTable = 'indexInCategoryTable' and
SubCategoryNo = 'SubCategoryNo'";
|
|
|
|
|
cshivaprasad wrote: Can u pls tell me, wether following SQL string format is correct or not
No. You should use parameters rather than inject values into the query - See SQL Injection Attacks and Tips on How to Prevent Them[^] unless you want your database attacked by a rampaging mob of mallicious attackers.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Procedure: We have two tables namely table A and Table B. Table A contains a field called ID Table B contains field ID and field 1,field 2,field 3.The procedure is to pick value from table A ID field, increment it by 1 to it and update the table A, then insert data into table B with this incremented value and other data.
Problem: When multiple concurrent users perform updation/insertion of data into table A,table B duplicate IDs are getting created in table B even though the statements are written within ADO.NET BEGINTRANSACTION,COMMIT/ROLLBACK.
Thanks
Mahesh
-- modified at 2:11 Tuesday 9th May, 2006
|
|
|
|
|
Study up on Holding Locks and SET TRANSACTION ISOLATION LEVEL in SQL Server Books Online.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Ex: SELECT DAY(GETDATE())
its display 9 but i need to show tuesday.
can anybody help?
raj
|
|
|
|
|
i found out myself. thanks
the answer is below:
SELECT datename(dw,getdate())
raj
|
|
|
|
|
Hi everybody
please i want to know how to index a column actualy a foreing key
i have a foreing key who megrate from a table to an other one
but i have to index the foreing key in the second table
is it a possibility to do that in sql server ??
try to be good if you can't be the best
|
|
|
|
|
If you want to add a foreign key constraint:
ALTER TABLE dbo.tblForeignTableName
ADD CONSTRAINT keyConstraintName
FOREIGN KEY(fldForeignFieldName)
REFERENCES dbo.tblPrimaryTableName(fldPrimaryFieldName)
ON UPDATE CASCADE
ON DELETE CASCADE
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi ,
as you know in sql server we have just datetime we don't have date & time
so when i want to recuperate the date of something in ma textbox i recuperate also the time
can you giveme a solution ??
thank you very much
try to be good if you can't be the best
|
|
|
|
|
When you recover this value from the database and store it in a datetime variable, you can check the date part by calling:
datevariable.Date;
That's it, well pretty much hehe, this actually returns the date with the time part set to 00:00:00
to put in a text box, you can simply call something like.
datevariable.ToShortString();
or
datevariable.ToString("MM/dd/yyyy");
you should change the formatting as you see fit.
I hope I addressed what you are looking for  .
daniero
|
|
|
|
|
thnx man , i will try the first because i tried the second before & it dosen't work
thanks a lot
try to be good if you can't be the best
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
