'passing parameters into SQL server - "must declare scalar variable"

EDIT:

In an effort to clean up the SQL just a little, I've written the code as PeterHE suggested, and hardcoded in (just for testing) the test values. Now I'm getting a new error message: "in_eq_Equipment.in_eq_CategoryID_fk" could not be bound."

Here's the revised test code:

    select in_eq_ID, in_eq_TagNumber as TagNumber, Title1Item, in_eq_AssetDescription as Description, in_eq_ExtendedDescription as ExtendedDescription, in_eq_SerialNumber as SerialNumber, in_eq_ValuationAmount as TotalValue, in_eq_CustodianName as Name, in_eq_ComplexBuilding as ShortLocation, in_eq_SubLocationCode as ShortRoomNumber, in_ca_Categories.in_ca_CategoryName as CategoryName, in_eq_DispositionDate as DispositionDate, Departments.DepartmentCode, DATEADD (dd, 0, DATEDIFF (dd, 0, in_eq_Equipment.in_eq_AcquisitionDate)) as AcquisitionDate

from in_eq_Equipment, Departments

LEFT JOIN in_ca_Categories ON in_eq_Equipment.in_eq_CategoryID_fk = in_ca_Categories.in_ca_CategoryID       
where in_eq_Equipment.DepartmentID = CAST ('00000000-0000-0000-0000-000000000000' AS nvarchar(36)) and upper (in_eq_AssetDescription) LIKE upper ('%T$')

Rusty old programmer here returning... trying to test a stored procedure and running into this error message. Error:

Msg 137, Level 15, State 2, Line 18 Must declare the scalar variable "@DepartmentID".

// The test calling code:

DECLARE @SearchString varchar(30), @DispositionText varchar(200)
declare @DepartmentID uniqueidentifier;

SET @SearchString = 'T'
SET @DispositionText = '';
SET @DepartmentID = '00000000-0000-0000-0000-000000000000';
EXEC GetInventoryByAssetDescription @SearchString, @DispositionText, @DepartmentID

Actual stored procedure code (Yes, I know it's bad code--I didn't write it originally, but must modify it within a short timeframe):

