CODEWITHSUNDEEP
sqlsql-serverstored-procedures
michaelk46
michaelk46

Reputation: 157

variable tablename and fieldname in Stored procedure

When I try to save the following Stored procedure, I get the error:

Msg 102, Level 15, State 1, Procedure InventorySearch, Line 29 Incorrect syntax near '@FieldName'.

How can I pass in both @TableName and @FieldName and use them to define which table and field should be used to search the DB?

ALTER PROCEDURE [dbo].[InventorySearch] 
    @FieldName AS nvarchar(50),
    @TableName AS nvarchar(50), 
    @SearchTerm nvarchar(50),
    @DeviceSearch bit
AS 
BEGIN
    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 @TableName.@FieldName like @SearchTerm
END

Upvotes: 0

Views: 184

Answers (2)

Dgan
Dgan

Reputation: 10295

You Need to Create Dynamic SQL as Below :

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 '+ (@TableName)+ '.'+ 
          (@FieldName) 
        +' like ''%' + (@SearchTerm ) +'%'''

print (@SQL)
EXEC (@SQL)
END

Upvotes: 1

Raj
Raj

Reputation: 343

you can write your code using dynamic query like below.

ALTER PROCEDURE [dbo].[InventorySearch] 
    @FieldName AS nvarchar(50),
    @TableName AS nvarchar(50), 
    @SearchTerm nvarchar(50),
    @DeviceSearch bit
AS 
BEGIN
  DECLARE @Sql VARCHAR(MAX)=''
   SET @sql = '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 '+@TableName+'.'+@FieldName+' like '+@SearchTerm+''

  EXEC (@sql)
END

Upvotes: 0

Related Questions