Insert records using EXEC syntaxe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert records using EXEC syntaxe

Author

Topic

ttornado
Starting Member

1 Post

Posted - 2004-09-14 : 10:32:21

Hi all,
I have to insert records in transact as the following code but it not work. That is the code I use:
DECLARE @CFisc varchar(50)
DECLARE @IdImm INT
DECLARE @Dim INT
DECLARE @TipoImm varchar(50)
DECLARE @piano INT
DECLARE @interno INT
DECLARE @RCatastale INT
DECLARE @AbitPrincipale bit
DECLARE @CCatastale varchar(50)
DECLARE @Aliq_ICI INT
DECLARE @importo INT
DECLARE @importo_atteso INT
DECLARE @DataPagamento varchar(50)
DECLARE @IdGeo varchar(50)
DECLARE @moltipl integer
DECLARE @PercRiv INT
DECLARE @PerPropr INT
DECLARE @InAff bit
DECLARE @CAP varchar(50)
DECLARE @VIA varchar(50)
DECLARE @frazione varchar(50)
DECLARE @num integer
DECLARE @comune varchar(50)
DECLARE @provincia varchar(50)
DECLARE @ScadRata varchar(50)
DECLARE @PagataRat bit
DECLARE @Detrazioni bit
DECLARE @Err varchar(100)
DECLARE @insertQ varchar(3000)
SET @IdImm = 1
SET @CFisc ='dfds'
SET @IdGeo ='fdg'
SET @Dim = 2
SET @TipoImm = 'fghgf'
SET @VIA ='tyr'
SET @num = 90
SET @piano = 34
SET @interno =6
SET @CAP ='cap'
SET @comune ='COMU'
SET @frazione ='FRA'
SET @provincia ='PROV'
SET @RCatastale =21
SET @AbitPrincipale=1
SET @CCatastale ='pooyhkj'
SET @PerPropr =2
SET @InAff =1
SET @PagataRat =1
SET @importo =123
SET @DataPagamento = 'iouyiu'
SET @ScadRata ='jh'
SET @Detrazioni=1
SET @insertQ = ('INSERT INTO dbo.T_TMP_IMMOBILE(ID_IMMOBILE, COD_FISCALE, ID_GEO, DIMENSIONE, TIPO_IMMOBILE, VIA, NUM, PIANO, INTERNO, CAP, COMUNE, FRAZIONE, PROVINCIA, RENDITA_CATASTALE, ABIT_PRINCIPALE, CCATASTALE, PERCENTPROPRIETA, INAFFITTO, PAGATARATA, IMPORTO, DATA_PAGAMENTO, SCADENZA_PAGAMENTO, DETRAZIONI, DESCR) VALUES(' + @IdImm + ', ' + @CFisc + ', ' + @IdGeo + ', ' + @Dim + ', ' + @TipoImm + ', ' + @VIA + ', ' + @num + ', ' + @piano + ', ' + @interno + ', ' + @CAP + ', ' + @comune + ', ' + @frazione + ', ' + @provincia + ', ' + @RCatastale + ', ' + @AbitPrincipale + ', ' + @CCatastale + ', ' + @PerPropr + ', ' + @InAff + ', ' + @PagataRat + ', ' + @importo + ', ' + @DataPagamento + ', ' + @ScadRata + ', ' + @Detrazioni + ', ICI)')

EXEC insertQ

Some one can help me to solve the problem?
Thanks a lot

TT

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 10:44:01

EXEC(@insertQ)

???

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 10:51:41

why are you using Exec() at all? Why are you using dynamic SQL for this? just call the INSERT statement directly. Why are you overcomplicating this?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 11:05:16

Well let's see, there's more to it.

1. You need to convert non char values to char
2. You need to wrap char data in quotes
3. You need the EXEC(@...) to execute the statement

Now I dodn't check the order of the col list to the values, but they had better be in the correct order....I say this becasue your declaratives ahere not in the same order as the values clause....which makes it harder to work with. If everything is in the same order, it'a breeze.

Try this


DECLARE
  @CFisc varchar(50), @IdImm INT, @Dim INT, @TipoImm varchar(50), @piano INT, @interno INT
, @RCatastale INT, @AbitPrincipale bit, @CCatastale varchar(50), @Aliq_ICI INT, @importo INT
, @importo_atteso INT, @DataPagamento varchar(50), @IdGeo varchar(50), @moltipl integer, @PercRiv INT
, @PerPropr INT, @InAff bit, @CAP varchar(50), @VIA varchar(50), @frazione varchar(50), @num integer
, @comune varchar(50), @provincia varchar(50), @ScadRata varchar(50), @PagataRat bit, @Detrazioni bit
, @Err varchar(100), @insertQ varchar(3000) 
SELECT
  @IdImm = 1, @CFisc ='dfds', @IdGeo ='fdg', @Dim = 2, @TipoImm = 'fghgf' , @VIA ='tyr', @num = 90
, @piano = 34, @interno =6, @CAP ='cap', @comune ='COMU', @frazione ='FRA', @provincia ='PROV', @RCatastale =21
, @AbitPrincipale=1 , @CCatastale ='pooyhkj', @PerPropr =2, @InAff =1, @PagataRat =1, @importo =123
, @DataPagamento = 'iouyiu' , @ScadRata ='jh', @Detrazioni=1
SET
  @insertQ = 'INSERT INTO dbo.T_TMP_IMMOBILE('
	+ 'ID_IMMOBILE, COD_FISCALE, ID_GEO, DIMENSIONE, TIPO_IMMOBILE, VIA, NUM, PIANO, INTERNO, CAP, COMUNE'
	+ ', FRAZIONE, PROVINCIA, RENDITA_CATASTALE, ABIT_PRINCIPALE, CCATASTALE, PERCENTPROPRIETA, INAFFITTO'
	+ ', PAGATARATA, IMPORTO, DATA_PAGAMENTO, SCADENZA_PAGAMENTO, DETRAZIONI, DESCR) '
	+ ' VALUES(' 
	+ CONVERT(varchar(15),@IdImm) + ', ''' + @CFisc + ''', ''' + @IdGeo + ''', ' + CONVERT(varchar(15),@Dim) 
	+ ', ''' + @TipoImm + ''', ''' + @VIA + ''', ' + CONVERT(varchar(15),@num) + ', ' + CONVERT(varchar(15),@piano) 
	+ ', ' + CONVERT(varchar(15),@interno) + ', ' + @CAP + ', ' + @comune + ', ' + @frazione + ', ' + @provincia 
	+ ', ' + CONVERT(varchar(15),@RCatastale) + ', ' + CONVERT(char(1),@AbitPrincipale) + ', ''' + @CCatastale + ''', ' 
	+ CONVERT(varchar(15),@PerPropr) + ', ' + CONVERT(char(1),@InAff) + ', ' + CONVERT(char(1),@PagataRat) 
	+ ', ' + CONVERT(varchar(15),@importo) + ', ''' + @DataPagamento + ''', ''' + @ScadRata + ''', ' 
	+ CONVERT(char(1),@Detrazioni) + ', ''ICI'')'

SELECT @insertQ

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 11:06:38

What the Dr. said....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources