Query help - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-12 : 13:42:54

Please correct me in getting the below output after executing the query:

UPDATE Employees SET ST_NAME = UPPER('STNAME')
UPDATE Employees SET ST_NAME = UPPER('STNAME'),
COUNTYNAME = UPPER('COUNTYNAME')

Below is my query:

declare @TblName varchar(50)
declare @Col1 varchar(50)
declare @Col2 varchar(50)
declare @Col3 varchar(50)
declare @Col4 varchar(50)
declare @numofcol int

set @TblName ='Employees'
set @Col1 ='STNAME'
set @Col2 ='COUNTYNAME'
set @Col3 =Null
set @Col4 =null
set @numofcol =2

declare @vsSQL varchar(8000),
@Col varchar(50),
@icol int,
@scol varchar(50)

SET @icol = 1
set @vsSQL = 'UPDATE ' + @TblName +
' SET ' + @Col1 +' = UPPER(''' +@Col1 + ''')'
print @vsSQL

WHILE @icol < @numofcol
BEGIN
set @icol = @icol + 1
select @scol = CONVERT(varchar(50), @icol)

SET @Col = '@Col' + @scol

set @vsSQL = @vsSQL + ', ' + @Col + ' = UPPER(''' +@Col + ''')'

print @vsSQL
END

Thanks for your help in advance

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-12 : 15:21:33

Why are you doing this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2007-02-13 : 07:54:04

i think it build the same... but when u execute it use sp_executesql

======================================
Ask to your self before u ask someone

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 08:09:02

[code]
set @vsSQL = 'UPDATE ' + @TblName + ' SET ' + @Col1 + ' = UPPER(''' + @Col1 + ''')'
if @Col2 is not null set @vsSQL = @vsSQL + ',' + @Col2 + ' = UPPER(''' + @Col2 + ''')'
if @Col3 is not null set @vsSQL = @vsSQL + ',' + @Col3 + ' = UPPER(''' + @Col3 + ''')'
if @Col4 is not null set @vsSQL = @vsSQL + ',' + @Col4 + ' = UPPER(''' + @Col4 + ''')'
print @vsSQL

WHILE @icol < @numofcol
BEGIN
set @icol = @icol + 1
select @scol = CONVERT(varchar(50), @icol)

SET @Col = '@Col' + @scol

set @vsSQL = @vsSQL + ', ' + @Col + ' = UPPER(''' +@Col + ''')'

print @vsSQL
END

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 08:52:39

What if @Col1 is NULL also?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 09:05:51

then @vsSQL will be NULL and nothing will get executed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources