none
ajuda para criar insert, update, delete RRS feed

  • Pergunta

  • Caros, Bom dia.

     

    Quando estava na faculdade eu fiz um projeto final que na hora que eu fazia insert, update, delete eu chamava um comando genérico.

    Por exemplo em um INSERT eu lembro que eu passava o nome da Tabela, Campos e Valores. Porém não acho meu projeto de faculdade e quero refazer o que fiz naquela época.

    Preciso de uma orientação de como eu faço, aí vai a minha idéia:

    Hoje eu passo os valores via ASP.NET assim:

     

     

    public void ApontColarIns(ALMApontColarDTO almacDTO){

     

    ConnectionSQL connSQL = new ConnectionSQL("S3", "DBS3");
    ExecutionSQL execSQL = new ExecutionSQL("ALMApontColarIns", CommandType.StoredProcedure, connSQL.SqlConnection);
    execSQL.AddParameter(
    "@FK_E10_UKEY", almacDTO.ChaveNota);
    execSQL.AddParameter(
    "@FK_D04_UKEY", almacDTO.ChaveItem);
    execSQL.AddParameter(
    "@ALM001NumLote", almacDTO.ALM001NumLote);
    execSQL.AddParameter(
    "@ALM001Laminas", almacDTO.ALM001Laminas);
    execSQL.AddParameter(
    "@ALM001Responsavel", almacDTO.ALM001Responsavel);
    execSQL.AddParameter(
    "@ALM001Altura", almacDTO.ALM001Altura);
    execSQL.AddParameter(
    "@ALM001Largura", almacDTO.ALM001Largura);
    try
    {
          connSQL.OpenConnection();
          execSQL.ExecuteNonQuery();
          return;
    }
    catch (Exception error) {
           connSQL.CloseConnection();
          
    throw new Exception(error.ToString());
    }

    }

     

    Como pode notar no comando acima, eu tenho que gerar uma STORED PROCEDURE de INSERT para cada Insert que vou fazer.

    Então pensei em fazer algo assim:

     

    CREATE PROCEDURE INSERTPADRAO
    @BANCO
    varchar(20),
    @TABELA varchar(20)
    @CAMPOS varchar(4000)
    @VALORES varchar(max)

    AS
    BEGIN
         
          
    DECLARE @StringSQL varchar(max)

     

     

           SET @StringSQL = 'INSERT INTO '+@BANCO+'.dbo.'+@TABELA+' ('+ @CAMPOS + ') VALUES('+ @VALORES +')'

     

           exec(@StringSQL)

     

     

    END

     

    Porém, não sei se é a melhor maneira, porque se eu imagino que uma variavel varchar(max) pode estourar, em algum momento eu vou precisar fazer conversão de dados ou passar o campo como data, int ..

    Alguém tem alguma idéia?

    Obrigado desde já.

    Att

    New Civic

    quarta-feira, 24 de novembro de 2010 11:05

