Stored Procedure With Select SQL SERVER

Question

Good evening, 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 field "Itemcodigo" interests, however it has several fields, and has about 170thousand lines, each with a different itemcodigo.

In the table Itensusuario has the user’s link with the item and some other information.

I need to create a stored database that will insert all items of the itemcodigo into the user items.

I tried some ways but it gave error in converting from varchar to int (I believe it is due to select I did)

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."

Answer 1

Bruno,

The problem is due to the fact that Voce is passing a string (varchar) to a parameter that expects a number (int)

Your "inserts" parameter has two parameters: @idusuario int, and @idproduto int

The Procedure Voce call is passing 594 to the user ID and a "select itemcodigo from xx.dbo.items" command that will actually fail because @idproduct 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 insert items 594

Considering that the table has the idusuario field.