Insert into dynamic column name

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)

Insert into dynamic column name

Author

Topic

Jarhead104
Starting Member

10 Posts

Posted - 2010-04-13 : 04:19:56

Hey!

I'm trying to create a SP which inserts a value into a column defined by a variable but this does not work...

INSERT INTO syncs (@syncvalue) VALUES (GETDATE())

Receiving the following error:
Invalid column name '@syncvalue'.

Is there any possibility to specify the column of a table by using a variable?

Thanks!
Regards,
Jarhead

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-13 : 04:27:41

yes. use dynamic SQL


declare @sql nvarchar(max)
select  @sql = 'INSERT INTO syncs (' + @syncvalue + ') VALUES (GETDATE())'

exec (@sql)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-13 : 04:38:26

that will not work. You have to use dynamic SQL to do it.

KH
[spoiler]Time is always against us[/spoiler]

Jarhead104
Starting Member

10 Posts

Posted - 2010-04-13 : 04:39:34

quote:
Originally posted by khtan

that will not work. You have to use dynamic SQL to do it.

KH
[spoiler]Time is always against us[/spoiler]

Sorry, deleted my post. Know what you are meaning! ;)

Thank you for your help! I will post the solution then...

Jarhead104
Starting Member

10 Posts

Posted - 2010-04-13 : 04:41:27

CREATE PROCEDURE [dbo].[sp_updatesync]
-- Add the parameters for the stored procedure here
@synctable NVARCHAR(30)
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @doit NVARCHAR(max)
SELECT @doit = 'INSERT INTO syncs (' + @synctable + ') VALUES (GETDATE())'

EXEC (@doit)

This works now :)
Thank you khtan!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-13 : 05:02:03

Also make sure you read this
www.sommarksog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources