Error at Insert Into Statment - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Error at Insert Into Statment

Author

Topic

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 13:53:33

Dear All,

DECLARE @EMPID INT
DECLARE @EMPNAME char (20)
DECLARE @HIREDATE DATETIME

SET @EMPID = 4
SET @EMPNAME = 'S'

EXEC ('
INSERT INTO DBO.Employee(Emp_ID,Emp_Name)
VALUES(' +@EMPID+ ','+@EMPNAME+')' )

There was an error when execute this statment and i don't know why its happend?
The error statment say
'The name 'S' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.'

SkyDiver

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-27 : 14:02:54

Why are you using dynamic SQL for this? Just use this instead of your EXEC:

INSERT INTO dbo.Employee(Emp_ID, Emp_Name)
VALUES(@EMPID, @EMPNAME)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 14:03:13

And if you want to use D_SQL,try this:-

EXEC ('
INSERT INTO DBO.Employee(Emp_ID,Emp_Name)
VALUES(' +@EMPID+ ','''+@EMPNAME+''')' )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-27 : 14:03:39

quote:
Originally posted by visakh16

try this:-

EXEC ('
INSERT INTO DBO.Employee(Emp_ID,Emp_Name)
VALUES(' +@EMPID+ ','''+@EMPNAME+''')' )

Why? Dynamic SQL is not needed, so we shouldn't provide fixes for it but rather provide the better solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

HellBoy
Starting Member

9 Posts

Posted - 2007-12-27 : 14:08:13

Thanks tkizer i know that i can do this but i want to test something
and i tried visakh16 solution and it done
Thanks alot

SkyDiver

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 14:13:49

quote:
Originally posted by tkizer

quote:
Originally posted by visakh16

try this:-

EXEC ('
INSERT INTO DBO.Employee(Emp_ID,Emp_Name)
VALUES(' +@EMPID+ ','''+@EMPNAME+''')' )

Why? Dynamic SQL is not needed, so we shouldn't provide fixes for it but rather provide the better solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I thought this might be a mock up of original scenario. It seemed like person had simulated this instead of giving the original code snippet.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 01:44:05

quote:
Originally posted by HellBoy

Dear All,

DECLARE @EMPID INT
DECLARE @EMPNAME char (20)
DECLARE @HIREDATE DATETIME

SET @EMPID = 4
SET @EMPNAME = 'S'

EXEC ('
INSERT INTO DBO.Employee(Emp_ID,Emp_Name)
VALUES(' +@EMPID+ ','+@EMPNAME+')' )

There was an error when execute this statment and i don't know why its happend?
The error statment say
'The name 'S' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.'

SkyDiver

As said, you dont need dynamic sql in this case

All about Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Make sure you read that article fully

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources