Problem with code syntax : Stored Procedure for Generic Insert

Question

I have problem compilin this code..can anyone tell whats wrong with the syntax

CREATE PROCEDURE spGenericInsert
    (
     @insValueStr nvarchar(200) 
     @tblName nvarchar(10) 
    )

AS

BEGIN

    DECLARE @insQueryStr nvarchar(400)
    DECLARE @insPrimaryKey nvarchar(10)
    DECLARE @rowCountVal integer
    DECLARE @prefix nvarchar(5)


    IF @tblName='HW_Master_DB'
     SET @rowCountVal=(SELECT COUNT(*) FROM HW_Master_DB)
    ELSE IF @TableName='SW_Master_DB'
     SET @rowCountVal=(SELECT COUNT(*) FROM SW_Master_DB)
    ELSE IF @TableName='INV_Allocation_DB'
     SET @rowCountVal=(SELECT COUNT(*) FROM INV_Allocation_DB)
    ELSE IF @TableName='REQ_Master_DB'
     SET @rowCountVal=(SELECT COUNT(*) FROM REQ_Master_DB)

    IF @tblName = 'DEFECT_LOG' 
     SET @prefix='DEF_'
    ELSE IF @tblName='INV_Allocation_DB'
     SET @prefix='INV_'
    ELSE IF @tblName='REQ_Master_DB'
     SET @prefix='REQ_'
    ELSE IF @tblName='SW_Master_DB'
     SET @prefix='SWI_'
    ELSE IF @tblName='HW_Master_DB'
     SET @prefix='HWI_' 


    SET @insPrimaryKey= @prefix + RIGHT(replicate('0',5)+ convert(varchar(5),@rowCountVal),5) -- returns somethin like 'DEF_00005'

    SET @insQueryStr= 'INSERT INTO ' + @tblName + ' VALUES (' + @insPrimaryKey + ',' + @insValueStr + ')'

    EXEC(@insQueryStr)

END

Answer 1

and your question?

Answer 2

Take your pick:

• @TableName isn't defined
• @tblName vs. @TableName

Answer 3

Aside from missing lots of semicolons, you're going to have to give us more to go on.

Actually, SQL Server might not need semicolons, so ignore that...

But here is a good place to start learning about stored prcedures in SQL server. You can search Google for some more as well.

Answer 4

I cannot immediately see what's wrong with the syntax (the sharp eye of Jonathan Lonowski has solved that already), but there are some things wrong with the code:

1. You create dynamic SQL, so your code is vunerable to SQL-injection attacks. Both the input parameters are used in a dangerous way. Solve this by creating a stored procedure for every table. So you don't have to generate SQL anymore.

2. There is no check if the table is not in the list used.

3. Your primary key generation algorithm can/will create duplicate keys in a multi-user scenario, or if rows are deleted from the table. Solve by using an identity column or some other feature from the database you are using.

Answer 5

Honestly, you seem to be making a headache for yourself. Check out integer identities and IDENTITY syntax.

Unless you are truly required to use keys in the "DEF_00005" format, they will make your life a lot easier.

CREATE TABLE DemoTable (
    Key INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Value VARCHAR(200)
);

INSERT INTO DemoTable (Value) VALUES ('Something');

SELECT * FROM DemoTable;

  | Key | Value     |
  |-----|-----------|
  | 1   | Something |

Answer 6

hi Jonathan,

I know about Integer Identity columns.. but i have to use a AlphaNumeric ID in the tables in inserting new values in a highly multi-user intranet system.

The records will not be deleted from the table. So problem is that of maintain synchronous insertion of records with ID field automatically generated.

Any suggestions how that can be done.