USE [Inventory]
GO
/****** Object:  StoredProcedure [dbo].[GetInventoryByAssetDescription]    Script Date: 12/23/2019 9:09:51 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GetInventoryByAssetDescription]
      (
            @SearchString varchar(30),
            @DispositionText varchar(200) = null,
            @DepartmentID uniqueidentifier
      )  
AS
begin
    SET NOCOUNT ON
    declare @sql nvarchar (2000)

    select @SearchString=UPPER(@SearchString)
    set @sql = '   select in_eq_ID,
        in_eq_TagNumber as TagNumber,
        Title1Item,
        in_eq_AssetDescription as Description,
        in_eq_ExtendedDescription as ExtendedDescription,
        in_eq_SerialNumber as SerialNumber,
        in_eq_ValuationAmount as TotalValue,
        in_eq_CustodianName as Name,
        in_eq_ComplexBuilding as ShortLocation,
        in_eq_SubLocationCode as ShortRoomNumber,
        in_ca_Categories.in_ca_CategoryName as CategoryName,
        in_eq_DispositionDate as DispositionDate,
        Departments.DepartmentCode,
        DATEADD (dd, 0, DATEDIFF (dd, 0, in_eq_Equipment.in_eq_AcquisitionDate)) as AcquisitionDate
        from in_eq_Equipment, Departments
        where in_eq_Equipment.DepartmentID = @DepartmentID
            LEFT JOIN in_ca_Categories ON in_eq_Equipment.in_eq_CategoryID_fk = in_ca_Categories.in_ca_CategoryID
            WHERE upper (in_eq_AssetDescription) LIKE upper ('''+ @SearchString + ''')
            '


        set  @sql=@sql+'   ' + ISNULL(@DispositionText,' ')  + '  order by in_eq_AssetDescription'
        execute (@sql)
        return
end

sql-server scalar

Solution 1:^[1]

The where clause:

where in_eq_Equipment.DepartmentID = @DepartmentID

Should be:

where in_eq_Equipment.DepartmentID = '''+CAST(@DepartmentID AS nvarchar(36))+'''

P.S.: It does not look like you need dynamic sql at all. Also even if you need, should change to use sp_executesql with parameters.

Solution 2:^[2]

Dynamic SQLs are very bad. I am just giving the code which prevents the errors that you are seeing:

USE [Inventory]
GO
/****** Object:  StoredProcedure [dbo].[GetInventoryByAssetDescription]    Script Date: 12/23/2019 9:09:51 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GetInventoryByAssetDescription]
      (
            @SearchString varchar(30),
            @DispositionText varchar(200) = null,
            @DepartmentID uniqueidentifier
      )  
AS
begin
    SET NOCOUNT ON
    declare @sql nvarchar (2000)

select @SearchString=UPPER(@SearchString)
set @sql = '   select in_eq_ID,
    in_eq_TagNumber as TagNumber,
    Title1Item,
    in_eq_AssetDescription as Description,
    in_eq_ExtendedDescription as ExtendedDescription,
    in_eq_SerialNumber as SerialNumber,
    in_eq_ValuationAmount as TotalValue,
    in_eq_CustodianName as Name,
    in_eq_ComplexBuilding as ShortLocation,
    in_eq_SubLocationCode as ShortRoomNumber,
    in_ca_Categories.in_ca_CategoryName as CategoryName,
    in_eq_DispositionDate as DispositionDate,
    Departments.DepartmentCode,
    DATEADD (dd, 0, DATEDIFF (dd, 0, in_eq_Equipment.in_eq_AcquisitionDate)) as AcquisitionDate
    from in_eq_Equipment, Departments
        LEFT JOIN in_ca_Categories ON in_eq_Equipment.in_eq_CategoryID_fk = in_ca_Categories.in_ca_CategoryID
        WHERE 
           in_eq_Equipment.DepartmentID = '''+CAST(@DepartmentID AS nvarchar(36))+'''
           upper (in_eq_AssetDescription) LIKE upper ('''+ @SearchString + ''')' --searchString is already converted UPPER CASE at the beginning, not sure why its converted here again.

    set  @sql=@sql+'   ' + ISNULL(@DispositionText,' ')  + '  order by in_eq_AssetDescription'
    execute (@sql)
    return

end

And below is the refactored stored procedure which directly takes your SQL inputs and returns the resultset:

USE [Inventory]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GetInventoryByAssetDescription]
      (
            @SearchString varchar(30),
            @DispositionText varchar(200) = null,
            @DepartmentID uniqueidentifier
      )  
AS
begin
    SET NOCOUNT ON

    SET @SearchString = '''%' + UPPER('test') + ''''
select in_eq_ID
       , in_eq_TagNumber as TagNumber
       , Title1Item
       , in_eq_AssetDescription as [Description]
       , in_eq_ExtendedDescription as ExtendedDescription
       , in_eq_SerialNumber as SerialNumber
       , in_eq_ValuationAmount as TotalValue
       , in_eq_CustodianName as [Name]
       , in_eq_ComplexBuilding as ShortLocation
       , in_eq_SubLocationCode as ShortRoomNumber
       , in_ca_Categories.in_ca_CategoryName as CategoryName
       , in_eq_DispositionDate as DispositionDate
       , Departments.DepartmentCode
       , DATEADD (dd, 0, DATEDIFF (dd, 0, in_eq_Equipment.in_eq_AcquisitionDate)) as AcquisitionDate
from      in_eq_Equipment
JOIN      Departments      ON in_eq_equipment.departmentid = departments.id
LEFT JOIN in_ca_Categories ON in_eq_Equipment.in_eq_CategoryID_fk = in_ca_Categories.in_ca_CategoryID       
where (in_eq_Equipment.DepartmentID =  @DepartmentID OR @DepartmentID = '00000000-0000-0000-0000-000000000000') 
     and upper (in_eq_AssetDescription) LIKE @SearchString 
     --AND ISNULL(@DispositionText,' ') = ' '  -- Not able to understand this condition from your dynamic SQL
order by in_eq_AssetDescription  
        return
end

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	PeterHe
Solution 2

'passing parameters into SQL server - "must declare scalar variable"

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]