Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-08 : 10:23:09
|
| katayoun writes "I have a problem with stored procedures in SQL Server.I use stored procedures to retrieve fields from a table, then in asp code I give values to the fields and update them (with recordset object and update method).But it seems that SQL Server creates it's own stored procedures to faciliate this update procedure and so some times it not only updates the required record (the one i've asked for in the stored procedure through a WHERE clause) but also other records.I've run the trace in SQL Server and found some procedures created by SQL, with sp_prepare that run something like : sp_prepare ...UPDATE ... WHERE (<f1> IS NULL) AND (<f2> IS NULL) AND ...and so you can see why not only the requested record but all the records that had null values for those fields where updated.So shall i refrain from using stored procedures in this way, or is there a way where i can set SQL NOT to create it's own temp stored procedureskati" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-08 : 10:39:37
|
| Sql Server will not do any UPDATE on user table on its own. there might be a bug in your Update statement . its perfectly logical when you say "UPDATE ... WHERE (<f1> IS NULL) AND (<f2> IS NULL) AND ..." it updates all the records where the conditin matches. What do you mean by your Requested Record.Post your code here. we will try refining it.search for "Stored Procedure" in Forum search , you will find lot of good links there , follow them and you will know quite a lot.----------------------------Anything that Doesn't Kills you Makes you Stronger |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-08 : 13:05:41
|
quote:
I use stored procedures to retrieve fields from a table, then in asp code I give values to the fields and update them (with recordset object and update method).
OK, I'm guessing here that you're saying you have code that looks something likers.Open "spGetMyRecord"rs.Fields("FirstName") = "Joe"rs.Fields("LastName") = "Smith"rs.UpdateWhile this is an option, I would strongly encourage you to avoid this technique, and instead, issue the actual SQL UPDATE statement that you wish to accomplish... UPDATE MyTable SET FirstName = 'Joe', LastName = 'Smith' WHERE ...or for new records INSERT INTO MyTable (FirstName, LastName) VALUES ('Joe', 'Smith')Please note use of single-quotes (') in SQL instead of double quotes(").By issuing the actual SQL update statement, you know exactly what you're going to get. Further, I would then wrap this update statement in a stored procedure with parameters and you can just pass in the new values as parameters.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be...Edited by - AjarnMark on 01/08/2002 13:06:25 |
|
|
|
katayoun
Starting Member
2 Posts |
Posted - 2002-01-11 : 09:17:05
|
hi, i'm not saying that it's doing an update on it's own, i'm calling the "update" method for the recordset BUT what it does is that before my update method it creates a stored procedure with a "where" clause that have nothing to do with my select and then when i call the update method that stored procedure is called and a whole set of records are updated instead of only updating the record i have retrieved with the select (since as i said the where clause of the prepared proc is different from the where clause of my select).all i can think of is that the cursor in my select method is incorrect (i use static cursor)and so after i retrieve the record and change some of the fields and then call update while i am assuming i'm working with the selected (and retrieved) record, my assumption is wrong. though i have to say that i have been doing this quite a time and this is the first time i run into this problem. thanksquote:
Sql Server will not do any UPDATE on user table on its own. there might be a bug in your Update statement . its perfectly logical when you say "UPDATE ... WHERE (<f1> IS NULL) AND (<f2> IS NULL) AND ..." it updates all the records where the conditin matches. What do you mean by your Requested Record.Post your code here. we will try refining it.search for "Stored Procedure" in Forum search , you will find lot of good links there , follow them and you will know quite a lot.----------------------------Anything that Doesn't Kills you Makes you Stronger
|
|
|
|
katayoun
Starting Member
2 Posts |
Posted - 2002-01-11 : 09:23:16
|
thanks,since i couldn't do anything else i did change all my stored procedures to contain the update statement also and did pass all the parameters. now here's a question, the parameters are quite alot, 10 or so and also a couple are pretty big varchar(512), will that be a problem?also i use the update method in another way also, that is i set the type of the command object to be "cmdTable" and the do basically the same thing i did with a stored proc and recordset. that is define a record set for the command object and give values to the fields and then call the update method for the record set object. do you recommend that i also should change this? thanks, katayounquote:
quote:
I use stored procedures to retrieve fields from a table, then in asp code I give values to the fields and update them (with recordset object and update method).
OK, I'm guessing here that you're saying you have code that looks something likers.Open "spGetMyRecord"rs.Fields("FirstName") = "Joe"rs.Fields("LastName") = "Smith"rs.UpdateWhile this is an option, I would strongly encourage you to avoid this technique, and instead, issue the actual SQL UPDATE statement that you wish to accomplish... UPDATE MyTable SET FirstName = 'Joe', LastName = 'Smith' WHERE ...or for new records INSERT INTO MyTable (FirstName, LastName) VALUES ('Joe', 'Smith')Please note use of single-quotes (') in SQL instead of double quotes(").By issuing the actual SQL update statement, you know exactly what you're going to get. Further, I would then wrap this update statement in a stored procedure with parameters and you can just pass in the new values as parameters.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be...Edited by - AjarnMark on 01/08/2002 13:06:25
|
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-01-11 : 10:55:39
|
| You shouldn't need a recordset at all if you're using the command object and stored procedures. Like so...**********Sub update_tableDim objCommSet objComm = Server.CreateObject("ADODB.Command")'run stored procedure objComm.ActiveConnection = objConnobjComm.CommandText = "update_table"objComm.CommandType = adCmdStoredProcobjComm.Parameters.Append objComm.CreateParameter("@user_id", adInteger, adParamInput, 10)objComm.Parameters.Append objComm.CreateParameter("@user_name", adVarChar, adParamInput, 50) objComm.ExecuteSet objComm = NothingEnd Sub**********Jack |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-11 : 11:06:14
|
Always put your code into stored procedure's . its no problem passing any no. of parameters to a stored procedure.I lost count of the time i have recommended this link  . but, Again here it is , follow ithttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10964HTH----------------------------Anything that Doesn't Kills you Makes you Stronger |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-11 : 17:40:34
|
| Of course, in Jack's example above, you might want to assign a value to your parameters before you call the .Execute method. --------------------------------There's a new General in town... |
|
|
|
|
|
|
|
|
Stored procedure problem