SP para Delete, Insert e UpDate

[Lab Design 0]

CREATE PROCEDURE osItens
	@osId	int,
	@op		int,
	@TRAcol	int,
	@numCat int,
	@tabela	nVarchar(50),
	@ret	int output

AS
	DECLARE @numReg		int	
	DECLARE @descricao	nVarchar(50)
	DECLARE @preco		decimal(12,2)
	if @op=-1  -- excluir um registro
		begin
			select @ret=0
			begin transaction
				delete from ordemServico_itens
				where numCat=@numCat AND osId=@osId
		  end

	else
		begin
			-- inserindo ou atualizando
			if exists(SELECT numCat FROM ordemServico_itens
				WHERE numCat=@numCat AND osId=@osId)
				begin
					-- se ja existe na tabela, retorna sem fazer nada
					select @ret=0
					return (@ret)
				end
			
			if numCat < 99900
				begin
					-- tanto faz para garantia, cortesia ou reparo
					-- busca somente a tabela de peças.
					SELECT @descricao=descricao FROM @tabela WHERE numCat=@numCat
					begin transaction
						INSERT INTO ordemServico_itens(osId, numCat, descricao, quantidade, preco, total) 
							VALUES(@osId, @numCat, @descricao,1,0,0)
				end

			else
				begin		
					SELECT @descricao=descricao,@preco='L'+@TRAcol FROM assistecTRA WHERE numCat=@numCat						
					begin transaction
						INSERT INTO ordemServico_itens(osId, numCat, descricao, quantidade, preco, total) 
							VALUES(@osId, @numCat, @descricao,1,@preco*4,@preco*4)
				end
		end

if @@error <> 0
	begin
		rollback transaction
		return(1)
	end
else
	begin
		UPDATE ordemServico SET maoDeObra=(SELECT SUM(total) FROM 
			ordemServico_itens where osId=@osId AND numCat > 99900),
			subTotal=(SELECT SUM(total) FROM 
			ordemServico_itens WHERE osId=@osId AND numCat < 99900),
			totalGeral=subTotal + maoDeObra
		WHERE id=@osId
		commit transaction
		return(0)
	end

 

Msg 137, Level 15, State 2, Procedure osItens, Line 36

Must declare the variable '@tabela'.

 

-- a var tabela é passada no parametro

 

Não consigo gravar a SP

[eriva_br 7]

dessa forma não vai ser possível, tu só vai conseguir colocando todo o comando em string e fazendo um EXEC

 

exemplo:

use pubs;

declare @table varchar(100)

set @table = 'titles'
exec ('select top 5 * from '+ @table)

set @table = 'authors'
exec ('select top 5 * from '+ @table)

lembrando que esta prática não é recomendada em termos de performance

 

t+

[Lab Design 0]

