SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 problem in insertion table variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashishmishra81
Starting Member

India
5 Posts

Posted - 04/21/2008 :  03:00:21  Show Profile  Visit ashishmishra81's Homepage  Reply with Quote
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
30282 Posts

Posted - 04/21/2008 :  03:57:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

ashishmishra81
Starting Member

India
5 Posts

Posted - 04/21/2008 :  04:09:12  Show Profile  Visit ashishmishra81's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/21/2008 :  04:33:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 04/21/2008 :  05:39:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ashishmishra81
Starting Member

India
5 Posts

Posted - 04/22/2008 :  01:48:22  Show Profile  Visit ashishmishra81's Homepage  Reply with Quote
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
Go to Top of Page

dass05555
Yak Posting Veteran

India
55 Posts

Posted - 04/22/2008 :  02:56:02  Show Profile  Reply with Quote
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+')')


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 04/22/2008 :  03:23:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000