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 |
|
sqldba2k6
Posting Yak Master
176 Posts |
 Posted - 2007-02-08 : 22:37:36
|
| 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.usp_UpperModify @Tablename Varchar(100), @field1 varchar(50), @field2 varchar(50), @field3 varchar(50), AsIF @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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-09 : 03:12:58
|
quote:
Originally posted by khtan
if you want to do this, you can only resort to using Dynamic SQL. See here http://www.sommarskog.se/dynamic_sql.html
KH
Which is not advisable at all !  Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-02-09 : 14:37:52
|
| 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)ASSET NOCOUNT ONdeclare @vsSQL nvarchar(4000)set @vsSQL = 'UPDATE ' + @TblName + ' SET ' + @Col1 + ' = UPPER(' + +@Col1 + ')'print @vsSQLEXEC sp_executesql @vsSQL |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-02-09 : 15:05:40
|
and an input paramtere for each column and add it to the update list separated with commas.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Generic storedprocedure