469,167 Members | 1,279 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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 2963
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.