'varchar' help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

'varchar' help

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 06:58:19

Hi all..
One doubt for me .. Can I use '&' in varchar...?

That means when a user name created like this 'w&s' I got a error as like this incorrect syntax near '&'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 06:59:27

Show the code that is giving the error please.

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-25 : 07:00:53

I Dont think so...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 07:01:18

alter procedure [dbo].[sp_create_tables]
(
@Ch_BoxList varchar(50)
)
as
begin
begin
declare @date datetime

DECLARE @sql VARCHAR(1000)

SET @sql ='select * into tbl_'+@Ch_BoxList+'_merchant from tbl_master_merchant where Merchant =''' +@Ch_BoxList+ ''' and updated_date=getdate()'
EXEC (@sql)
end
begin

SET @sql ='select * into tbl_'+@Ch_BoxList+'_product from tbl_master_product where client_name =''' +@Ch_BoxList+ ''' and updated_date=getdate()'
EXEC (@sql)
end
end

-- exec [sp_create_tables] 'j&r'

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 07:12:53

I received this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '&'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '&'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 07:16:56

It is not a good idea to have a table named tbl_j&r_merchant or tbl_j&r_product.
In this case the tablename has to be in square brackets like this: [tbl_j&r_merchant]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 07:18:42

try this way:
SET @sql ='select * into tbl_[[]'+@Ch_BoxList+']_merchant from tbl_master_merchant where Merchant =''' +@Ch_BoxList+ ''' and updated_date=getdate()'

No, you're never too old to Yak'n'Roll if you're too young to die.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 07:22:35

Hey how to make square brackets in table name ..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 07:24:37

I have already posted an example above...

No, you're never too old to Yak'n'Roll if you're too young to die.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 07:29:20

Sorry ..

While executing your query i got this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '['.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '['.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 07:36:38

Sorry, I was wrong.
Try like this:
SET @sql ='select * into [tbl_'+@Ch_BoxList+'_merchant] from tbl_master_merchant where Merchant =''' +@Ch_BoxList+ ''' and updated_date=getdate()'

No, you're never too old to Yak'n'Roll if you're too young to die.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 07:43:05

Hey this also creates an error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'tbl_j&r_merchant'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 07:49:16

I've tested it and it works.
Please show the code that is giving the error.

No, you're never too old to Yak'n'Roll if you're too young to die.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-25 : 07:53:31

SET @sql ='select * into [tbl_'+@Ch_BoxList+'_product] from tbl_master_product where client_name =''' +@Ch_BoxList+ ''' and updated_date=getdate()'

Received error But I cleared it Thank You for your help it is my mistake ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-25 : 07:57:45

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-28 : 10:44:10

Also see why it is bad practice to send object name as parameter
www.sommarskog.se/dynamic_sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources