Stored procedure syntax error sqlserver 2000

[jrprogr]

I am getting syntax error for the below.Please correct me..

CREATE PROCEDURE dbo.spUpdateUp
@TblName varchar(50),
@numofcol number,
@Col1 varchar(50),
@Col2 varchar(50),
@Col3 varchar(50),
@Col4 varchar(50)

AS
SET NOCOUNT ON
declare @vsSQL nvarchar(8000), @Col varchar(50), @icol
number, @scol varchar(50)
SET @icol = 1
set @vsSQL = 'UPDATE ' + @TblName + ' SET ' + @Col1 +' = UPPER(' + +@Col1 + ')'
WHILE @icol < @numofcol
BEGIN
set @icol = @icol + 1
@scol = CONVERT(varchar(50), @icol)
set @vsSQL = @vsSQL + ', ' + @Col + @scol + ' =
UPPER('+ @Col + @scol + ')'
END
IF LEN(@vsSQL) < 8000
EXEC sp_executesql @vsSQL
ELSE
BEGIN
RAISERROR ('LENGTH OF SQLSTRING HAS EXCEEDED THE
MAXIMUM VALUE',10,1)
END

 

[jrprogr]

I have corrected the above SP.
I have compiled the following stored procedure successfully.
But when I am printing the last statement , it shows a blank value It looks like there is a null value that is creating this problem.

Please help..

EXEC spUpdateUp 'TETN','CLNAME','CFNAME','','',2

CREATE PROCEDURE dbo.spUpdateUp
@TblName varchar(50),
@Col1 varchar(50),
@Col2 varchar(50),
@Col3 varchar(50),
@Col4 varchar(50),
@numofcol int
AS
SET NOCOUNT ON
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)
print @scol
set @vsSQL = @vsSQL + ', ' + @Col + @scol + ' = UPPER('+ @Col + @scol + ')'
print @vsSQL
END

 

[bborissov]

I didn't see where you store value to @Col variable.
Also you must add single quotes in your dynamic SQL:

CREATE PROCEDURE dbo.spUpdateUp
       @TblName varchar(50),
       @Col1 varchar(50),
       @Col2 varchar(50),
       @Col3 varchar(50),
       @Col4 varchar(50),
       @numofcol int
AS
SET NOCOUNT ON
declare @vsSQL varchar(8000),
        @Col varchar(50),
        @icol int,
        @scol varchar(50)

SET @icol = 1
set @vsSQL = 'UPDATE ' + @TblName + 
             ' SET ' + @Col1 +' = UPPER([COLOR=red]''[/color]' +@Col1 + '[COLOR=red]''[/color])'
print @vsSQL

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

           ------ SET  @Col  = ????????????????

           set @vsSQL = @vsSQL + ', ' + @Col + @scol + ' = UPPER([COLOR=red]''[/color]'+ @Col + @scol + '[COLOR=red]''[/color])'
print @vsSQL
END

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

 

[jrprogr]

Thanks borislav for your response..

I need the help in getting the below output:

UPDATE Employees SET ST_NAME = UPPER('STNAME')
2
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)
print @scol

SET @Col = '@Col' + @scol

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

-- set @vsSQL = @vsSQL + ', ' + @Col + @scol + ' = UPPER('''+ @Col + @scol + ''')'
print @vsSQL
END