Update in sqlserver 2000

[jrprogr]

I want to write a generic stored procedure in sqlserver 2000.which should update the data in uppercase..it should not hard coded as below.
Please help me in rewriting the generic code..when tablename or field passed it should update in the respective tables.


CREATE Procedure dbo.UpdateUP
@Tablename Varchar(100),
@field1 varchar(50),
@field2 varchar(50),
@field3 varchar(50),

As



IF @Tablename= 'Customer' and (@field1 is null or @field1='fname') and (@field2 is null or @field2='lname')

UPDATE [Customer]
SET [fname] = UCase([@field1])
, [lname] =UCase([@field2])

Else if @Tablename= 'City' and (@field1 is null or @field1='street') and (@field2 is null or @field2='street1') and (@field3 is null or @field3='street3')

end if
end if

 

[GeertVerhoeven]

Hi,

You can do this by writing a dynamic sql statement and then execute it via the sp_sqlexec stored procedure. You can retrieve the columns of a table by querying the sys.columns table.

For example: select * from sys.columns where object_id = object_id('TABLE1')

NOTE: the maximum characters you can have is 8000 so this means that length of your statement is limited.

Greetz,

Geert


Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[jrprogr]

Thanks greetz..

I have rewritten the code .Please correct me to enhance it to take 3 or 4 columns..

Create PROC dbo.dbo.UpdateUP
@TblName varchar(100),
@Col1 varchar(50)
AS
SET NOCOUNT ON
declare @vsSQL nvarchar(4000)
set @vsSQL = 'UPDATE ' + @TblName + ' SET ' + @Col1 + ' = UPPER(' + +@Col1 + ')'
print @vsSQL
EXEC sp_executesql @vsSQL

 

[gmmastros]

jrprogr,

I'm curious to know why you want to convert your data to uppercase. In my opinion, if the user wants to enter data in lower case or even Mixed Case, then you should be able to accomodate that.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[GeertVerhoeven]

Hi jrprogr,

What I would do is to create a cursor that performs a query on the sys tables. This will then retrieve a row for each column in your table.

Within your cursor you will then build your update statement as a dynamic query.

When all the columns are added, you can execute the query with the sp_executesql.

Since you don't know the number of columns in advance, you can have the issue that the statement is going beyond 8000 characters.

Greetz,

Geert



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[jrprogr]

I did also surprised by the requirement.Still i am also cursiorly asking my manager abt conversion reqs,i didn't get the answer yet.But the data was already in tables has mixed case.Now wants to converts data already stored from mixed case to upper case..
Do you have any suggestion on above procedures to get the desired results..

Thanks for your help in advance..

 

[jrprogr]

Greetz,

I know the column limitation for my requirements it shouldn't go beyond 8 fields..
With dynamic sql will it work..

 

[gmmastros]

Run this query....

Select 'Update [' + Table_Name + '] Set [' + Column_Name + '] = Upper([' + Column_Name + '])'
From   Information_Schema.columns
Where  Data_Type Like '%char'

In the output window, you will see multiple records returned. Simply copy/paste from the output window in to a new query analyzer window. Then, check to make sure that this will ONLY update those table/columns that you want updated and then run the query.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[GeertVerhoeven]

Yes, then there is no problem.

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog