stored ptocedure 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)

stored ptocedure help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-11 : 19:29:57

I m getting syntax error...please help 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 20:32:38

1. nvarchar can have max of 4000 characters
2. what is data type number ?
3. Why Dynamic SQL ? Not advisable. http://www.sommarskog.se/dynamic_sql.html


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

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

    WHILE @icol < @numofcol
    BEGIN
        set    @icol = @icol + 1
        select @scol = CONVERT(varchar(50), @icol)
        set    @vsSQL = @vsSQL + ', ' + @Col + @scol + ' = UPPER('+ @Col + @scol + ')'
    END

    -- this not required. You would have encounter error if you try to assign more than 4000 chars to the @vsSQL 
    IF LEN(@vsSQL) < 8000
        EXEC sp_executesql @vsSQL
    ELSE
    BEGIN
         RAISERROR ('LENGTH OF SQLSTRING HAS EXCEEDED THE MAXIMUM VALUE',10,1)
    END

EDIT : over deleted

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-11 : 22:07:27

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 'TENANT','CLSTNAME','CFSTNAME','','',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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 22:30:43

what is the @Col for ? It is not assign with any value.


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

Please also explain what are you trying to do here ? I can only help to fix the basic syntax

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-11 : 22:54:23

I am just trying to create a SQL to update the table with the upper case values . You pass the table name
and pass any number of field names for that table. Then the stored procedure creates dynamic SQL to update the table to the extent of fields passesd

@vsSQL = @vsSQL + ', ' + @Col + @scol + ' = UPPER('+ @Col + @scol + ')'
IN this statement, @Col is the variable name and @scol is the number in the loop @Col+2 will be @Col2 which should be the second field in the parameter list. But that is the not case.

I tried '@Col' (literal) + @scol
even then it is not making any diference

Please help
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 23:01:37

Still don't quite understand the use of @Col there.

Maybe you can post a the related table structure, some sample data and the result that you want.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-12 : 11:07:15

Thanks for your response..

I need the help in getting the below output after executing the query:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources