June 5, 2011 at 6:17 am
hi.
i have problem about a query with dinamic insert.
i have a few table that i set their column names same. becaause their structure is same and i want to write one nsert,pdate query for this same structured tables.
my query is this:
proc name...
@baslik nvarchar(max)=null,
@icerik nvarchar(max)=null
AS
BEGIN
declare @sqlcum nvarchar(max)
set @sqlcum ='insert into makale (baslik,icerik) values(N'''+@baslik+ ''',N'''+@icerik+ ''')'
exec(@sqlcum)
................
this works ok. problem is that ' character. i use fckeditor. If i use this dinamic insert query ' character gives error. how can i solve this problem?
Thanks..
June 5, 2011 at 6:34 am
sa.ordekci (6/5/2011)
this works ok. problem is that ' character. i use fckeditor. If i use this dinamic insert query ' character gives error. how can i solve this problem?
I can not understand this,can you give some more Detail.
June 5, 2011 at 6:45 am
proc name...
@baslik nvarchar(max)=null,
@icerik nvarchar(max)=null
AS
BEGIN
declare @sqlcum nvarchar(max)
set @sqlcum ='insert into makale (baslik,icerik) values(N'''+@baslik+ ''',N'''+@icerik+ ''')'
exec(@sqlcum)
................
my dinamic query is this. i use this on a web site project. i use textbox, fckeditor or saving datas. for example when i wrote a writing like this:
Izmir'in en güzel yerleri..." s you see, i use ' character after Izmir. if i use this character, dinamic query gives error:
"Incorrect syntax near the keyword 'in'.
Unclosed quotation mark after the character string ')'. "
June 5, 2011 at 6:51 am
Yes,Cause ' character is used to start and end String.
when you want to insert that value in sql sever,you must provide 'Izmir''in en güzel yerleri' to sql server.
instead of 'Izmir'in en güzel yerleri'.see the difference in both string 'Izmir' and 'Izmir''.
June 5, 2011 at 6:57 am
Run following code it will execute successfully
declare @baslik nvarchar(max)=null
SET @baslik='Izmir''in en güzel yerleri'
Select @baslik
while when you run following code you will get error
declare @baslik nvarchar(max)=null
SET @baslik='Izmir'in en güzel yerleri'
Select @baslik
June 5, 2011 at 7:01 am
but i enter baslik from textbox. that is @baslik value can change. also if i use as below:
@baslik nvarchar(max)=null,
@icerik nvarchar(max)=null
AS
BEGIN
insert into makale values(@baslik,@icerik)
END
normal proc not dinamic.
it runs and accept ' character.
June 5, 2011 at 12:34 pm
You might want to look at the Quotename() function in BOL.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 7, 2011 at 6:58 am
another option is to use replace as in:
declare @a1 varchar(50)
declare @sql varchar(255)
select @a1 = 'Izmir''in en güzel yerleri'
-- char(39) = ' this means it replaces a single quote with 2 single quotes
select @sql = 'Select ' + CHAR(39) + Replace(@a1, CHAR(39),CHAR(39)+CHAR(39)) + CHAR(39)
exec(@sql)
-- output from exec is: Izmir'in en güzel yerleri
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply