语法错误：通用插入的存储过程

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

我有问题compilin这个代码..谁知道什么是错误的语法

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

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.

我知道整数标识列..但我必须在表中使用AlphaNumeric ID在高度多用户内部网系统中插入新值。

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

记录不会从表中删除。所以问题是维护同步插入具有自动生成ID字段的记录。

Any suggestions how that can be done.

任何建议如何做到这一点。

4 个解决方案

#1

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:

我无法立即看到语法有什么问题(Jonathan Lonowski的敏锐眼光已经解决了这个问题),但代码有些问题:

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.

您可以创建动态SQL,因此您的代码可以适应SQL注入攻击。两个输入参数都以危险的方式使用。通过为每个表创建存储过程来解决此问题。所以你不必再生成SQL了。
There is no check if the table is not in the list used.

没有检查表是否不在使用的列表中。
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.

您的主键生成算法可以/将在多用户方案中创建重复键,或者从表中删除行。通过使用标识列或您正在使用的数据库中的某些其他功能来解决。

#2

Take your pick:

拿你的选择:

@TableName isn't defined

@TableName未定义

@tblName vs. @TableName

@tblName与@TableName

#3

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

老实说,你似乎为自己头疼。查看整数标识和IDENTITY语法。

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

除非您真的需要使用“DEF_00005”格式的密钥,否则它们将使您的生活更轻松。

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 |

#4

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...

实际上,SQL Server可能不需要分号,所以忽略它......

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

但是这里是一个开始学习SQL Server中存储过程的好地方。您也可以搜索Google以获取更多信息。

#1