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 |
|
Aerathi
Starting Member
14 Posts |
 Posted - 2006-01-23 : 13:14:19
|
| I have a stored procedure with a *ton* of input parameters. All the varchar params need capitalized :/Is there any way to dynamically loop through all of the input parameters and then use the UPPER to capitalize them? |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-23 : 14:15:19
|
| U mean, U want to change the variable name or data input to be "First letter in Upper Case" ?If u want the first one, u can do it by a word processor as MS word, or by writing a function urselfIf u want to change data, u may have to write a function to get varchar and return the same after changing the first letter to Upper case and after defining of parameters, call another function which changes each of the data element to ur need |
 |
|
|
Aerathi
Starting Member
14 Posts |
Posted - 2006-01-23 : 16:47:39
|
| I need to change the data. Something like this is as far as I've gotten.[CODE] SET NOCOUNT ON DECLARE COFQ_Cursor CURSOR LOCAL FOR SELECT c.Name FROM SYSOBJECTS O INNER JOIN SYSCOLUMNS C ON O.ID = C.ID where o.name = 'GetQuote' and c.type = 39 OPEN COFQ_Cursor FETCH NEXT FROM COFQ_Cursor INTO @ParamName WHILE @@FETCH_STATUS = 0 BEGIN --I would need some kind of dsql statement here to change the data stored in that parameter to refelct something like: --SET @InputParameter1 = UPPER(@InputParameter1) --I just don't know how to work the parameter so that I'm able to dynamically alter each one's data FETCH NEXT FROM COFQ_Cursor INTO @ParamName END[/CODE] |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-23 : 20:19:17
|
| My suggestion is different!!Its like followsAlter Procedure ... ( @a varchar, @b varchar, @c Varchar .....)as-- Before any other codeSet @a = Ucase(@a)Set @b = Ucase(@b)Set @c = Ucase(@c)............. rest of the codeIf u couldn't type the "ton" urselfWrite another program considering the text in parameters list (eg. @a varchar, @b varchar, @c Varchar ....)and convert that list to the assign, function call lines list(eg.Set @a = Ucase(@a)Set @b = Ucase(@b)Set @c = Ucase(@c)..........)U can make use of some other tool like Excel to perform this easily.[Note, once done it is permanent and u don't need to get the help of any other program] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-24 : 01:13:58
|
| Why do you want to change parameters to capitalised?MadhivananFailing to plan is Planning to fail |
|
|
|
Aerathi
Starting Member
14 Posts |
Posted - 2006-01-24 : 09:40:09
|
quote:
Originally posted by madhivanan
Why do you want to change parameters to capitalised?MadhivananFailing to plan is Planning to fail
Well, I inherited this project where we are interfacing with biztalk to another company. The parameter list is silly huge, and the database standard is to store everything in all caps. Sadly some parts of our system (such as certain crystal reports) are case sensative in their searches.I know I could just go in and capitalize every parameter myself, but that means that others in the future (if parameters are added or removed) will have to do that as well. It's much better in my opinion to do this dynamically so that it can be done just once.Believe me I know in the future that someone somewhere will add another input parameter, not add the UPPER to it, and it will make it into production and reports will not work, and it will all trace back to this. |
|
|
|
|
|
|
|
|
Looping through input parameters?