How do I reference a field in a dynamic sql query?

Question

I have a dynamic sql query in SQL Server. So I build it, set it to a varible and then I attempt to format a where clause based on a date. However, when I attempt to do this, I get "The multi-part identifier 'FIELD NAME" can not be bound. I beleive this is because the actual tables are in a dynamic from claus so they can't be seen until it is compiled. Any way around this?

Here I am attempting to say, give me all Persons where DOB between YEAR+MONTH specified, for example, 201001 and 201012 would be the entire year of 2010. Here is the code in part....

    ALTER PROCEDURE get_persons_by_search_criteria

@month_from as nvarchar(2) = null,
@year_from as nvarchar(4) = null,
@month_to as nvarchar(2) = null,
@year_to as nvarchar(4) = null


AS

declare @from_date varchar(10)
declare @to_date varchar(10)
declare @sqlstr varchar(5000)

set @sqlstr = ' SELECT     
    Person.PersonID, 
    Person.FirstName, 
    Person.LastName, 
FROM    Person '

--Attemtping to create a value like 201108 (year + month)
set @from_date = Convert(VarChar(10), @year_from) + Replace(Str(@month_from, 2), ' ', '0')  
set @to_date = Convert(VarChar(10), @year_to) + Replace(Str(@month_to, 2), ' ', '0') 

set @sqlstr = @sqlstr +  ' WHERE '
set @sqlstr = @sqlstr + Convert(VarChar(10), Person.DOBYear) + Replace(Str(Person.DOBMonth, 2), ' ', '0') 
set @sqlstr = @sqlstr + ' BETWEEN '   + @from_date + ' and ' + @to_date


exec(@sqlstr)

This doesn't look like it needs to be dynamic SQL. Why can't you use [this technique](http://stackoverflow.com/questions/7212339/how-do-i-do-a-comparison-with-just-a-month-and-year-not-a-complete-date/7214021#7214021) from your previous question? — Martin Smith, Aug 27 '11 at 19:40
Please indicate the version of SQL Server when asking questions. @Martin's memory is clearly better than mine, but we're not all going to know what you answered last time you were asked "What version of SQL Server?" — Aaron Bertrand, Aug 27 '11 at 21:58

score 1 · Accepted Answer · answered Aug 27 '11 at 19:36

This line gives the error, because the PERSON table is not open when you build the dynamic string.

set @sqlstr = @sqlstr + Convert(VarChar(10), Person.DOBYear) + Replace(Str(Person.DOBMonth, 2), ' ', '0')

Try this

set @sqlstr = @sqlstr + ' Convert(VarChar(10), Person.DOBYear) + Replace(Str(Person.DOBMonth, 2), '' '', ''0'') '

Should do the trick for you..

score 1 · Answer 2 · edited Jul 29 '20 at 18:31

I realize you've already fixed your issue and accepted an answer, but I thought I would also point out a few other potential improvements (both for you and for any future readers of the question).

ALTER PROCEDURE dbo.get_persons_by_search_criteria
    @month_from VARCHAR(2) = NULL,
    @year_from  VARCHAR(4) = NULL,
    @month_to   VARCHAR(2) = NULL,
    @year_to    VARCHAR(4) = NULL
AS
BEGIN 
   SET NOCOUNT ON;

   SELECT
       PersonID, DOBYear, DOBMonth
   FROM
       dbo.Person
   WHERE 
       DOBYear + RIGHT('0' + DOBMonth, 2) + '01'
       BETWEEN @year_from + RIGHT('0' + @month_from, 2) + '01'
           AND @year_to   + RIGHT('0' + @month_to,   2) + '01'
   ORDER BY 
       PersonID, DOBYear, DOBMonth;
END
GO

Isn't that easier on the eyes, easier to follow, and easier to maintain?

Summary:

always use the schema prefix when creating, altering or referencing objects.
don't use Unicode (NCHAR/NVARCHAR) when you don't need to support Unicode data (numbers will never need to contain umlauts, for example). Choosing the right data type might not be that important in this specific case, but it can be crucial in others.
wrap your procedure body in BEGIN/END - this will prevent you from unknowingly picking up other unwanted code from the query window. And always use SET NOCOUNT ON at the beginning of your procedures. I address these and other issues in my "stored procedure best practices checklist."
to avoid changes in behavior, you should always include an ORDER BY clause. If today it orders by first name, and tomorrow it starts ordering by last name, someone is going to complain. See the second section of this post.
learn to write SQL without dynamic SQL, when possible. If you're going to continue using dynamic SQL, at least please try to use sp_executesql instead of EXEC(). I explained the reasons in another recent question: SQL Server use EXEC/sp_executesql or just plain sql in stored procedure?

Even better would be to just store their date of birth as a DATE in the first place. Why would you store the year and month as separate strings? There must be some reason you are doing this but I can't imagine what it is. All it does is make this kind of string matching less efficient than if you were actually using dates, reduces your ability to perform any type of date operations on the values, and makes it very difficult to validate the values passed in. Right now your stuff is going to choke later than it should have to if someone calls the following:

EXEC get_persons_by_search_criteria 
    @month_from = '97',
    @year_from  = 'Audi',
    @month_to   = 'TT',
    @year_to    = 'Oy!!';

Which they could do, because you perform no validation whatsoever. With DATE variables at least the error message that comes back would make sense. Right now with either of our versions they'll just get an empty result set.

How do I reference a field in a dynamic sql query?

2 Answers2