Respostas

  • Consegui fazer em STORED PROCEDURE.

    Claro que falta os outros tipos de dados e tudo mais, e pode ser que dependendo do que tiver sendo inserido ou o número de inserções pode ser que fique lento o sistema. Mas com certeza ter uma stored procedure padrão que auxilie o trabalho de um desenvolvedor é uma mão na roda..

    SEGUE o código que ficou:

    declare @xml				xml,
    		@iddoc				int,
    		@banconome			varchar(50),
    		@bancoschema		varchar(50),
    		@bancotabela		varchar(50),
    		@bancocomando		varchar(50),
    		@bancocompleto		varchar(255),
    		@bancocampos		varchar(255),
    		@SQLString			varchar(MAX),
    		
    		@cCamposNome		varchar(50),
    		@cCamposTipo		varchar(50),
    		@cCamposTamanho		varchar(10),
    		@cCamposString		nvarchar(MAX),
    		@cCamposParametros	nvarchar(MAX),
    		@cCamposTexto		varchar(MAX)
    
    
    		IF object_id('tempdb.dbo.#BDDinamico') is not null
    		BEGIN
    			DROP TABLE #BDDinamico
    		END	
    
    		CREATE TABLE #BDDinamico (BDDinamicoID INT)
    		INSERT INTO #BDDinamico (BDDinamicoID) VALUES (1)
    
    
    		select @xml='<?xml version="1.0" encoding="iso-8859-1" ?> 
    		<Comando>
    			<configuracao>
    				<banconome>S3</banconome>
    				<bancoschema>dbo</bancoschema>
    				<bancotabela>teste</bancotabela>
    				<bancocomando>INSERT INTO</bancocomando>
    			</configuracao>
    	
    			<dados>
    				<campo id="texto" value="JOSÉ DA SILVA"/>
    				<campo id="caracter" value="a"/>
    				<campo id="textolongo" value="aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10" />
    			</dados>
    		</Comando>'
    
    
    		/* prepara o xml para ser lido */
    		EXEC sp_xml_preparedocument @iddoc OUTPUT, @xml
    
    
    		/* pega as informações de banco¨*/
    		SELECT	@banconome=@xml.value('(/Comando/configuracao/banconome)[1]', 'varchar(50)'),
    				@bancoschema=@xml.value('(/Comando/configuracao/bancoschema)[1]', 'varchar(50)'),
    				@bancotabela=@xml.value('(/Comando/configuracao/bancotabela)[1]', 'varchar(50)'),
    				@bancocomando=@xml.value('(/Comando/configuracao/bancocomando)[1]', 'varchar(50)')
    		
    		SET @bancocompleto = @banconome + '.' + @bancoschema + '.' + @bancotabela
    		SET @bancocampos = ''
    
    
    		
    		/* pega os metadados e popula tabela temporaria */		
    		
    		DECLARE cCampos CURSOR
    		FAST_FORWARD
    		FOR	
    			
    			select 
    				c.[name],
    				t.[name],
    				c.[length] 
    			from 
    				sysobjects o 
    				inner join syscolumns c on o.id = c.id
    				inner join systypes t on c.xtype = t.xtype
    				left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@bancotabela,N'column',null) e on e.objname COLLATE Latin1_General_CI_AI =c.name COLLATE Latin1_General_CI_AI 
    			where 
    				o.name=@bancotabela
    			order by c.colorder
    
    		Open cCampos
    		FETCH NEXT FROM cCampos INTO @cCamposNome,@cCamposTipo,@cCamposTamanho
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			
    			
    			--existe o nó?
    			IF (SELECT @xml.exist('//dados//campo[@id=sql:variable("@cCamposNome")]')) = 1 
    			BEGIN
    			
    				SET @bancocampos = @bancocampos + @cCamposNome + ','
    			
    			
    				IF @cCamposTipo = 'varchar' or @cCamposTipo = 'char' or @cCamposTipo = 'ntext'
    				BEGIN
    					
    					IF @cCamposTipo = 'ntext' 
    					BEGIN
    						set @cCamposString= 'ALTER TABLE #BDDinamico ADD '+ @cCamposNome + ' ' + @cCamposTipo + ' NULL'
    					END
    					ELSE
    					BEGIN
    						set @cCamposString= 'ALTER TABLE #BDDinamico ADD '+ @cCamposNome + ' ' + @cCamposTipo+'('+@cCamposTamanho+') NULL'
    					END
    					exec(@cCamposString)
    
    
    
    					SELECT @cCamposTexto = item.value('@value', 'VARCHAR(MAX)') FROM @xml.nodes('//dados//campo[@id=sql:variable("@cCamposNome")]') t (item)
    					SET @cCamposString= 'UPDATE #BDDinamico SET '+ @cCamposNome+'=@XcCamposTexto'
    					set @cCamposParametros = '@XcCamposTexto VARCHAR(MAX)'
    					EXEC sp_executesql @cCamposString, @cCamposParametros, @cCamposTexto
    				
    				
    				
    				END
    
    				
    			
    			END
    	
    		
    		FETCH NEXT FROM cCampos INTO @cCamposNome,@cCamposTipo,@cCamposTamanho
    		END
    		CLOSE cCampos
    		DEALLOCATE cCampos
    
    	
    	
    	
    		--remove ID que não serve para nada
    		ALTER TABLE #BDDinamico
    		DROP COLUMN BDDinamicoID
    		
    	
    		--insere comando	
    		
    		SET @bancocampos = LEFT(@bancocampos,DATALENGTH(@bancocampos)-1)
    		SET @SQLString = 'INSERT INTO '+@bancocompleto+ ' ('+ @bancocampos + ') (SELECT * FROM #BDDinamico)'
    	 exec(@SQLString) 
    
    OBRIGADO A TODOS QUE ME AJUDARAM NESTA QUESTÃO....
    • Marcado como Resposta New Civic sexta-feira, 26 de novembro de 2010 12:51
    sexta-feira, 26 de novembro de 2010 12:51

