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(+@c buffer+','%[^A-Za-z0-9_#$@]%','')',
'COLUMN';


Thanks.

[Erland Sommarskog]

Re: I'm baffled by the single quotes with STMT

Doug Baroter (qwert12345@box frog.com) writes:[color=blue]
> 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(+@c buffer+','%[^A-Za-z0-9_#$@]%','')',
> 'COLUMN';[/color]

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/techinfo/productdoc/2000/books.asp

[Doug Baroter]

Re: I'm baffled by the single quotes with STMT

Erland,

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

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns941BEC 43659B9Yazorman @127.0.0.1>...[color=blue]
> Doug Baroter (qwert12345@box frog.com) writes:[color=green]
> > 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(+@c buffer+','%[^A-Za-z0-9_#$@]%','')',
> > 'COLUMN';[/color]
>
> 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.[/color]
Are you saying I should do something like
set @tbuffer = '''+@tbuffer+'' ';
set @cbuffer = '''+@cbuffer+'' ';
and then
EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...
[color=blue]
>
> Furthermore, replace() only handles fixed strings, and does not have
> any capacbilities to find patterns.[/color]
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]

Re: I'm baffled by the single quotes with STMT

Doug Baroter (qwert12345@box frog.com) writes:[color=blue]
> Are you saying I should do something like
> set @tbuffer = '''+@tbuffer+'' ';
> set @cbuffer = '''+@cbuffer+'' ';
> and then
> EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...[/color]

Rather:

SELECT @old_name = 'tbl.' + <whatever>
SELECT @new_name = <whichever>
EXEC sp_rename @old_name, @new_name, 'column'
[color=blue]
> 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?[/color]

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/techinfo/productdoc/2000/books.asp

[Doug Baroter]

Re: I'm baffled by the single quotes with STMT

Thanks, Erland, pls see below.
Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns941D8E 88A84Yazorman@1 27.0.0.1>...[color=blue]
> Doug Baroter (qwert12345@box frog.com) writes:[color=green]
> > Are you saying I should do something like
> > set @tbuffer = '''+@tbuffer+'' ';
> > set @cbuffer = '''+@cbuffer+'' ';
> > and then
> > EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...[/color]
>
> Rather:[/color]
[color=blue]
> SELECT @old_name = 'tbl.' + <whatever>
> SELECT @new_name = <whichever>
> EXEC sp_rename @old_name, @new_name, 'column'[/color]
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.
[color=blue][color=green]
> > 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?[/color]
>
> 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.[/color]

[Erland Sommarskog]

Re: I'm baffled by the single quotes with STMT

Doug Baroter (qwert12345@box frog.com) writes:[color=blue][color=green]
>> SELECT @old_name = 'tbl.' + <whatever>
>> SELECT @new_name = <whichever>
>> EXEC sp_rename @old_name, @new_name, 'column'[/color]
>
> Unfortunately the tbl name is dynamically determined.[/color]

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/techinfo/productdoc/2000/books.asp

[Doug Baroter]

Re: I'm baffled by the single quotes with STMT

OK. Thanks.
Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns941D61 6A4DA25Yazorman @127.0.0.1>...[color=blue]
> Doug Baroter (qwert12345@box frog.com) writes:[color=green][color=darkred]
> >> SELECT @old_name = 'tbl.' + <whatever>
> >> SELECT @new_name = <whichever>
> >> EXEC sp_rename @old_name, @new_name, 'column'[/color]
> >
> > Unfortunately the tbl name is dynamically determined.[/color]
>
> SELECT @old_name = @tbl + '.' + @column
>
> The point is that when you come to sp_rename you must have a single
> value.[/color]