I want to search GTIN option in admin product list. So, for that I am providing GTIN value in ProductLoadAllPaged store procedure. Now, when I search GTIN value from product list at that time throw datatable error and from console application get message that System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@GTIN"..
Here is store procedure added code,
ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
@GTIN nvarchar(50) = null--AWAZ
)
AS
BEGIN
...........
--SKU (exact match)
IF @SearchSku = 1
BEGIN
SET @sql = @sql + 'OR p.[Sku] = @OriginalKeywords '
END
--NEW ADDED CODE FOR GTIN SEARCH
IF @GTIN is not null
BEGIN
SET @sql = @sql + 'AND p.Gtin = @GTIN'
END
--localized product name
SET @sql = @sql + '
UNION
SELECT lp.EntityId
FROM LocalizedProperty lp with (NOLOCK)
WHERE
lp.LocaleKeyGroup = N''Product''
AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
AND ( (lp.LocaleKey = N''Name'''
..........
END
In the stored procedure change this line of code:
EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000)', @Keywords, @OriginalKeywords
To this:
EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000), @GTIN nvarchar(50)', @Keywords, @OriginalKeywords, @GTIN
This will add your new parameter when executing the dynamic SQL query.
User contributions licensed under CC BY-SA 3.0