Home SQL Server : query that doesn't work with parameters
Reply: 1

SQL Server : query that doesn't work with parameters

mbcharney
1#
mbcharney Published in 2017-11-13 17:48:37Z

I am not sure why this query works one way and not the other.

If I use the following query:

Select 
    dlb.referenceid, dlb.partnum, 
    pd.width, pd.length,
    dlb.partqty,
    Convert(date, dlb.entrydatetime, 101) as entrydatetime,
    dlb.status, dlb.material 
From 
    tbl_dlbase dlb 
Join 
    tbl_partdetails pd On dlb.referenceid = pd.referenceid 
Where 
    (pd.Spacing = 3) 
    And ((dlb.status = 'Available') or (dlb.status = 'Reserved')) 
Order By 
    dlb.status

It returns one result as it should.

If I use the exact same query but with parameters:

Select 
    dlb.referenceid, dlb.partnum,
    pd.width, pd.length,
    dlb.partqty,
    Convert(date, dlb.entrydatetime, 101) as entrydatetime,
    dlb.status, dlb.material 
From 
    tbl_dlbase dlb 
Join 
    tbl_partdetails pd On dlb.referenceid = pd.referenceid 
Where 
    (@parameter1 = @Criteria1) 
    And ((dlb.status = 'Available') or (dlb.status = 'Reserved')) 
Order By 
    dlb.status

And have the following parameters declared and set as follows:

Declare @parameter1 varchar(50)
Declare @Criteria1 varchar(50)
Set @parameter1 = 'pd.Spacing'
Set @Criteria1 = 3

The query returns no results.

I have used this type parameter query many times before but for some reason this time it won't work. Can anyone tell me what stupid thing I am missing or screwed up? I can't seem to see anything wrongs with it.

scsimon
2#
scsimon Reply to 2017-11-13 18:27:03Z

You can't use parameters as object names without dynamic sql. Here is a fix... just change the print to exec(@sql) when you are happy with it...

Declare @parameter1 varchar(50)
Declare @Criteria1 varchar(50)
Set @parameter1 = 'pd.Spacing'
Set @Criteria1 = '3'

declare @sql varchar(max) 
set @sql =
'Select 
    dlb.referenceid, 
    dlb.partnum, 
    pd.width, 
    pd.length, 
    dlb.partqty, 
    Convert(date, dlb.entrydatetime, 101) as entrydatetime, 
    dlb.status, 
    dlb.material 
From tbl_dlbase dlb 
Join tbl_partdetails pd On 
dlb.referenceid = pd.referenceid
Where (' + @parameter1 + ' = ' + @Criteria1 + ') 
AND ((dlb.status = ''Available'') OR (dlb.status = ''Reserved'')) 
Order By dlb.status'

print @sql
--exec(@sql)
--sp_executesql @sql

This makes your where clause Where (pd.Spacing = 3) otherwise the where clause is simply comparing the parameters, which obviously aren't the same since they aren't set to the same thing. It's the same thing as writing:

Select 'True'
where @parameter1 = @Criteria1

which is interpreted as

Select 'True'
where 'pd.Spacing'= '3'    --string literals, not column names.
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305252 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO