Im baffled by the single quotes with STMT

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.Doug Baroter (qwert12345@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

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

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941BEC43659B9Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.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?|||Doug Baroter (qwert12345@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, pls see below.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D8E88A84Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.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.|||Doug Baroter (qwert12345@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK. Thanks.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D616A4DA25Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.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.