Stored Procedure Com select SQL SERVER
Good night, I'm trying to create a stored procedure that will fetch one of the data in a select, the reason is because I have a table of items, and I need to enter all the codes of the items in another table.
The idea is + - this: In the item table, only the "Itemcode" field matters, but it has several fields, and has about 170 thousand rows, each with a different itemcode.
In the ItensUsuario table has the user's link with the item and some other information.
I need to create a stored procedure that will insert all itemcode items into the user items.
I tried some ways, but gave error in converting varchar to int (I believe it is due to the select I made)
create procedure inserirtodositens
@idusuario int,
@idproduto int
as
begin
insert into xx.dbo.itensusuario
values (@idusuario, @idproduto, 0, getdate(),0,-1)
end
execute inserirtodositens 594, 'select itemcodigo from xx.dbo.itens'
The error that returns is " Error converting data type varchar to int."
Caso eu mude o "@idproduto int" para
"idproduto varchar(8000) = NULL"
The error that returns is " Conversion failed when converting the varchar value 'select itemcodigo from XX.dbo.items ' to data type int."
1 answers
Bruno,
The problem is due to the fact that you are passing a string (varchar) to a parameter that expects a number (int)
Your "inserirtodositens" procedure has two paramatros: @ idusuario int, and @ idproducto int
The procedure call you TA passing 594 TO USER ID and a command " select itemcodigo from XX. dbo.items " that will actually fail because @idproducto expects a numeric value.
To achieve your goal, try the following :
create procedure inserirtodositens
@idusuario int
as
begin
insert into xx.dbo.itensusuario
select @idusuario, itemcodigo, 0, getdate(),0,-1 from xx.dbo.itens
where
idusuario = @idusuario
end
Execute insertodositens 594
To whereas in the table has the idusuario field.