Todas as Respostas

  • CAROS,

    Consegui desenvolver uma função para insert, porém queria saber a opinião de vocês.

     

    ALTER PROCEDURE BDInsert
    	@BANCO		varchar(20),
    	@TABELA		varchar(20),
    	@CAMPOS		varchar(4000),
    	@VALORES	varchar(max),
    	@SEPARADOR	varchar(4)
    AS
    BEGIN
    
    
    
    		/**********************************************
    		Procedure: BDInsert
    		Descrição:
    		Store procedure para realizar inserts padrões
    	    
    		Criação: 24/11/2010
    		Criado por: NEW CIVIC
    		
    		Atualização: 24/11/2009
    		Atualizado por: NEW CIVIC
    		
    		***********************************************/
    
    		DECLARE @Local		varchar(255)
    		DECLARE @SQLString	varchar(max)
    	
    		DECLARE 
    			@SPLITContador			INT,
    			@SPLITOcorrencias		INT,
    			@SPLITStringTemporaria1	VARCHAR(MAX),
    			@SPLITStringTemporaria2	VARCHAR(MAX),
    			@SPLITCampoNome			Varchar(50),	
    			@SPLITCampoTipo			varchar(50),
    			@SPLITCampoTamanho		int
    			
    		SET @SPLITContador = 0
    	
    		/* coloca virgula no final */
    		IF SUBSTRING(@CAMPOS,LEN(@CAMPOS),1) <> @SEPARADOR
    		BEGIN
    			SET @CAMPOS = @CAMPOS + @SEPARADOR
    		END
    	
    		
    		IF SUBSTRING(@VALORES,LEN(@VALORES),1) <> @SEPARADOR
    		BEGIN
    			SET @VALORES = @VALORES + @SEPARADOR
    		END
    	
    	
    	
    		SET @Local = @BANCO + '.dbo.'+@TABELA
    			
    		SET @SQLString = 'INSERT INTO '
    		SET @SQLString = @SQLString + @Local
    		
    		
    		--coloca os campos, tirando a última virgula.
    		SET @SQLString = @SQLString + ' ('+ LEFT( @CAMPOS, LEN(@CAMPOS)-1) + ') '
    	
    		SET @SQLString = @SQLString + ' VALUES ('
    		
    		
    
    
    		SET @SPLITOcorrencias = (DATALENGTH(REPLACE(@CAMPOS,@SEPARADOR,@SEPARADOR+'#')) - DATALENGTH(@CAMPOS))/ DATALENGTH(@SEPARADOR)
    		SET @SPLITStringTemporaria1	= @CAMPOS
    		SET @SPLITStringTemporaria2	= @VALORES
    
    
    		WHILE @SPLITContador <= @SPLITOcorrencias 
    		BEGIN
    		
    			SET @SPLITContador = @SPLITContador + 1
    			
    			SET @SPLITCampoNome = SUBSTRING(@SPLITStringTemporaria1,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria1)-1)
    			
    			/* descobre o tipo de campo e tamanho */
    			select 
    				@SPLITCampoTipo=t.[name]
    				-- caso precise,@SPLITCampoTamanho=c.[length] 
    			from 
    				sysobjects o 
    				inner join syscolumns c on o.id = c.id
    				inner join systypes t on c.xtype = t.xtype
    				left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@TABELA,N'column',null) e on e.objname COLLATE Latin1_General_CI_AI =c.name COLLATE Latin1_General_CI_AI 
    			where 
    				o.name =@TABELA
    				AND c.[name]=RTRIM(LTRIM(@SPLITCampoNome))
    			order by c.colorder
    			
    			IF 
    				@SPLITCampoTipo = 'text' or 
    				@SPLITCampoTipo = 'varchar' or
    				@SPLITCampoTipo = 'char' or
    				@SPLITCampoTipo = 'nvarchar' or
    				@SPLITCampoTipo = 'nchar'
    			BEGIN
    				SET @SQLString = @SQLString + ''''+ SUBSTRING(@SPLITStringTemporaria2,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)-1) +''','
    			END
    			ELSE
    			BEGIN
    			
    				IF 
    					@SPLITCampoTipo = 'int' or 
    					@SPLITCampoTipo = 'real' or
    					@SPLITCampoTipo = 'money' or
    					@SPLITCampoTipo = 'float' or
    					@SPLITCampoTipo = 'bit' or 
    					@SPLITCampoTipo = 'decimal' or 
    					@SPLITCampoTipo = 'smallmoney' or 
    					@SPLITCampoTipo = 'bigint' or 
    					@SPLITCampoTipo = 'smallmoney'
    				BEGIN			
    					SET @SQLString = @SQLString + SUBSTRING(@SPLITStringTemporaria2,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)-1) +','
    				END
    				ELSE
    				BEGIN
    				
    					IF 
    						@SPLITCampoTipo = 'date' or 
    						@SPLITCampoTipo = 'time' or
    						@SPLITCampoTipo = 'datetime' 
    					BEGIN
    						SET @SQLString = @SQLString + ' CAST( ('+ SUBSTRING(@SPLITStringTemporaria2,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)-1) +') as datetime),'
    					END	
    				
    				
    				END
    			
    			END
    			
    			
    			
    			--tira a última virgula
    			SET @SQLString = LEFT( @SQLString,LEN(@SQLString)-1)
    			
    			
    			SET @SQLString = @SQLString + ')'						
    								
    		
    			
    			--atualiza stringtemporaria1
    			SET @SPLITStringTemporaria1 = SUBSTRING(@SPLITStringTemporaria1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria1)+1,80000)
    			IF DATALENGTH(@SPLITStringTemporaria1) = 0
    				BREAK
    
    			--atualiza stringtemporaria2
    			SET @SPLITStringTemporaria2 = SUBSTRING(@SPLITStringTemporaria2,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)+1,80000)
    			IF DATALENGTH(@SPLITStringTemporaria2) = 0
    				BREAK
    
    
    				
    		END
    		
    			
    		print @SQLString
    		
    		exec(@SQLString)
    
    END
    	

    • Editado New Civic quarta-feira, 24 de novembro de 2010 16:40 arrumando código
    quarta-feira, 24 de novembro de 2010 16:38
  • New Civic,

    Mas qual é a sua dúvida?

    Realmente você tem a necessidade de criar uma Stored Procedure para Insert, Update e Delete?

    Porque não utilizar os próprios recursos oferecidos pelo SQL Server?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    quinta-feira, 25 de novembro de 2010 12:06
  • Olá Junior, Bom dia.

    Obrigado por interagir comigo.

    A minha necessidade é facilitar a programação. Como o Insert, Update e Delete tem sempre uma estrutura padrão, pensei em fazer um comando padrão, para facilitar a tarefa de programação. Neste comando padrão, passaria a tabela, o banco, os campos que quero atualizar e os valores.

    Hoje na maneira que programo, tenho que criar uma stored procedure para inserir, uma para alterar, uma para deletar para cada tela que desenvolvo.

    Já estou com mais de 100 stored procedures, que a unica diferença que existem nelas é os campos, tabela e valores o padrão é o mesmo..

    Veja um exemplo de uma procedure minha:

     

    USE [S3]
    GO
    /****** Object: StoredProcedure [dbo].[RHCargosIns]  Script Date: 11/25/2010 11:08:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[RHCargosIns]
    	@RH002Nome		varchar(100),
    	@RH002Ativo		char(1)
    AS
    BEGIN
    
    	/**********************************************
      Procedure: RHCargosIns
      Descrição:
      Store Procedure para inserir um novo grupo
        
      Criação: 02/06/2009
      Criado por: New Civic
      Atualização: 02/06/2009
      Atualizado por: New Civic
      
      ***********************************************/
    
    	INSERT INTO RH002Cargos(RH002Nome,RH002Ativo) VALUES(@RH002Nome,@RH002Ativo)
    
    END
    

     

    Agora veja outra procedure de outra tela:

    USE [S3]
    GO
    /****** Object: StoredProcedure [dbo].[RHEmpresasIns]  Script Date: 11/25/2010 11:08:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[RHEmpresasIns]
    	@RH001FKEntidade			int,
    	@RH008Contato				varchar(50),
    	@RH008ContatoEmail			varchar(50),
    	@RH008ContatoDDD			char(2),
    	@RH008ContatoTelefone		varchar(14),
    	@RH008ContatoTelefoneCompl	varchar(10),
    	@RH008Instrutor				char(1)
    AS
    BEGIN
    
    	/**********************************************
      Procedure: RHEmpresasIns
      Descrição:
      Store Procedure para inserir uma nova empresa
        
      Criação: 03/07/2009
      Criado por: New Civic
      Atualização: 03/07/2009
      Atualizado por: New Civic
      
      ***********************************************/
    		
    	INSERT INTO RH008Empresas(RH001FKEntidade,RH008Contato,RH008ContatoEmail,RH008ContatoDDD,RH008ContatoTelefone,RH008ContatoTelefoneCompl,RH008Instrutor) VALUES(@RH001FKEntidade,@RH008Contato,@RH008ContatoEmail,@RH008ContatoDDD,@RH008ContatoTelefone,@RH008ContatoTelefoneCompl,@RH008Instrutor)
    
    END
    

     SE for ver a única diferença entre elas são os campos, tabelas e valores.

    Eu cheguei a fazer algumas modificações no código que criei, veja:

     

    ALTER PROCEDURE BDInsert
    	@BANCO		varchar(20),
    	@TABELA		varchar(20),
    	@CAMPOS		VARCHAR(MAX),
    	@VALORES	NVARCHAR(MAX),
    	@SEPARADOR	varchar(4)
    AS
    BEGIN
    
    
    		/**********************************************
    		Procedure: BDInsert
    		Descrição:
    		Store procedure para realizar inserts padrões
    	    
    		Criação: 24/11/2010
    		Criado por: New Civic
    		
    		Atualização: 24/11/2009
    		Atualizado por: New Civic
    		
    		***********************************************/
    
    		DECLARE @Local		varchar(255)
    		DECLARE @SQLString	NVARCHAR(MAX)
    	
    		DECLARE 
    			@SPLITContador			INT,
    			@SPLITOcorrencias		INT,
    			@SPLITStringTemporaria1	NVARCHAR(MAX),
    			@SPLITStringTemporaria2	NVARCHAR(MAX),
    			@SPLITCampoNome			Varchar(50),	
    			@SPLITCampoTipo			varchar(50),
    			@SPLITCampoTamanho		int
    			
    		SET @SPLITContador = 0
    	
    		/* coloca separador no final */
    		IF SUBSTRING(@CAMPOS,LEN(@CAMPOS),1) <> ','
    		BEGIN
    			SET @CAMPOS = @CAMPOS + ','
    		END
    
    		IF SUBSTRING(@VALORES,DATALENGTH(@VALORES),LEN(@SEPARADOR)) <> @SEPARADOR
    		BEGIN
    			SET @VALORES = @VALORES + @SEPARADOR
    		END
    
    		print @VALORES
    
    		SET @Local = @BANCO + '.dbo.'+@TABELA
    			
    		SET @SQLString = 'INSERT INTO '
    		SET @SQLString = @SQLString + @Local
    		
    		--coloca os campos, tirando a última virgula.
    		SET @SQLString = @SQLString + ' ('+ LEFT( @CAMPOS, LEN(@CAMPOS)-1) + ') '
    	
    		SET @SQLString = @SQLString + ' VALUES ('
    		
    		SET @SPLITOcorrencias = (DATALENGTH(REPLACE(@CAMPOS,@SEPARADOR,@SEPARADOR+'#')) - DATALENGTH(@CAMPOS))/ DATALENGTH(@SEPARADOR)
    		SET @SPLITStringTemporaria1	= @CAMPOS
    		SET @SPLITStringTemporaria2	= @VALORES
    
    		
    		WHILE @SPLITContador <= @SPLITOcorrencias 
    		BEGIN
    		
    			SET @SPLITContador = @SPLITContador + 1
    			
    			SET @SPLITCampoNome = SUBSTRING(@SPLITStringTemporaria1,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria1)-1)
    			
    			/* descobre o tipo de campo e tamanho */
    			select 
    				@SPLITCampoTipo=t.[name]
    				-- caso precise,@SPLITCampoTamanho=c.[length] 
    			from 
    				sysobjects o 
    				inner join syscolumns c on o.id = c.id
    				inner join systypes t on c.xtype = t.xtype
    				left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@TABELA,N'column',null) e on e.objname COLLATE Latin1_General_CI_AI =c.name COLLATE Latin1_General_CI_AI 
    			where 
    				o.name =@TABELA
    				AND c.[name]=RTRIM(LTRIM(@SPLITCampoNome))
    			order by c.colorder
    			
    			IF 
    				@SPLITCampoTipo = 'text' or 
    				@SPLITCampoTipo = 'varchar' or
    				@SPLITCampoTipo = 'char' or
    				@SPLITCampoTipo = 'nvarchar' or
    				@SPLITCampoTipo = 'nchar'
    			BEGIN
    				SET @SQLString = @SQLString + ''''+ SUBSTRING(@SPLITStringTemporaria2,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)-1) +''','
    			END
    			ELSE
    			BEGIN
    			
    				IF 
    					@SPLITCampoTipo = 'int' or 
    					@SPLITCampoTipo = 'real' or
    					@SPLITCampoTipo = 'money' or
    					@SPLITCampoTipo = 'float' or
    					@SPLITCampoTipo = 'bit' or 
    					@SPLITCampoTipo = 'decimal' or 
    					@SPLITCampoTipo = 'smallmoney' or 
    					@SPLITCampoTipo = 'bigint' or 
    					@SPLITCampoTipo = 'smallmoney'
    				BEGIN			
    					SET @SQLString = @SQLString + SUBSTRING(@SPLITStringTemporaria2,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)-1) +','
    				END
    				ELSE
    				BEGIN
    				
    					IF 
    						@SPLITCampoTipo = 'date' or 
    						@SPLITCampoTipo = 'time' or
    						@SPLITCampoTipo = 'datetime' 
    					BEGIN
    						SET @SQLString = @SQLString + ' CAST( ('+ SUBSTRING(@SPLITStringTemporaria2,1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)-1) +') as datetime),'
    					END	
    				
    				
    				END
    			
    			END
    			
    			
    		
    			
    
    			
    			--atualiza stringtemporaria1
    			SET @SPLITStringTemporaria1 = SUBSTRING(@SPLITStringTemporaria1,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria1)+1,800000)
    			IF DATALENGTH(@SPLITStringTemporaria1) = 0
    				BREAK
    
    			--atualiza stringtemporaria2
    			SET @SPLITStringTemporaria2 = SUBSTRING(@SPLITStringTemporaria2,CHARINDEX(@SEPARADOR,@SPLITStringTemporaria2)+1,800000)
    			IF DATALENGTH(@SPLITStringTemporaria2) = 0
    				BREAK
    
    
    				
    		END
    		
    		
    		
    			
    		--tira a última virgula
    		SET @SQLString = LEFT( @SQLString,LEN(@SQLString)-1)			
    		SET @SQLString = @SQLString + ')'						
    		
    		print @SQLString
    
    		exec(@SQLString)
    
    END
    

    Mas estou tendo problemas para inserir campos grandes como ntext, blob, image.

    Agora se você tem outra sugestão para fazer o que eu estou querendo fazer, estou aberto a aprender.

    Muito obrigado

    New Civic

     

     

    quinta-feira, 25 de novembro de 2010 13:10
  • New Civic,

    Na verdade eu não tenho uma sugestão, como o seu ambiente e necessidade são bem específicas. Se entendi bem você deseja criar uma espécie de stored procedure padrão para estes procedimentos de manipulação de dados.

    Qual é a linguagem de progração e componente de acesso ao banco que você esta utilizando?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
    quinta-feira, 25 de novembro de 2010 13:14
  • A idéia era esta mesmo...

    Ou algo que facilite a programação.

     

    Eu na programação estou usando ASP.NET com C#

     

    Abaixo um exemplo da chamada da procedure:

     public void CargosIns(RHCargosDTO rhcaDTO)
      {
       ConnectionSQL connSQL = new ConnectionSQL("S3", "DBS3");
       ExecutionSQL execSQL = new ExecutionSQL("RHCargosIns", CommandType.StoredProcedure, connSQL.SqlConnection);
       execSQL.AddParameter("@RH002Nome", rhcaDTO.RH002Nome);
       execSQL.AddParameter("@RH002Ativo", rhcaDTO.RH002Ativo);
       try
       {
        connSQL.OpenConnection();
        execSQL.ExecuteNonQuery();
        return;
       }
       catch(Exception error)
       {
        connSQL.CloseConnection();
        throw new Exception(error.ToString());
       }
      }

    agora o exemplo para outro insert identico:

     public void EntidadesIns(RHEntidadesDTO rhentDTO)
      {
       ConnectionSQL connSQL = new ConnectionSQL("S3", "DBS3");
       ExecutionSQL execSQL = new ExecutionSQL("RHEntidadesIns", CommandType.StoredProcedure, connSQL.SqlConnection);
       execSQL.AddParameter("@RH001Nome", rhentDTO.RH001Nome);
       execSQL.AddParameter("@RH001Ativo", rhentDTO.RH001Ativo);
       try
       {
        connSQL.OpenConnection();
        execSQL.ExecuteNonQuery();
        return;
       }
       catch(Exception error)
       {
        connSQL.CloseConnection();
        throw new Exception(error.ToString());
       }
      }
    

    Quando disse usar as ferramentas do SQL o que quiz dizer exatamente?

    quinta-feira, 25 de novembro de 2010 13:51
  • Bem,

    Pesquisando na internet, achei que talvez o melhor caminho, quanto a questão de tamanho, tipo de dados e limitações seria passar para a procedure os valores e campos como um XML.

    Vou trabalhar em uma procedure que receba algo assim:

    <INSERT>
    <Campo1>valor1</CAMPO1>
    <Campo2>valor2</campo2>
    <Campo3>valor3<campo3>
    </INSERT>

    não sei se é a melhor maneira, mas parece que se der certo vai ser uma mão na roda..

    se alguém tiver algo mais ou menos feito avisa ai..

     

    quinta-feira, 25 de novembro de 2010 16:28
  • Então pessoal,

    Estou trabalhando em uma versão, porém acabei esbarrando no mesmo problema "CAMPOS NTEXT"

    Abaixo o que eu fiz até agora:

    declare @xml xml,
    		@iddoc int,
    		@banconome			varchar(50),
    		@bancoschema		varchar(50),
    		@bancotabela		varchar(50),
    		@bancocomando		varchar(50),
    		
    		@cCamposNome		varchar(50),
    		@cCamposTipo		varchar(50),
    		@cCamposTamanho		varchar(10),
    		@cCamposString		varchar(MAX)
    
    
    		IF object_id('tempdb.dbo.#BDDinamico') is not null
    		BEGIN
    			DROP TABLE #BDDinamico
    		END	
    
    		CREATE TABLE #BDDinamico (BDDinamicoID INT)
    		INSERT INTO #BDDinamico (BDDinamicoID) VALUES (1)
    
    
    		select @xml='<?xml version="1.0" encoding="iso-8859-1" ?> 
    		<Comando>
    			<configuracao>
    				<banconome>S3</banconome>
    				<bancoschema>dbo</bancoschema>
    				<bancotabela>teste</bancotabela>
    				<bancocomando>INSERT INTO</bancocomando>
    			</configuracao>
    	
    			<dados>
    				<campo id="texto" value="TESTE" />
    			</dados>
    		</Comando>'
    
    
    
    
    
    
    		/* prepara o xml para ser lido */
    
    		EXEC sp_xml_preparedocument @iddoc OUTPUT, @xml
    
    
    		/* pega as informações de banco¨*/
    		SELECT	@banconome=@xml.value('(/Comando/configuracao/banconome)[1]', 'varchar(50)'),
    				@bancoschema=@xml.value('(/Comando/configuracao/bancoschema)[1]', 'varchar(50)'),
    				@bancotabela=@xml.value('(/Comando/configuracao/bancotabela)[1]', 'varchar(50)'),
    				@bancocomando=@xml.value('(/Comando/configuracao/bancocomando)[1]', 'varchar(50)')
    
    
    		
    		/* pega os metadados e popula tabela temporaria */		
    		
    		DECLARE cCampos CURSOR
    		FAST_FORWARD
    		FOR	
    			
    			select 
    				c.[name],
    				t.[name],
    				c.[length] 
    			from 
    				sysobjects o 
    				inner join syscolumns c on o.id = c.id
    				inner join systypes t on c.xtype = t.xtype
    				left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@bancotabela,N'column',null) e on e.objname COLLATE Latin1_General_CI_AI =c.name COLLATE Latin1_General_CI_AI 
    			where 
    				o.name=@bancotabela
    			order by c.colorder
    
    		Open cCampos
    		FETCH NEXT FROM cCampos INTO @cCamposNome,@cCamposTipo,@cCamposTamanho
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			
    			
    			--existe o nó?
    			IF (SELECT @xml.exist('//dados//campo[@id=sql:variable("@cCamposNome")]')) = 1 
    			BEGIN
    			
    				set @cCamposString= 'ALTER TABLE #BDDinamico ADD '+ @cCamposNome +' VARCHAR(20) NULL'
    				exec(@cCamposString)
    
    
    				IF @cCamposTipo = 'varchar'
    				BEGIN
    				
    					DECLARE @cCamposValor varchar(255)
    					SELECT @cCamposValor=item.value('@value', 'VARCHAR(255)') FROM @xml.nodes('//dados//campo[@id=sql:variable("@cCamposNome")]') t (item)
    
    				END
    
    				set @cCamposString= 'UPDATE #BDDinamico SET '+ @cCamposNome + '='''+ @cCamposValor +''''
    				exec(@cCamposString)
    			
    			END
    			
    			
    	
    	
    		FETCH NEXT FROM cCampos INTO @cCamposNome,@cCamposTipo,@cCamposTamanho
    		END
    		CLOSE cCampos
    		DEALLOCATE cCampos
    
    
    		select * FROM #BDDinamico 
    

     

    O problema foi que eu pensei assim, ler todos os campos jogar em uma tabela temporária e depois fazer o INSERT INTO (CAMPOS) (SELECT...)

    Como faço isto dinamicamente na linha:

    set @cCamposString= 'UPDATE #BDDinamico SET '+ @cCamposNome + '='''+ @cCamposValor +''''
    				exec(@cCamposString)
    

    EStou sendo obrigado a jogar qualquer valor que vier em uma variável do tipo varchar(MAX).

    Alguém tem alguma idéia de como continuar isto? ou de fazer o que propuz no problema?

    Obrigado

    New Civic.

     

     

    quinta-feira, 25 de novembro de 2010 19:41
  • New Civic,

    Eu fiz isso uma vez mas eu usei os comandos dentro do C#.Net

    Dentro do C#.Net ou VB.Net, você tem mais recursos que no TSQL, e fiz de forma que o programa identificasse as chaves, tipos de dados, etc.

    e dentro do programa um tratamento especial pra cada tipo de dados, esse problema que você relata do NTEXT, é fácil de resolver.

    Em linhas gerais você pode criar uma barra de funções que trate essas operações padrão insert, updade, delete e até mesmo select básico sem muitas frescuras.

    Mas caso tenha interesse em aprofundar posso te passar um esboço do bem projeto.

     

    Att.
    Junior

    • Editado Wander Junior sexta-feira, 26 de novembro de 2010 04:36 erro de digitação
    sexta-feira, 26 de novembro de 2010 04:29
  • Wander,

     

    Agradeço seu contato. Gostaria sim de que se possível me passe este esboço. O intuito é facilitar a programação. Pois o desenvolvimento aqui é muito específico.

    Está ficando grande de mais o sistema e a manutenção está dificil.

    OBrigado

    New Civic.

     

    sexta-feira, 26 de novembro de 2010 10:10
  • Porém, gostaria de tentar fazer em SQL, porque o input não vai vir somente do ASP.NET, vai vir também se der certo a função de alguns programas em DELPHI e um em VB.

     

    sexta-feira, 26 de novembro de 2010 10:35
  • sobre a questão do ntext, consegui fazer um teste assim:

    
    DECLARE @param1 nvarchar(MAX)
    DECLARE @param2 Nvarchar(MAX)
    DECLARE @ID		int
    DECLARE @texto varchar(MAX)
    
    set @ID = 1
    set @texto = '
    
    texto com muitos caracteres...
    
    '		
    
    SELECT @param1 = 'UPDATE teste SET textolongo=@Xtexto WHERE id=@XId'
    SELECT @param2 = '@Xid int,@Xtexto varchar(MAX)'
    EXEC sp_executesql @param1, @param2, @ID,@texto
    
    select DATALENGTH(textolongo),textolongo from teste where id=@ID
    
     
    

     E a resposta foi 23550 caracteres, acho que funcionou..

    vou implentar no meu código e ver o que dá..

    sexta-feira, 26 de novembro de 2010 12:11
  • Consegui fazer em STORED PROCEDURE.

    Claro que falta os outros tipos de dados e tudo mais, e pode ser que dependendo do que tiver sendo inserido ou o número de inserções pode ser que fique lento o sistema. Mas com certeza ter uma stored procedure padrão que auxilie o trabalho de um desenvolvedor é uma mão na roda..

    SEGUE o código que ficou:

    declare @xml				xml,
    		@iddoc				int,
    		@banconome			varchar(50),
    		@bancoschema		varchar(50),
    		@bancotabela		varchar(50),
    		@bancocomando		varchar(50),
    		@bancocompleto		varchar(255),
    		@bancocampos		varchar(255),
    		@SQLString			varchar(MAX),
    		
    		@cCamposNome		varchar(50),
    		@cCamposTipo		varchar(50),
    		@cCamposTamanho		varchar(10),
    		@cCamposString		nvarchar(MAX),
    		@cCamposParametros	nvarchar(MAX),
    		@cCamposTexto		varchar(MAX)
    
    
    		IF object_id('tempdb.dbo.#BDDinamico') is not null
    		BEGIN
    			DROP TABLE #BDDinamico
    		END	
    
    		CREATE TABLE #BDDinamico (BDDinamicoID INT)
    		INSERT INTO #BDDinamico (BDDinamicoID) VALUES (1)
    
    
    		select @xml='<?xml version="1.0" encoding="iso-8859-1" ?> 
    		<Comando>
    			<configuracao>
    				<banconome>S3</banconome>
    				<bancoschema>dbo</bancoschema>
    				<bancotabela>teste</bancotabela>
    				<bancocomando>INSERT INTO</bancocomando>
    			</configuracao>
    	
    			<dados>
    				<campo id="texto" value="JOSÉ DA SILVA"/>
    				<campo id="caracter" value="a"/>
    				<campo id="textolongo" value="aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10aaaaaaaa10" />
    			</dados>
    		</Comando>'
    
    
    		/* prepara o xml para ser lido */
    		EXEC sp_xml_preparedocument @iddoc OUTPUT, @xml
    
    
    		/* pega as informações de banco¨*/
    		SELECT	@banconome=@xml.value('(/Comando/configuracao/banconome)[1]', 'varchar(50)'),
    				@bancoschema=@xml.value('(/Comando/configuracao/bancoschema)[1]', 'varchar(50)'),
    				@bancotabela=@xml.value('(/Comando/configuracao/bancotabela)[1]', 'varchar(50)'),
    				@bancocomando=@xml.value('(/Comando/configuracao/bancocomando)[1]', 'varchar(50)')
    		
    		SET @bancocompleto = @banconome + '.' + @bancoschema + '.' + @bancotabela
    		SET @bancocampos = ''
    
    
    		
    		/* pega os metadados e popula tabela temporaria */		
    		
    		DECLARE cCampos CURSOR
    		FAST_FORWARD
    		FOR	
    			
    			select 
    				c.[name],
    				t.[name],
    				c.[length] 
    			from 
    				sysobjects o 
    				inner join syscolumns c on o.id = c.id
    				inner join systypes t on c.xtype = t.xtype
    				left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@bancotabela,N'column',null) e on e.objname COLLATE Latin1_General_CI_AI =c.name COLLATE Latin1_General_CI_AI 
    			where 
    				o.name=@bancotabela
    			order by c.colorder
    
    		Open cCampos
    		FETCH NEXT FROM cCampos INTO @cCamposNome,@cCamposTipo,@cCamposTamanho
    		WHILE @@FETCH_STATUS = 0
    		BEGIN
    			
    			
    			--existe o nó?
    			IF (SELECT @xml.exist('//dados//campo[@id=sql:variable("@cCamposNome")]')) = 1 
    			BEGIN
    			
    				SET @bancocampos = @bancocampos + @cCamposNome + ','
    			
    			
    				IF @cCamposTipo = 'varchar' or @cCamposTipo = 'char' or @cCamposTipo = 'ntext'
    				BEGIN
    					
    					IF @cCamposTipo = 'ntext' 
    					BEGIN
    						set @cCamposString= 'ALTER TABLE #BDDinamico ADD '+ @cCamposNome + ' ' + @cCamposTipo + ' NULL'
    					END
    					ELSE
    					BEGIN
    						set @cCamposString= 'ALTER TABLE #BDDinamico ADD '+ @cCamposNome + ' ' + @cCamposTipo+'('+@cCamposTamanho+') NULL'
    					END
    					exec(@cCamposString)
    
    
    
    					SELECT @cCamposTexto = item.value('@value', 'VARCHAR(MAX)') FROM @xml.nodes('//dados//campo[@id=sql:variable("@cCamposNome")]') t (item)
    					SET @cCamposString= 'UPDATE #BDDinamico SET '+ @cCamposNome+'=@XcCamposTexto'
    					set @cCamposParametros = '@XcCamposTexto VARCHAR(MAX)'
    					EXEC sp_executesql @cCamposString, @cCamposParametros, @cCamposTexto
    				
    				
    				
    				END
    
    				
    			
    			END
    	
    		
    		FETCH NEXT FROM cCampos INTO @cCamposNome,@cCamposTipo,@cCamposTamanho
    		END
    		CLOSE cCampos
    		DEALLOCATE cCampos
    
    	
    	
    	
    		--remove ID que não serve para nada
    		ALTER TABLE #BDDinamico
    		DROP COLUMN BDDinamicoID
    		
    	
    		--insere comando	
    		
    		SET @bancocampos = LEFT(@bancocampos,DATALENGTH(@bancocampos)-1)
    		SET @SQLString = 'INSERT INTO '+@bancocompleto+ ' ('+ @bancocampos + ') (SELECT * FROM #BDDinamico)'
    	 exec(@SQLString) 
    
    OBRIGADO A TODOS QUE ME AJUDARAM NESTA QUESTÃO....
    • Marcado como Resposta New Civic sexta-feira, 26 de novembro de 2010 12:51
    sexta-feira, 26 de novembro de 2010 12:51