Valeu eriva_br, mas eu não tenho outra forma senão passando por parametro ou fazendo um select na SP, pois eu tenho uma tabela de OS com header, uma tabela de peças e serviços, uma tabela de equipamentos, sendo que cada equipt. aponta para uma tabela de peças. Eu até pensei em montar uma única tabela de peças mas seria uma loucura ja que tenho mais de 200 equipamentos e algumas como parafusos por exemplo são comuns a maioria dos equipts. Ainda to estudando a viabilidade de deixar em script no linux (já esta funcionando perfeitamente porém não tenho portabilidade uma que futuro eu poderia reescrever todo o sistema para uma plataforma windows ou outra diferente do linux que possa surgir no futuro sei la.

 

So pra documentar a necessidade:

Tabela OS:

[id]

[idProd] aponta para a tabela de equitp.

[subTotal]

[maodeobra]

[totalgeral]

 

Tabela ProdutosAssistec

[id] vinculado a tabela OS no campo idProd

[tabelaPecas] informa o nome da tabela de peças

[TRA] aponta a coluna na tabela de Reembolso de Atendimento (somente para os casos de garantia)

 

Tabela OSItens

[id]

[descricao]

[qtde]

[preco] - 0 para peças no caso garantia ou Valor referencial para serviços de garantia ou valor unitário para reparos

[total]

 

existem ainda outras tabelas de apois mas que nao fazem parte da SP

 

A tabela Os aponta para a tabela equipt que informa a tabela de peças.

Agora quem determina tudo é o numCat que sendo < 99900 se refere a uma peça e portanto deve ser buscado na tabela indicada pela tabela equipts, já o numCat sendo > 99900 se refere a serviços, neste caso a tabela é única porém cada equipt. aponta para uma coluna que informa o valor referencial para aquele serviço.

 

Aproveitando o post eu tenho uma dúvida que agora veio à tona:

 

Qual seria a vantagem real de se usar SP pra web, fora a economia de banda no acesso ao servidor DB. Isso não poderia acarretar numa sobrecarga no servidor de DB caso o site comece a ter muito acesso?

 

- Porque no caso dos scripts o serviço fica mais ou menos meio a meio, voce so passa para o servidor DB as queries já formatadas e somente executa e te devolve os resultados, no caso das SP você transfere pra ele todo o processo de contrução das queries, joins e etc...

 

A minha dúvida vem no sentido de que usando SP, liberamos o servidor web mas a meu ver sobrecarregamos o servidor DB.

 

Até que ponto isso é viável?

 

 

Eu programo pra web a muitos anos, mas sempre dentro da plataforma linux e portanto usando o mysql que não tem tantos recursos quanto o sql server ou oracle ou etc... mas é um db enxuto e muito rápido entao nunca vi necessidade antes de pensar em SP para web, já que os meus sites não sao grandes e portanto não demandam tanto fluxo do servidor mysql, mas agora estou trabalhando em projeto misto ou seja, toda a parte de web eu programo em php mas o DB é sql server, pois na intranet dos clientes está rodando uma aplicação vb.net e tivemos dificuldades em conexões com o mysql, conexão inconsistente não sei porque..

[MisterPe 0]

Como bem colocou o eriva, o único modo de trabalhar com queries baseadas em colunas no BD, é através de queries dinâmicas + comando "exec".

 

No seu caso, ficaria mais ou menos assim...

 

 

CREATE PROCEDURE osItens
	@osId	int,
	@op		int,
	@TRAcol	int,
	@numCat int,
	@tabela	nVarchar(50),
	@ret	int output	

AS
	DECLARE @numReg	   int	
	DECLARE @descricao	nVarchar(50)
	DECLARE @preco		decimal(12,2)
	DECLARE @Comando	  varchar (1500)	   -- variavel para montar o comando
	:
	:	
			if numCat < 99900
				begin
					-- tanto faz para garantia, cortesia ou reparo
					-- busca somente a tabela de peças.
					
					-- monta string com comando para obter a descricao em @tabela
					   set @Comando = 'SELECT ' + rtrim(@descricao) + ' = descricao FROM ' + rtrim(@tabela) + ' WHERE numCat = ' + rtrim(str(@numCat))
					   exec(@Comando)
					:
					:

Quanto a seu receio em relação ao uso das SPs, fique tranquilo !

 

Lembre-se do seguinte : o que determina a carga de acesso ao BD não é o tipo de estrutura (query ou sp), mas sim a demanda de uso do próprio site !!! Se você tiver muitos acessos, o fato de ser uma SP vai te ajudar... pois se fossem queries simples, os acessos seriam os mesmos e o tráfego seria maior.

 

A utilização de SPs no ambiente WEB é altamente recomendada pois, além de melhorar a utilização da banda (uma vez que somente os parâmetros serão passados pela aplicação e não a query completa), otimiza a manutenção e atualização do código SQL.

 

Ok ?!?

[Lab Design 0]

Valeu MisterPe, eu consegui resolver de outra forma nao muito apresentável mas funcionou.

ALTER PROCEDURE [dbo].[insertOsItens]
	@osId	int,	-- id da os (PK)
	@idProd	int,	-- id do equip.
	@op		int,	-- tipo de operação
	@numCat int,	-- id da peça ou serviço
	@ret	int output
AS
	DECLARE @preco		decimal(12,2)
	DECLARE @descricao	nVarchar(50)
	DECLARE @TRA		int
	if @op=-1  -- excluir um registro
		begin
			select @ret=0
			begin transaction
				DELETE FROM ordemServico_itens WHERE numCat=@numCat AND osId=@osId
		  end
	else
		if not exists(SELECT id FROM ordemServico_itens WHERE numCat=@numCat AND osId=@osId)
			if (@numCat < 99900)
				begin
					SELECT @descricao=descricao FROM produtos WHERE numCat=@numCat					
					begin transaction
						INSERT INTO ordemServico_itens(osId, numCat, descricao, quantidade,preco,total) 
							VALUES(@osId, @numCat, upper(@descricao),1, 0,0)
				end
			else
				begin
					SELECT @descricao=descricao FROM assistecTRA WHERE numCat=@numCat
					--print(@descricao)
					SELECT @TRA=coluna FROM produtosAssistec WHERE id=@idProd
					--print(@descricao)
					DECLARE @tmp nVarchar(10)
					if (@TRA=1) SELECT @tmp=L1 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=2) SELECT @tmp=L2 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=3) SELECT @tmp=L3 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=4) SELECT @tmp=L4 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=5) SELECT @tmp=L5 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=6) SELECT @tmp=L6 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=7) SELECT @tmp=L7 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=8) SELECT @tmp=L8 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=9) SELECT @tmp=L9 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=10) SELECT @tmp=L10 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=11) SELECT @tmp=L11 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=12) SELECT @tmp=L12 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=13) SELECT @tmp=L13 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=14) SELECT @tmp=L14 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=15) SELECT @tmp=L15 FROM assistecTRA WHERE numCat=@numCat
					if (@TRA=16) SELECT @tmp=L16 FROM assistecTRA WHERE numCat=@numCat
					SET @preco=left(@tmp, 1)*4 -- por tem um numcat 99951 que o valor da tabela  de 1-2 a 1-10
					begin transaction
						INSERT INTO ordemServico_itens(osId, numCat, descricao, quantidade,preco,total)
							VALUES(@osId, @numCat, upper(@descricao),1,@preco,@preco)
				end
		

if @@error <> 0
	begin
		rollback transaction
		return(1)
	end
else
	begin
		DECLARE @subTotal	decimal(12,2)
		DECLARE @MO			decimal(12,2)
		SELECT @subTotal=SUM(total) FROM ordemServico_itens where osId=@osId AND numCat < 99900
		SELECT @MO=SUM(total) FROM ordemServico_itens where osId=@osId AND numCat > 99900
		UPDATE ordemServico SET subTotal=@subTotal,maoDeObra=@MO,totalGeral=subTotal + maoDeObra WHERE id=@osId
		commit transaction
		return(0)
	end

O que me incomodou foi usar 16 ifs se tiver uma forma de contornar isso numa unica linha acredito que seria a solução, por ora ta funcionando dessa forma.

 

Vou tentar montar o comando pra substituir esses 16 ifs se funcionar ja valeu pelo trabalho