Error converting data type varchar to float in stored procedure

Question

I've tried a lot of ways in the forum, but did not succeed. I have a table called provinces. The columns in this table I want to access the parameters are entered manually. As can be seen, but I get the error in the title. I'm SQL code is as follows

create proc test 
(
@column_city varchar (500), 
@city_n varchar (500) 
) 
as 
begin 
declare @sql nvarchar (1000) 
set @sql = 'SELECT' + @ column_city + 'FROM a distance where city =' +city_n
exec (sql) 
end 

exec test "istanbul", "chicago"

When I run the above code "Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float. "Error I'm getting

Istanbul is the name of a column. ankara sent to the city column is the value. two cities where the distance will be calculated. Normally when I wrote the sql query is running. Send to Istanbul where I value my goal to write and work will be provided

select istanbul from distance
where CITY_NAME = 'ankara'

I've been descriptive

There's so much wrong with that query, it's unbelievable. SQL doesn't recognize double quotes ("), but instead you can use single quotes (') for text strings (you did it right in SET @ sql = 'SELECT', but not in the EXEC line). You also need to add a @ for the variable names. I.e. @column_city, and @sql, not column_city and @ sql You called the proc test, and execute test4. Could you just copy the actual code, rather than whatever it is you copied now? — SchmitzIT
what is the value of istanbul? do you have spaces inside your data? — Kevin Cook

Rahul Tripathi Rahul Tripathi · Accepted Answer · 2014-08-26T17:02:03

Your query is syntactically incorrect. Try this:

create proc test 
(
@column_city varchar (500), 
@city_n varchar (500) 
) 
as 
begin 
declare @sql nvarchar (1000) 
set @ sql = 'SELECT QUOTENAME(''' + @ column_city + ''') FROM distance where city =' +@ city_n 
exec (@sql) 
end

And then you need to execute it like this:

exec test 'istanbul', 'chicago'

Error converting data type varchar to float in stored procedure

1 Answers