Stored procedure with table name and values as parameters

Question

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.

"My table name is formed by a table name + an Id" Be aware this is a bad design. I suggest you reconsider and change this as soon as possible. — Nick.Mc
– Nick.Mc, Commented Feb 21, 2015 at 23:17

angel_neo · Accepted Answer · 2015-02-23 14:24:36Z

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.

Giorgi Nakeuri · Accepted Answer · 2015-02-21 20:57:47Z

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

Gregg · Accepted Answer · 2015-02-21 21:44:01Z

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

Collectives™ on Stack Overflow

Stored procedure with table name and values as parameters

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related