Dynamic SQL Problem

I'm getting "Invalid Column" error with below code. Can anyone Help?
CODE:
USE [Northwind]
GO
declare @.SQL varchar(1000), @.debug int
declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
int
set @.sTable="Orders"
set @.sField="OrderDate"
set @.sField2="employeeID"
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
--EXEC(@.SQL)Your @.variables are not passed into the Exec(), nor should they be. The
Exec() is run separately from the stored proceedure.
Here is a simple example of how I circumvent this:
declare @.SQL varchar(1000)
declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
set @.getTable='Orders'
set @.getField='OrderDate'
set @.getFilter='employeeID = 3'
SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
Set @.SQL = Replace(@.SQL,'getField',@.getField)
Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
EXEC(@.SQL)
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||that's fine except i need a @.employeeID variable. i hardcoded 3 just for
this simple example and will actually be passing 2 WHERE variables in the
production code. can you modify your code?
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:145010B5-D41D-4184-BE78-7F239164BD44@.microsoft.com...
> Your @.variables are not passed into the Exec(), nor should they be. The
> Exec() is run separately from the stored proceedure.
> Here is a simple example of how I circumvent this:
> declare @.SQL varchar(1000)
> declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
> set @.getTable='Orders'
> set @.getField='OrderDate'
> set @.getFilter='employeeID = 3'
> SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
> Set @.SQL = Replace(@.SQL,'getField',@.getField)
> Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
> Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
> EXEC(@.SQL)
>
> "Scott" wrote:
>|||My goof. Your doing pretty much what I am. However, you have double quotes
around you @.variables.
Also watch out for empty space, by declaring as char rather than varchar,
your string is longer than it needs to be.
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||You main problem is that you delimited your strings with double quote marks.
In T-SQL, strings are delimited with single quotes. i.e. set @.sTable='Order
s'
For readability of the print @.sql, I changed your variables from char(40) to
varchar(40). The code will execute fine with char(40), it just has a lot of
extra spaces.
If you change the variables to varchar or you happen to have a table name of
40 characters, the WHERE clause will fail because there will be no space
between them. I suggest you put a space in front of the word WHERE as I've
done below.
Finally, EXEC (@.SQL) is no longer the recommended best practices. You
should be using exex sp_executesql which requires unicode input so I changed
@.SQL from varchar(1000) to nvarchar(1000).
Hope that helps,
Joe
Here's the corrected code:
USE [Northwind]
GO
declare @.SQL nvarchar(1000), @.debug int
declare @.sTable varchar(40), @.sField varchar(40), @.sField2 varchar(40),
@.employeeID int
set @.sTable='Orders'
set @.sField='OrderDate'
set @.sField2='employeeID'
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + ' WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
exec sp_executesql @.sql
"Scott" wrote:

> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||Parameterised dynamic queries are best done using the sp_executesql system
procedure. No hassle, no fuss - pure execution.
ML
http://milambda.blogspot.com/