Click here to Skip to main content
14,732,936 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
i created store procedure that create table and insert variables in the table
but i get error when i try to put the variable in
can some one tell me what is wrong white my code?

ALTER PROC [dbo].[SqldataTable2]
@tabelname nvarchar(50),
@id real,
@prod nvarchar(50),
@category varchar(50),
@price real,
@instock bit,
@count real,
@DateUpdate datetime,
@description nvarchar(max) 


as


   BEGIN
    
  
DECLARE @SQLString NVARCHAR(MAX)

 set @SQLString='
 

INSERT INTO '+@tabelname+' (ID,ProductName,Category,Price,Instock,[Count],DateUpdate,[Description])
VALUES (@id,@prod,@category,@price,@instock,@count,@DateUpdate,@description)
 
Select *from '+@tabelname+' 
order by ID Asc'
 EXEC (@SQLString)
 end;

-- and when i put a variable in the store procedure it tell:
-- "Must declare the scalar variable "@id"."
SqldataTable2 'dbo.DBT',66,'mazda','buty',786567,'true',88,'1978','buuu'
Posted
Comments
[no name] 26-Dec-13 21:24pm
   
i done that now it said
"Error converting data type nvarchar to real.
"
Christian Graus 27-Dec-13 0:09am
   
The errors always mean what they say. The first error was telling you that you had not passed in the variables, only their names. The second means you're passing in varchar and the column type is real, or vice versa. If you told us your table definition, we could tell you exactly, but if you just read the errors, it tells you all you need to know.

You need to parse in your variables as well:

'INSERT INTO '+@tabelname+' (ID,ProductName,Category,Price,Instock,[Count],DateUpdate,[Description])
VALUES (' + @id + ',...)'
   
Comments
Maciej Los 27-Dec-13 2:02am
   
+5
Are you the same person who asked about random table names last time ? Why are you doing this ? What are the tables named after ? Why can't you have the one table with an extra column, which denotes what is currently denoted in your system by different tables ? Then you can write sane, intelligent code. This is a mess.
   
Comments
[no name] 27-Dec-13 0:43am
   
nop that not me
Christian Graus 27-Dec-13 0:57am
   
OK, no matter. What you're doing is still utterly broken. Why do you need dynamic table names ? What does each table represent ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900