无参数存储过程插入表数据及联表插入存储过程报错求助

Hey there! Let's tackle your two stored procedure needs one by one—starting with the first request, then diving into troubleshooting the error in your second procedure.

Creating a parameterless stored procedure for inserting data is straightforward—you just wrap your INSERT logic inside the procedure's BEGIN...END block. Here's a practical example tailored to common use cases:

CREATE PROCEDURE [dbo].[stored_proc_insert_data]
AS
BEGIN
    -- Option 1: Insert hardcoded static values
    INSERT INTO [dbo].[YourTargetTable] (col1, col2, created_date)
    VALUES ('sample_val1', 'sample_val2', GETDATE());

    -- Option 2: Insert filtered data from another table (e.g., last 7 days)
    -- INSERT INTO [dbo].[YourTargetTable] (col1, col2, created_date)
    -- SELECT source_col1, source_col2, source_date
    -- FROM dbo.YourSourceTable
    -- WHERE source_date >= DATEADD(dd, -7, CAST(GETDATE() AS DATE));
END
GO

To run it, just execute EXEC dbo.stored_proc_insert_data—no parameters needed.

First, let's re-post your code for reference:

CREATE PROCEDURE [dbo].[stored_proc1] 
AS 
BEGIN 
    INSERT INTO [dbo].[IN_TABLE] 
    SELECT l.col1, l.col2, l.col3, l.col4, r.col1, r.col2 
    FROM db2.dbo.table1 AS l 
    LEFT JOIN dbo.[table2] AS r ON l.col1 = r.col2 
    WHERE l.col4 >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -7); 

    DELETE FROM dbo.[IN_TABLE] WHERE col4 < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),-7); 
END 
GO

Here are the most likely causes of your error, along with fixes:

• Column count/data type mismatch
  You didn't specify column names in your INSERT statement, which means the SELECT output must exactly match IN_TABLE's column count, order, and data types. If IN_TABLE has more/less than 6 columns, or a column's type doesn't align (e.g., l.col1 is INT but IN_TABLE's first column is VARCHAR), you'll get an error.
  Fix this by explicitly listing columns—it's safer and easier to debug:

  INSERT INTO [dbo].[IN_TABLE] (target_col1, target_col2, target_col3, target_col4, target_col5, target_col6) -- Replace with actual IN_TABLE column names
  SELECT l.col1, l.col2, l.col3, l.col4, r.col1, r.col2 
  FROM db2.dbo.table1 AS l 
  LEFT JOIN dbo.[table2] AS r ON l.col1 = r.col2 
  WHERE l.col4 >= DATEADD(dd, -7, CAST(GETDATE() AS DATE));
  

• Cross-database permission issues
  Your procedure accesses db2.dbo.table1—if the user executing the procedure doesn't have SELECT permissions on db2 or table1, you'll get a permission denied error. Check the user's permissions, or add an EXECUTE AS clause to run the procedure under a user with sufficient access:

  CREATE PROCEDURE [dbo].[stored_proc1] 
  WITH EXECUTE AS 'db2_read_user' -- Replace with a user that has access to db2.dbo.table1
  AS 
  BEGIN 
      -- Rest of your logic
  END 
  GO
  

• Date type mismatch or invalid date calculation
  If l.col4 isn't a date/time data type (e.g., it's stored as VARCHAR), comparing it to a date value will trigger a conversion error. Verify table1.col4's type, or explicitly cast it to a date:

  WHERE CAST(l.col4 AS DATE) >= DATEADD(dd, -7, CAST(GETDATE() AS DATE))
  

  Also, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -7) can be simplified to DATEADD(dd, -7, CAST(GETDATE() AS DATE)) for readability—same result.

• Constraint violations in IN_TABLE
  If IN_TABLE has primary key, unique, or non-null constraints, your SELECT output might violate them. For example, LEFT JOIN can return NULL values for r.col1/r.col2—if the corresponding columns in IN_TABLE are set to NOT NULL, this will fail. Fix this by handling NULLs (e.g., ISNULL(r.col1, 'default_value')) or adjusting the table constraints.

内容的提问来源于stack exchange，提问作者Krishnang K Dalal