September 18, 2006 at 3:20 pm
Hi, I am new to writing scripts in SQL but I am trying to do some custom stuff for an application that we have. I want to do an update statement to update field in a database and I have posted the transact SQL code below but would like to put that in a stored procedure so that I can call the stored procedure from a batch file during the night. Can this be done or does anyone know of a better way to do this?? Thanks in advance for your help!!
update table_name
set column_name = 'value'
September 18, 2006 at 3:32 pm
Yes it can be done. Are u planning to update the columns for all the rows in the table or few rows(based on selection criteria). The statement u have will update all the rows in the table.
Thanks
Sreejith
September 18, 2006 at 4:01 pm
Yes, I want to do all rows.
Thanks,
Shawn
September 18, 2006 at 10:11 pm
CREATE PROCEDURE dbo.Upd_TableName
@Value VARCHAR(50) --REPLACE WITH THE EXACT DATATYPE
AS
BEGIN
UPDATE dbo.Table_Name
SET column_name = @Value
END
Prasad Bhogadi
www.inforaise.com
September 19, 2006 at 5:49 am
How do I specify the actual value that I want? I want to set that value.
September 19, 2006 at 5:52 am
If you know the value that you need to update you can directly use it in the update statement
CREATE PROCEDURE dbo.Upd_TableName
AS
BEGIN
UPDATE dbo.Table_Name
SET column_name = 'Prasad'
END
But it would be same for all the records and is it the desired functionality you are looking at?
Prasad Bhogadi
www.inforaise.com
September 19, 2006 at 5:58 am
Prasad assumed you would pass the value in as a parameter to the stored procedure. If this is the case, your SQL command to run the SP would look like:
Exec sp_Update_TableName 'Value'
if "value" is a character data type, else
Exec sp_Update_TableName Value
if it is numeric.
September 19, 2006 at 6:00 am
Perfect that works just like I want it. The thing is is that we have an application that does not recognize alpha characters so I am changing it in SQL after it is populated. Thanks for your help!!
Shawn
September 19, 2006 at 6:04 am
Probabaly you may not want to use sp_ as prefix for you stored procedure as it is meant for the system stored procedures. I assume Monte just used it for an example purpose.
Prasad Bhogadi
www.inforaise.com
September 19, 2006 at 6:09 am
Prasad,
I actually used your example so I didn't use sp_ as prefix. Thanks alot!!
Shawn
September 19, 2006 at 6:35 am
If you want this to happen to every value that is entered into the database, you may want to consider an insert and/or update trigger. This is code that is run whenever a statement modifies data (insert, update, delete). This way your change happens immediately, and you don't have to wait until after the scheduled stored proc is run.
September 19, 2006 at 9:06 am
I have not set up a trigger before but that sounds like it would actually be better. Can you help me out with the syntax of that?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply