471,887 Members | 1,049 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,887 software developers and data experts.

I'm 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.
Jul 20 '05 #1
6 3011
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
Jul 20 '05 #2
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?
Jul 20 '05 #3
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
Jul 20 '05 #4
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.

Jul 20 '05 #5
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
Jul 20 '05 #6
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.

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by sinister | last post: by
11 posts views Thread by Jakanapes | last post: by
4 posts views Thread by sankofa | last post: by
5 posts views Thread by Joel | last post: by
2 posts views Thread by claus.hirth | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.