SP help: error when inserting a value (msg 207)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SP help: error when inserting a value (msg 207)

Author

Topic

oxillery
Starting Member

3 Posts

Posted - 2011-10-25 : 10:56:45

Hi

I'm a student and i'm trying to write a SP in MS SQL. I'm not very familiar with MS SQL but I worked with MySQL before.

I have written a SP that creates a table (if it doesn't exist) and add 2 values in this table.

SP code:


CREATE PROCEDURE addHashValue( @hash1 varchar (16),@hash2 varchar (16), @string varchar (30)) 
	
AS
begin
Declare @SQL varchar(1000)
-- check if table exists
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].['+@hash1+']')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
-- create new table
Select @SQL ='Create table '+@hash1+'( hashValue varchar(16) NOT NULL, solution varchar(30) NOT NULL)'
exec(@sql)
end

-- add values
select @SQL = 'insert into '+@hash1+'(hashValue,solution) values('+@hash2+','+@string+')'
exec(@SQL)
end

test code:


DECLARE	@return_value int

EXEC	@return_value = [dbo].[addHashValue]
		@hash1 = N'e10adc3949ba59ab',
		@hash2 = N'be56e057f20f883e',
		@string = N'123456'

SELECT	'Return Value' = @return_value

GO

When i try to run this I get the following error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'be56e057f20f883e'

I've been looking for some definitions about this error, but found nothing that resolved the problem.
I also tried to replace the insert with the actual insert (so without using parameters ) and the error remains the same.

I hope someone can help
Grtz Oxillery

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-26 : 06:50:52

Hello i give you ,May be dummy Answer ,But Please try it ,May be useful
Do you tried to use double quotation instead of single one ,So use (") instead of (')

paul Tech

oxillery
Starting Member

3 Posts

Posted - 2011-10-26 : 09:29:38

Where do you mean?

In my SP or test code?

grtz oxillery

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-26 : 09:45:04

select @SQL = 'insert into '+@hash1+'(hashValue,solution) values('''+@hash2+''','''+@string+''')'
exec(@SQL)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

oxillery
Starting Member

3 Posts

Posted - 2011-10-26 : 09:55:15

That did the trick :D

I was looking for a way to insert a ' as text.

Thanks for the help :)
Grtz Oxillery

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 03:44:36

quote:
Originally posted by oxillery

That did the trick :D

I was looking for a way to insert a ' as text.

Thanks for the help :)
Grtz Oxillery

see below to understand how ' works

http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources