| Author |
 Topic  |
|
|
ashishmishra81
Starting Member
India
5 Posts |
 Posted - 04/21/2008 : 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
Sweden
29138 Posts |
Posted - 04/21/2008 : 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
India
5 Posts |
Posted - 04/21/2008 : 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
Sweden
29138 Posts |
Posted - 04/21/2008 : 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
India
22461 Posts |
Posted - 04/21/2008 : 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
India
5 Posts |
Posted - 04/22/2008 : 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
India
55 Posts |
Posted - 04/22/2008 : 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
India
22461 Posts |
Posted - 04/22/2008 : 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 |
|
|
| |
Topic |
|
|
|
problem in insertion table variable
