[Solved] Procedure in SQL Server query

by


  1. You better specify all columns after your table #tempbe_budget like following:

    INSERT INTO #tempbe_budget(
    budgetno,
    Finyear,
    Deptid,
    …..
    )

  2. Add collation adjustment to your JOINS:

    FROM budget bud
    LEFT OUTER JOIN womaster wo
    ON wo.bcno1516 COLLATE SQL_Latin1_General_CP1_CI_AS = bud.newno COLLATE SQL_Latin1_General_CP1_CI_AS
    LEFT OUTER JOIN department dept
    ON dept.deptid COLLATE SQL_Latin1_General_CP1_CI_AS = bud.deptid COLLATE SQL_Latin1_General_CP1_CI_AS
    LEFT OUTER JOIN #tempbewo two
    ON two.bewobcno COLLATE SQL_Latin1_General_CP1_CI_AS = bud.newno COLLATE SQL_Latin1_General_CP1_CI_AS
    LEFT OUTER JOIN #tempbepo tpo
    ON tpo .bepobcno COLLATE SQL_Latin1_General_CP1_CI_AS = bud.newno COLLATE SQL_Latin1_General_CP1_CI_AS
    LEFT OUTER JOIN employee emp
    ON emp.empid COLLATE SQL_Latin1_General_CP1_CI_AS = bud.empid COLLATE SQL_Latin1_General_CP1_CI_AS

I’m not sure which one cause an error, you can experiment with that, but I’d guess collation of your database is different from Server collation and affects joins only to Temp tables.

  1. By your column names I see that they can be type INT and not CHAR/VARCHAR. Strongly suggest have appropriate data type.

solved Procedure in SQL Server query