Must Declare Scalar variable - Stored procedure

Question

I keep getting an error that says:

[SqlException (0x80131904): Must declare the scalar variable "@DeviceSearch".]

Even though the value is being passed into the Stored Procedure. Here is the SQL Trace:

exec InventorySearch @FieldName=N'Value',@TableName=N'AssetTag',@SearchTerm=N'00002730',@DeviceSearch=1

Here is the variable declaration in the Stored Procedure and also the line that is using it:

@DeviceSearch nvarchar(50),
@FieldName AS nvarchar(50),
@TableName AS nvarchar(50),
@SearchTerm nvarchar(50)

WHERE (@DeviceSearch = 1 and LogicallyDeleted = 0 and '+ @TableName+ '.'+ @FieldName+ 
                                                            ' like ''%@SearchTerm%'')

Can anyone see what I need to do to correct this?

Well, by only seeing a limited part of what seems to be some dynamic SQL, it will be hard to help you — Lamak, Nov 11 '14 at 18:49
You are passing integer data whereas datatype for `@DeviceSearch` is defined as nvarchar. Try to change Data Type on one end and it should work. — Sanket Tarun Shah, Nov 11 '14 at 18:54

Dudi Konfino · Answer 1 · 2014-11-11T19:22:26.340

0

try this i believe it will help

   exec
InventorySearch 
@FieldName=N'Value',
@TableName=N'AssetTag',
@SearchTerm=N'00002730',
@DeviceSearch='1'

and also

WHERE (@DeviceSearch = '1' and LogicallyDeleted = 0
       and '+ @TableName+ '.'+ @FieldName+ 
      ' like ''%@SearchTerm%'')

edited Nov 11 '14 at 19:22

answered Nov 11 '14 at 18:56

Dudi Konfino

1,126
2
13
24

score 0 · Accepted Answer · answered Nov 11 '14 at 18:59

0

You can Debug Your Query using output from print statements while executing query

ALTER PROCEDURE [dbo].[InventorySearch] 
    @FieldName AS nvarchar(50),
    @TableName AS nvarchar(50), 
    @SearchTerm nvarchar(50),
    @DeviceSearch bit
AS 
BEGIN
 DECLARE @SQL NVARCHAR(MAX)=

'SELECT AssetTag.Value, Device.LK_User, Device.LK_Location,
        AssetTag.CreatedOn, Device.AssetTypeId, AssetTag.StatusTypeId, 
        Device.DeviceID, Device.SerialNumber, Device.LogicallyDeleted
 FROM Device INNER JOIN AssetTag ON Device.DeviceID = AssetTag.DeviceID  
 WHERE '+CAST(@DeviceSearch as nvarchar(5))+'=1 and LogicallyDeleted = 0 and '+ (@TableName)+ '.'+ 
          (@FieldName) 
        +' like ''%' + (@SearchTerm ) +'%'''

print (@SQL)
EXEC (@SQL)
END

answered Nov 11 '14 at 18:59

Dgan

10,077
1
29
51

CAST(@DeviceSearch as nvarchar(5)) on device search took care of it. Thank You for the suggestion of using the print(@SQL). I am new to writing Stored procedures and had never seen that before. One other question ... I have seen where people bring up SQL Injection concerns when using Dynamic SQL and rightly so. Is there anything I should consider with this answer or do you know of some good references I can look at? – michaelk46 Nov 11 '14 at 20:36
@michaelk46 you can refer this http://stackoverflow.com/questions/4102387/how-to-cleanse-dynamic-sql-in-sql-server-prevent-sql-injection and http://msdn.microsoft.com/en-us/magazine/cc163523.aspx – Dgan Nov 12 '14 at 05:15
@michaelk46 its my pleasure – Dgan Nov 12 '14 at 13:14

Must Declare Scalar variable - Stored procedure

2 Answers2