Procedure returning zero rows when passing where clause as parameter

Question

My procedure returning nothing when pass where clause condition as parameter. Below is the example query and I am passing @strWhereClauseField & @strWhereClauseValue as a parameter. It works fine when I convert my query in string and execute it. But I don't want to use query string because order by is not working in query string.

DECLARE @Data TABLE
(
     Id int identity(1,1), Product varchar(10)
)

INSERT @Data VALUES ('HP2030'), ('HP2031'), ('HP2032'), ('HP2033');

DECLARE @strWhereClauseField NVARCHAR(1000) = '1'
DECLARE @strWhereClauseValue NVARCHAR(1000) = '1'

SET @strWhereClauseField = '@Data.Product'
SET @strWhereClauseValue = '''HP2030'''

SELECT * 
FROM @Data 
WHERE @strWhereClauseField = @strWhereClauseValue

user7351608 · Answer 1 · 2016-12-30T08:14:11.023

0

Unfortunately what you are doing will not work in SSMS (SQL Server Management Studio) as SSMS sees the query as so:

SELECT * FROM @Data WHERE '@Data.Product' = '''HP2030'''

Try the following:

DECLARE @Data TABLE
(
     Id int identity(1,1), Product varchar(10)
)

INSERT @Data VALUES ('HP2030'), ('HP2031'), ('HP2032'), ('HP2033');

DECLARE @strWhereClauseField NVARCHAR(1000) = '@Data.Product'
DECLARE @strWhereClauseValue NVARCHAR(1000) = '''HP2030'''
DECLARE @strOrderByColumn NVARCHAR(1000) = 'Product'


DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM @Data WHERE ' + @strWhereClauseField + '=' + @strWhereClauseValue + ' ORDER BY ' + @strOrderByColumn

EXEC sp_executesql(@sql)

edited Dec 30 '16 at 08:14

answered Dec 30 '16 at 07:45

user7351608

441
2
10

Just `EXEC(@sql)` You also have a problem that the scope in which `EXEC(@sql)` runs doesn't have access to the table variable. You'll need to use a more 'accessible' table or wrap table declaration and loading into `@sql` Finally `@WhereClauseField == '@Data.Product'` doesn't work. Either drop the table prefix or use an alias. – Disillusioned Dec 30 '16 at 08:55

score 0 · Accepted Answer · answered Dec 30 '16 at 07:53

0

Try this:

DECLARE    @strWhereClauseField NVARCHAR(1000) = '1'
DECLARE     @strWhereClauseValue NVARCHAR(1000) = '1'
DECLARE @QUERY VARCHAR(MAX)

SET @strWhereClauseField='Product'
SET @strWhereClauseValue='''HP2030'''

SET @QUERY = 'DECLARE @Data TABLE
(
 Id int identity(1,1)
,Product varchar(10)
 )
INSERT @Data VALUES
(''HP2030'');
INSERT @Data VALUES
(''HP2031'');
INSERT @Data VALUES
(''HP2032'');
INSERT @Data VALUES
(''HP2033'');
SELECT * FROM @Data WHERE '+@strWhereClauseField+'='+@strWhereClauseValue+''
EXEC (@QUERY)

answered Dec 30 '16 at 07:53

Ranjana Ghimire

1,785
1
12
20

appreciate your answer but i cannot use query string because order by is not working in query string and my original query is truncated due to its length. – user1891251 Dec 30 '16 at 07:57
@user1891251 `ORDER BY` works perfectly fine if you include it in the string. And exactly how long is your query that it doesn't fit in `varchar(max)`? – Disillusioned Dec 30 '16 at 08:59
Length is 5000 but it can vary based on parameters – user1891251 Jan 02 '17 at 07:53
@user1891251 5000 fits comfortably within `varchar(max)` Did you actually try this answer? – Disillusioned Jan 02 '17 at 22:11
I tried and finally get it with varchar(max) using string query and order by working is fine now. thanks buddy – user1891251 Jan 03 '17 at 10:43

Procedure returning zero rows when passing where clause as parameter

2 Answers2