0

How to put singl quote one an SQL query string.

How to put singl quote one an SQL query string.

18th Mar 2022, 9:33 AM
Akang Toshi
Akang Toshi - avatar
17 odpowiedzi
+ 2
Hello guys i have solved the problem. Umm... I have just declared a variable for the rows1,rows2, rows3 and cancatenate thembin the query. Although, this should have worked in the first place when I tried it didn't. It showed an error code regarding conversion of int to nvarchar. The problem was that SQL string cancatening does not accept any int values. Here is the working query. Alter PROC spInsertRows @TableName nvarchar(10) AS BEGIN Declare @Row1 nvarchar(10) Declare @Row2 nvarchar(10) Declare @Row2 nvarchar(10) Set @Row1='1' Set @Row1='2' Set @Row1='3' Declare @query nvarchar(max) Set @query= 'Insert into '+QUOTENAME(@TableName)+ 'Rows values ' +'('+(CONVERT(nvarchar(12),@Row1)+'),' +'('+(CONVERT(nvarchar(12),@Row2)+'),' +'('+(CONVERT(nvarchar(12),@Row3)+')' Execute (query) END
20th Mar 2022, 11:24 PM
Akang Toshi
Akang Toshi - avatar
+ 1
If i put two single quotes like ''row1'' it gives another error like conversion failed while converting varchar values "row1" to data type int
18th Mar 2022, 9:08 PM
Akang Toshi
Akang Toshi - avatar
+ 1
Sorry, parentheses left over set query= 'Insert into' + @tablename+ ' (Rows) values ('''row1''', '''row2''' , '''row3''') '
18th Mar 2022, 9:12 PM
Monica Garcia
Monica Garcia - avatar
+ 1
Sintax is INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
18th Mar 2022, 9:14 PM
Monica Garcia
Monica Garcia - avatar
+ 1
I think a space is missing after the into set query= 'Insert into ' + @tablename+ ' (Rows) values ('''row1''', '''row2''' , '''row3''') ;'
18th Mar 2022, 9:52 PM
Monica Garcia
Monica Garcia - avatar
0
I am cancatening a sting like set @query= 'Insert into' + @tablename+ ' (Rows) values (' row1'),('row2') , ('row3') ' So how should I do it here ?
18th Mar 2022, 1:01 PM
Akang Toshi
Akang Toshi - avatar
0
Putting the single quote before row1 terminates the string.
18th Mar 2022, 1:02 PM
Akang Toshi
Akang Toshi - avatar
0
Try with set query= 'Insert into' + @tablename+ ' (Rows) values ('''row1'''),('''row2''') , ('''row3''') '
18th Mar 2022, 8:55 PM
Monica Garcia
Monica Garcia - avatar
0
It gives an error saying incorrect syntax near row1
18th Mar 2022, 9:05 PM
Akang Toshi
Akang Toshi - avatar
0
Yes but I want to put the row1, row2 and row3 in different rows and leave the other columns empty .
18th Mar 2022, 9:16 PM
Akang Toshi
Akang Toshi - avatar
0
I am making a table with table name as a varible using paramaters from my other end of the app with 9 columns (Rows,C1,C2...c9) namely. What I want to do is i want to put three rows in the column name Rows and leave the other columns as null. ☺️
18th Mar 2022, 9:22 PM
Akang Toshi
Akang Toshi - avatar
0
Ok, is correct the parentheses for this case INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), ... (value_list_n); Has you tried putting ; at the end? set query= 'Insert into' + @tablename+ ' (Rows) values ('''row1''', '''row2''' , '''row3''') ;'
18th Mar 2022, 9:31 PM
Monica Garcia
Monica Garcia - avatar
0
Yes did just now it's not working 😔
18th Mar 2022, 9:39 PM
Akang Toshi
Akang Toshi - avatar
0
It's there 😊 in my actual code. I think the problem is that the queries as String is doing something strange. It does not want to accept datatype as nvarchar. It works well on int type. If i put ...'insert into ' + QUOTENAME (@TableName)+ ' (Rows) Values (1),(2),(3)'
18th Mar 2022, 9:56 PM
Akang Toshi
Akang Toshi - avatar
0
What is the data type of column Rows?
18th Mar 2022, 10:41 PM
Monica Garcia
Monica Garcia - avatar
0
It's nvarchar
18th Mar 2022, 10:42 PM
Akang Toshi
Akang Toshi - avatar
0
Sorry, just asking, do you really need to use NVARCHAR? if not, probably it's worth trying to use the VARCHAR instead (considering MySQL in tags). Other possible workaround *might* be to check and ensure the character set for the respective field(s) have been properly configured. https://stackoverflow.com/questions/2214901/mysql-equivalent-data-types?lq=1
19th Mar 2022, 5:59 AM
Ipang