Dynamic SQL Issue

When I execute the following stored procedure I get the
error: 'Invalid operator for data type. Operator equals
subtract, type equals varchar.'
SQL Server thinks I'm trying to subtract the mobile_phone
instead of adding dashes between the numbers.
Here is my stored procedure:
---
create PROCEDURE SelectSortedUsers
@.SortColumn varchar(70)
, @.SortDirection char(4)
AS
declare @.sqlstring varchar(2000);
set @.sqlstring = 'select u.last_name
, u.logon , territory
, r.short_description as Role
, r.role_key
, u.active
, CONVERT(varchar,u.last_login_dt,101) as
last_login_dt
, u.email
, substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) as mobile_phone
from users u
inner join roles r
on u.role_key = r.role_key
order by u.' + @.SortColumn + ' ' + @.SortDirection
exec (@.sqlstring);
=========================================================
How can I add dashes for mobile phone?
Thanks.
DarinYou need to surround strings with single quotes (you will probably see the
problem if you use PRINT @.sql instead of EXEC(@.sql))
, ''' + substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Darin Browne" <db@.nospam.com> wrote in message
news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
> When I execute the following stored procedure I get the
> error: 'Invalid operator for data type. Operator equals
> subtract, type equals varchar.'
> SQL Server thinks I'm trying to subtract the mobile_phone
> instead of adding dashes between the numbers.
> Here is my stored procedure:
> ---
> create PROCEDURE SelectSortedUsers
> @.SortColumn varchar(70)
> , @.SortDirection char(4)
> AS
> declare @.sqlstring varchar(2000);
> set @.sqlstring = 'select u.last_name
> , u.logon , territory
> , r.short_description as Role
> , r.role_key
> , u.active
> , CONVERT(varchar,u.last_login_dt,101) as
> last_login_dt
> , u.email
> , substring(u.mobile_phone, 1, 3) + '-' +
> substring(u.mobile_phone, 4, 3) + '-' +
> substring(u.mobile_phone, 7, 4) as mobile_phone
> from users u
> inner join roles r
> on u.role_key = r.role_key
> order by u.' + @.SortColumn + ' ' + @.SortDirection
> exec (@.sqlstring);
> =========================================================> How can I add dashes for mobile phone?
> Thanks.
> Darin|||Aaron, thanks for your quick reply.
Applying your suggestion, I get an error because Server
doesn't know what table 'u' is aliasing because it's now
outside the dynmaic string where 'u' is aliased.
I've tried moving the 3 quotes around to find the perfect
spot but to no avail.
Any ideas?
Thanks.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>|||It's working!
Thanks for your help.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>