I'm baffled by the single quotes with STMT

[Doug Baroter]

Hi,

Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.

EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')',
'COLUMN';
Thanks.

[Erland Sommarskog]

Doug Baroter (qw********@boxfrog.com) writes:

Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.

EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')',
'COLUMN';

You can only pass constants and variables as parameters to stored procedures.
You cannot pass an expression as a parameter, but you must put everything
in variables.

Furthermore, replace() only handles fixed strings, and does not have
any capacbilities to find patterns.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Doug Baroter]

Erland,

Thanks for the quick response. Please my further question below.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Doug Baroter (qw********@boxfrog.com) writes:

Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.

EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')',
'COLUMN';
You can only pass constants and variables as parameters to stored procedures.
You cannot pass an expression as a parameter, but you must put everything
in variables.

Are you saying I should do something like
set @tbuffer = '''+@tbuffer+''';
set @cbuffer = '''+@cbuffer+''';
and then
EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...

Furthermore, replace() only handles fixed strings, and does not have
any capacbilities to find patterns.

Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?

[Erland Sommarskog]

Doug Baroter (qw********@boxfrog.com) writes:

Are you saying I should do something like
set @tbuffer = '''+@tbuffer+''';
set @cbuffer = '''+@cbuffer+''';
and then
EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...
Rather:

SELECT @old_name = 'tbl.' + <whatever>
SELECT @new_name = <whichever>
EXEC sp_rename @old_name, @new_name, 'column'
Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?

Unfortunately, SQL is poor for this task. You are probably better off
if you write some program in Perl, Visual Basic, VBscript, C or whatever
is your favourite client language.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Doug Baroter]

Thanks, Erland, pls see below.
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...

Doug Baroter (qw********@boxfrog.com) writes:

Are you saying I should do something like
set @tbuffer = '''+@tbuffer+''';
set @cbuffer = '''+@cbuffer+''';
and then
EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...
Rather:

SELECT @old_name = 'tbl.' + <whatever>
SELECT @new_name = <whichever>
EXEC sp_rename @old_name, @new_name, 'column'

Unfortunately the tbl name is dynamically determined. But as you
recommended below, I just use a non-SQL language to take care of the
whole problem except one minor one, that is, I haven't got the RegExp
part fully completed.

Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?

Unfortunately, SQL is poor for this task. You are probably better off
if you write some program in Perl, Visual Basic, VBscript, C or whatever
is your favourite client language.

[Erland Sommarskog]

Doug Baroter (qw********@boxfrog.com) writes:

SELECT @old_name = 'tbl.' + <whatever>
SELECT @new_name = <whichever>
EXEC sp_rename @old_name, @new_name, 'column'

Unfortunately the tbl name is dynamically determined.

SELECT @old_name = @tbl + '.' + @column

The point is that when you come to sp_rename you must have a single
value.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Doug Baroter]

OK. Thanks.
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Doug Baroter (qw********@boxfrog.com) writes:

SELECT @old_name = 'tbl.' + <whatever>
SELECT @new_name = <whichever>
EXEC sp_rename @old_name, @new_name, 'column'

Unfortunately the tbl name is dynamically determined.

SELECT @old_name = @tbl + '.' + @column

The point is that when you come to sp_rename you must have a single
value.