Query Help

[jrprogr]

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..

 

[SQLBill]

So what does the PRINT @vsSQL return?

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Apparently, you want to convert your data to upper case. Are you trying to do this 1 time (to 'fix' the data), or is this something you need to run repeatedly?


-George

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

 

[sunila7]

One way to get this working is.

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

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

SET @SQL = 'UPDATE ' + @TblName + ' SET ' 

SELECT @SQLF =  @SQLF  + CASE WHEN F IS NULL THEN '' ELSE  F + '= UPPER(' + F  + '),' END 
FROM
(SELECT @Col1 F
UNION
SELECT @Col2
UNION
SELECT @Col3
UNION
SELECT @Col4
) TBL

SELECT @SQLF

SET @SQL  = @SQL + @SQLF

SELECT  SUBSTRING(@SQL, 1, LEN(@SQL) - 1)

Sunil

 

[jrprogr]

George, currently it will be used for one time fix and later on it may be used repeatedly..

 

[bborissov]

O GOD!
NOW I understand what you trying to do. No you can't dynamically access the variables

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

IF @numofcol < 1
   RETURN

declare @vsSQL varchar(8000)

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

IF @numofcol = 2
   set @vsSQL = @vsSQL +','+
                       @Col2 +' = UPPER(''' +@Col2 + ''')'

IF @numofcol = 3
   set @vsSQL = @vsSQL + ',' +
                       @Col3 +' = UPPER(''' +@Col3 + ''')'

IF @numofcol = 4
   set @vsSQL = @vsSQL + ',' +
                       @Col4 +' = UPPER(''' +@Col4 + ''')'


EXEC (@vsSQL)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

I thought so. Look at my response in this thread: thread183-1332711

When you run the code in Query Analyzer, you will see a series of update queries in the output window. Copy/paste from the output window in to a new window and then run the resulting updates.

-George

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