title: Generic Insert stored proc : Runtime error
body: <p>The following code generates the primaey key for the new record to be inserted and inserts the record into a table, whose name and the values to be inserted are given as parameters to the stored procedure. I am getting a runtime error. I am using Visual Studio 2005 to work with SQL Server 2005 Express Edition</p>
  
  <pre><code>ALTER PROCEDURE spGenericInsert
  
  (
      @insValueStr nvarchar(300), 
      @tblName nvarchar(10) 
  )
  
  
  AS
  
  DECLARE @sql nvarchar(400)
  DECLARE @params nvarchar(200)
  DECLARE @insPrimaryKey nvarchar(10)
  DECLARE @rowCountVal integer
  DECLARE @prefix nvarchar(5)
  
  --following gets the rowcount of the table--
  SELECT @rowCountVal = ISNULL(SUM(spart.rows), 0)
      FROM sys.partitions spart 
      WHERE spart.object_id = object_id(@tblName) AND spart.index_id &lt; 2
  
  SET @rowCountVal = @rowCountVal+1
  
  
  
  
  --Following Creates the Primary Key--
  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'
  
  
  -- Following is for inserting into the table --
  
  SELECT @sql =   N' INSERT INTO @tableName VALUES ' +
          N' ( @PrimaryKey , @ValueStr )'
  
  SELECT @params = N'@tableName nvarchar(10), ' +
                      N'@PrimaryKey nvarchar(10), ' +
                      N'@ValueStr  nvarchar(300)'
  
  EXEC sp_executesql @sql, @params, @tableName=@tblName, @PrimaryKey=@insPrimaryKey, @ValueStr=@insValueStr
  </code></pre>
  
  <p>Output Message:</p>
  
  <pre><code>Running [dbo].[spGenericInsert] ( @insValueStr = 2,"Hi",1/1/1987, @tblName = DEFECT_LOG ).
  
  Must declare the table variable "@tableName".
  
  No rows affected.
  
  (0 row(s) returned)
  
  @RETURN_VALUE = 0
  
  Finished running [dbo].[spGenericInsert].
  </code></pre>