CODEWITHSUNDEEP
sql-serverstored-procedures
angel_neo
angel_neo

Reputation: 331

Stored procedure with table name and values as parameters

I have problems for insert values with a stored procedure with values as parameters and table name too. this is part of my table:

My table:

CREATE TABLE [dbo].[INGRESOLOTEMP_100]
(
    [CLIENTE_CODIGO] [SMALLINT] NOT NULL,
    [CAJA_CODIGO] [nvarchar](20) NULL,
    [CAJA_NUMERO] [SMALLINT] NOT NULL,
)

My stored procedure:

CREATE PROCEDURE [dbo].[SP_LOT_INSERTLOTEMP]
    @IDENT_TABLA NVARCHAR(10),
    @CLIENTE_CODIGO SMALLINT,
    @CAJA_CODIGO NVARCHAR(15),
    @CAJA_NUMERO SMALLINT
AS
    DECLARE @NOMBRE_TABLA NVARCHAR(40)

    SELECT 
       @NOMBRE_TABLA = 'INGRESOLOTEMP_' + CONVERT(VARCHAR(100), @IDENT_TABLA)

    SELECT 
       @CAJA_CODIGO = CONVERT(VARCHAR(20), @CAJA_CODIGO)

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'INSERT into dbo.' + quotename(@NOMBRE_TABLA) + 
   ' (CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO) VALUES (' +
    CONVERT(VARCHAR(20),@CLIENTE_CODIGO) + ',' + @CAJA_CODIGO + ',' + 
    CONVERT(VARCHAR(20),@CAJA_NUMERO) + 
    ')'

    EXEC sp_executesql @SQL

My table name is formed by a table name + an Id (every user has one)

But the problem is when I execute the stored procedure:

SP_LOT_INSERTLOTEMP 100, 123, 'uio123', 1

I have an error in SQL with value: 'uio123'

But if I execute: 

SP_LOT_INSERTLOTEMP 100, 123, 80, 1

The insert is perfect, The problem is that I need insert numeric and text values, even date time values too. I realized varchar conversions in the stored procedure, but I don't get it yet.

Anyone, have an Idea?

Please I hope you can help me.

Upvotes: 1

Views: 991

Answers (3)

angel_neo
angel_neo

Reputation: 331

Gregg, Giorgi... Thanks, I followed your advices, I think I get it:

ALTER PROCEDURE [dbo].[SP_LOT_INSERTLOTEMP]
@IDENT_TABLA NVARCHAR(10),
@CLIENTE_CODIGO SMALLINT,
@CAJA_CODIGO NVARCHAR(15),
@CAJA_NUMERO SMALLINT
AS
BEGIN
DECLARE @NOMBRE_TABLA NVARCHAR(40)
SELECT @NOMBRE_TABLA = 'INGRESOLOTEMP_' +  CONVERT(VARCHAR(100),@IDENT_TABLA)


DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'INSERT into dbo.' + @NOMBRE_TABLA    
SET @SQL += '(CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO) VALUES ('
SET @SQL += CONVERT(VARCHAR(20), @CLIENTE_CODIGO) + ', '  
SET @SQL += '''' + CONVERT(VARCHAR(20), @CAJA_CODIGO) + ''', '  
SET @SQL += CONVERT(VARCHAR(20), @CAJA_NUMERO) + ')'

DECLARE @ParmDefinition nvarchar(500)
SET @ParmDefinition = '@IDENT_TABLA NVARCHAR(10), @CLIENTE_CODIGO SMALLINT, @CAJA_CODIGO NVARCHAR(15), @CAJA_NUMERO SMALLINT' 


SELECT @sql
PRINT @sql
EXEC sp_executesql @SQL, @ParmDefinition, @IDENT_TABLA, @CLIENTE_CODIGO, @CAJA_CODIGO, @CAJA_NUMERO

END

And when I print what execution contains:

EXEC [SP_LOT_INSERTLOTEMP] 100, 123, 'UIO123', 6

I have this:

INSERT into dbo.INGRESOLOTEMP_100(CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO)         VALUES (123, 'UIO123', 6)

I will continue, If I have any question, I hope somebody or you, can help me.

Thanks.

Upvotes: 1

Gregg
Gregg

Reputation: 625

Several changes needed to be made to your procedure; see below:

ALTER PROCEDURE [dbo].[SP_LOT_INSERTLOTEMP]
@IDENT_TABLA NVARCHAR(10),
@CLIENTE_CODIGO SMALLINT,
@CAJA_CODIGO NVARCHAR(15),
@CAJA_NUMERO SMALLINT
AS
BEGIN
DECLARE @NOMBRE_TABLA NVARCHAR(40)
SELECT @NOMBRE_TABLA = 'INGRESOLOTEMP_' +  CONVERT(VARCHAR(100),@IDENT_TABLA)
SELECT @CAJA_CODIGO = CONVERT(VARCHAR(20), @CAJA_CODIGO)

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'INSERT into dbo.' + @NOMBRE_TABLA    
SET  @SQL += '(CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO) 
VALUES (@CLIENTE_CODIGO, @CAJA_CODIGO,  @CAJA_NUMERO)'

DECLARE @ParmDefinition nvarchar(500)
SET @ParmDefinition = '@IDENT_TABLA NVARCHAR(10), @CLIENTE_CODIGO  SMALLINT, @CAJA_CODIGO NVARCHAR(15), @CAJA_NUMERO SMALLINT' 

SELECT @sql
EXEC sp_executesql @SQL, @ParmDefinition, @IDENT_TABLA, @CLIENTE_CODIGO, @CAJA_CODIGO, @CAJA_NUMERO

END

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

The problem is in varchar column. Notice the generated statements for

EXEC SP_LOT_INSERTLOTEMP 100, 123, 'uio123', 1
EXEC SP_LOT_INSERTLOTEMP 100, 123, 80, 1


INSERT into dbo.[INGRESOLOTEMP_100] (CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO) VALUES (123,uio123,1)
INSERT into dbo.[INGRESOLOTEMP_100] (CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO) VALUES (123,80,1)

Try uncommenting --PRINT @SQL and see yourself the resulting statements.

You should provide additional quotes. Try this:

ALTER PROCEDURE [dbo].[SP_LOT_INSERTLOTEMP]
@IDENT_TABLA NVARCHAR(10),
@CLIENTE_CODIGO SMALLINT,
@CAJA_CODIGO NVARCHAR(15),
@CAJA_NUMERO SMALLINT
AS
DECLARE @NOMBRE_TABLA NVARCHAR(40)
SELECT @NOMBRE_TABLA = 'INGRESOLOTEMP_' + CONVERT(VARCHAR(100),@IDENT_TABLA)
SELECT @CAJA_CODIGO = CONVERT(VARCHAR(20), @CAJA_CODIGO)

DECLARE @SQL NVARCHAR(MAX)
   SET @SQL = 'INSERT into dbo.' + quotename(@NOMBRE_TABLA) + 
   ' (CLIENTE_CODIGO, CAJA_CODIGO, CAJA_NUMERO) VALUES (' +
     + CONVERT(VARCHAR(20),@CLIENTE_CODIGO) +','''/*changes here*/ + @CAJA_CODIGO + ''','/*changes here*/ + 
    CONVERT(VARCHAR(20),@CAJA_NUMERO) + 
    ')'
  --PRINT   @SQL
 EXEC sp_executesql @SQL

Upvotes: 0

Related Questions