Procedure expects parameter Error

Question

Post reply

Procedure expects parameter Error

dean gross

SSCrazy

Points: 2632
More actions
August 14, 2007 at 8:14 am

#180393

I am getting this error from the following sproc. Could someone tell me what i have done wrong. I don't have paremeter names '@statement' so I'm confused.
Thanks
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
USE [PAR_WORK_IN_PROGRESS]
GO
/****** Object: StoredProcedure [dbo].[srptGrossProfitSalesByLibrary]    Script Date: 08/14/2007 10:08:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Dean Gross
-- Create date: 8/13/2007
-- Description: Calculate GPS
-- =============================================
CREATE PROCEDURE [dbo].[srptGrossProfitSalesByLibrary]
-- Add the parameters for the stored procedure here
@dbName varchar = PARFLS --database to query
,@GBU varchar(10) = PAR --global business unit
,@BookedDate smalldatetime = '20061231' --get bookings after this date
,@debug bit = 0
AS
DECLARE @sql varchar(max) -- sql statement
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--TODO add database parameter so that multiple dbs can be used easily
SET @SQL =
'SELECT dbo.Programs.Program
  , LEFT(loc.Work_Location, 1) + ''0'' AS DivNum
  , CASE WHEN LEFT(loc.Work_Location, 1)+ ''0'' = ''80'' THEN ''Buildings'' ELSE d.[Division Name] END AS Division
  , JM.Job_Number AS Job
  , Cust.Customer_Name AS Customer
  , Emp.Employee_First_and_Last_Name AS BDRep
  , SUM(BudDet.Budgeted_Cost_Amount) AS [Budgeted GPS]
  , dbo.Calendar.FP AS FinPer
  , dbo.Calendar.FY AS FinYear
  , ''New'' AS GPSType
CASE @dbName
  WHEN PARFLS THEN
   FROM dbo.Divisions AS d
    INNER JOIN dbo.Calendar
    INNER JOIN parfls.dbo.BudgetDetail AS BudDet
    INNER JOIN parfls.dbo.JobMaster AS JM ON BudDet.Job_Number = JM.Job_Number
    INNER JOIN parfls.dbo.Customer AS Cust
     ON JM.Customer_Number = Cust.Customer_Number
    INNER JOIN parfls.dbo.WorkLocation AS Loc
     ON JM.Office_Number = Loc.Work_Location
    INNER JOIN parfls.dbo.CostTypeReporting AS CTR
     ON BudDet.Cost_Type = CTR.CostType
     ON dbo.Calendar.dt = JM.Job_Booked_Date
    LEFT OUTER JOIN dbo.Programs
    INNER JOIN dbo.CustomerPrograms
     ON dbo.Programs.ProgramID = dbo.CustomerPrograms.ProgramID
     ON JM.Customer_Number = dbo.CustomerPrograms.Customer_number
    LEFT OUTER JOIN parfls.dbo.HrEmployee AS Emp
    INNER JOIN parfls.dbo.BusinessDevelopment AS BD
     ON Emp.Employee_Number = BD.Business_Development_Rep
     ON JM.Job_Number = BD.Job_Number
     ON d.[Division Number] = LEFT(Loc.Work_Location, 1) + ''0''
  WHEN CANFLS
   FROM dbo.Divisions AS d
    INNER JOIN dbo.Calendar
    INNER JOIN canfls.dbo.BudgetDetail AS BudDet
    INNER JOIN canfls.dbo.JobMaster AS JM ON BudDet.Job_Number = JM.Job_Number
    INNER JOIN canfls.dbo.Customer AS Cust
     ON JM.Customer_Number = Cust.Customer_Number
    INNER JOIN canfls.dbo.WorkLocation AS Loc
     ON JM.Office_Number = Loc.Work_Location
    INNER JOIN canfls.dbo.CostTypeReporting AS CTR
     ON BudDet.Cost_Type = CTR.CostType
     ON dbo.Calendar.dt = JM.Job_Booked_Date
    LEFT OUTER JOIN dbo.Programs
    INNER JOIN dbo.CustomerPrograms
     ON dbo.Programs.ProgramID = dbo.CustomerPrograms.ProgramID
     ON JM.Customer_Number = dbo.CustomerPrograms.Customer_number
    LEFT OUTER JOIN canfls.dbo.HrEmployee AS Emp
    INNER JOIN canfls.dbo.BusinessDevelopment AS BD
     ON Emp.Employee_Number = BD.Business_Development_Rep
     ON JM.Job_Number = BD.Job_Number
     ON d.[Division Number] = LEFT(Loc.Work_Location, 1) + ''0''
END
  WHERE   (JM.Job_Booked_Date > CONVERT(DATETIME, ''2007-01-01 00:00:00'', 102))
    AND (CTR.RptGroup = ''gp'')
    AND (JM.Office_Number BETWEEN ''200'' AND ''899'')
    AND (d.GBU = @GBU)
  GROUP BY JM.Office_Number
   , Cust.Customer_Name
   , JM.Job_Number
   , Emp.Employee_First_and_Last_Name
   , Loc.Work_Location, d.[Division Name]
   , dbo.Calendar.FP
   , dbo.Calendar.FY
   , d.[Division Number]
   , dbo.Programs.Program
  HAVING (SUM(BudDet.Budgeted_Cost_Amount) <> 0)'
if @debug =1 print @sql
EXEC sp_executesql @sql, @dbName ,@GBU ,@BookedDate
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

chane your datatype for the @sql to nvarchar

sp_executesql works with unicode characters

you really must stay away from this dynamic sql if possible

Everything you can imagine is real.

dean gross SSCrazy Points: 2632 More actions · Answer 2

Thanks why do recommend staying away from this dynamic sql?

I have mutiple identical dbs (for various companies in my corporation). I need to run the same query against several of them to produce a consolidated report. Is there a better approach?

TIA

Dean

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Here is an article that thoroughly explains dynamic sql.

http://www.sommarskog.se/dynamic_sql.html

It can possibly have Performance, and Security problems, but if used wisely, and in the correct places its okay.

bledu SSChampion Points: 12335 More actions · Answer 4

dlgross

i had a case similar to yours once. and i used dynamic sql

Everything you can imagine is real.