problem in insertion table variable

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 2000 Forums

Transact-SQL (2000)

problem in insertion table variable

Author

Topic

ashishmishra81
Starting Member

5 Posts

Posted - 2008-04-21 : 03:00:21

I am try to build a Stored procedure to insert into a table whose name is contain in variable @tname, other field are just variable to supply value to table fields,i have implement simple type of SP for "SELECT" AND "CREATE" ,but this procedure does not work, please help me

CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,form_id)
values ('+ @mass +',' +@to_id + ','+@form_id +') ')
Edit/Delete Message

sonu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 03:57:01

Shouldn't "form_id" be "FROM_ID"?
Also @mass variable need to be enclosed in single quotes.

CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,from_id)
values ('+ quotename(@mass, '''') +',' +@to_id + ','+@form_id +') ')

E 12°55'05.25"
N 56°04'39.16"

ashishmishra81
Starting Member

5 Posts

Posted - 2008-04-21 : 04:09:12

thanks for replay me
but it does not work,'form'is just a column field name in table

quote:
Originally posted by Peso

Shouldn't "form_id" be "FROM_ID"?
Also @mass variable need to be enclosed in single quotes.

CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,from_id)
values ('+ quotename(@mass, '''') +',' +@to_id + ','+@form_id +') ')

E 12°55'05.25"
N 56°04'39.16"

sonu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 04:33:25

Ok. So just ignore the first suggestion.
What happens when you tried the second suggestion.

Also explain why it "doesn't work".

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-21 : 05:39:44

You need to use multiple single quotes if the value is a string
Also make sure to read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

ashishmishra81
Starting Member

5 Posts

Posted - 2008-04-22 : 01:48:22

thanks friend
it now working if i used multiple single quotes in string field which @mass here, if i used three single qoutes nested each other ,then only it works

CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,form_id)
values ('''+@mass+''',' +@to_id + ','+@form_id +') ')

[quote]Originally posted by madhivanan

You need to use multiple single quotes if the value is a string
Also make sure to read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

sonu

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-04-22 : 02:56:02

hi friend,
chk it out this ,i think it would satisfy u...

drop proc insert_mess
go
CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,form_id)
values ('+@mass+','+@to_id+','+@form_id+')')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 03:23:30

quote:
Originally posted by dass05555

hi friend,
chk it out this ,i think it would satisfy u...

drop proc insert_mess
go
CREATE proc insert_mess
@tname nvarchar(50),
@mass nvarchar(250),
@to_id int,
@form_id int
as
exec ('insert into ['+ @tname +'] ( [message],to_id,form_id)
values ('+@mass+','+@to_id+','+@form_id+')')

Read OP's previous reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources