CODEWITHSUNDEEP
sql-servert-sqlspecial-characters
CiucaS
CiucaS

Reputation: 2128

"." char throws error in sql-server

This shouldn't be a very hard to solve problem, but I'm out pages on google to search for the solution.

I have a database with users that have their names like 'firstname.lastname'. Example : john.smith , b.obama , v.putin and so on.

I try to add these users some roles within a SP

This is my SP : 

ALTER PROCEDURE [dbo].[sp_adaugaUserInRol]
    -- Add the parameters for the stored procedure here
    @Rol varchar(50), 
    @User varchar(32)
AS
BEGIN
declare @sRol varchar(50),
        @sUser varchar(32);
set @sUser = LTRIM(RTRIM(@User))
set @sRol = LTRIM(RTRIM(@Rol))
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    print(' ALTER ROLE ' + @sRol  + ' ADD MEMBER  ' +@User )



END

If I use a string for user like 'john' , 'obama' it works, but if I use it as 'john.smith' or 'b.obama' I get the error Incorrect syntax near '.'. , which is very logical as SQL thinks john is a table and smith is a column ( my guess ).

How can I tell SQL that john.smith is a string?

Upvotes: 1

Views: 59

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

print(' ALTER ROLE [' + @sRol  + '] ADD MEMBER  [' +@User + ']' )

Upvotes: 4

Z .
Z .

Reputation: 12837

put it in square brackets:

[john.smith]

Upvotes: 5

Related Questions