Posted by Erland Sommarskog on 08/14/07 21:19

Bhavna (bhavnabakshi@hotmail.com) writes:
> I have recently updated the stored procedure and now I am getting
> "Microsoft OLE DB Provider for ODBC Drivers error '80040e14' .
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
> 'bbakshi'. " error message. Here is the stored procedure
>
> CREATE PROCEDURE stp_ExportMeetings @selmeetings varchar(8000), @user
> varchar(50)
> AS
>
> DECLARE @sql varchar(8000)
> DECLARE @empty varchar(1)
> SET @empty = ''
>
>
> SELECT @sql = 'UPDATE tblWrkshops SET Exported = 1, ExportDate =
> GETDATE(), ExportedBy = "' + @user + '"
> WHERE (tblWrkshops.Exported is null) OR (tblWrkshops.Exported <> 1)
> and tblWrkshops.SetupNumb in (' + @selmeetings + ')'
> EXEC (@sql)

You get the error because you try to use " as a string delimiter. By
default, " delimits identifiers not string literals.

But you should never interpolate values into the query string. And in
this case there is no reason to use dynamic SQL at all:

UPDATE tblWrkshops
SET Exported = 1,
ExportDate = GETDATE(),
ExportedBy = @user
FROM tblWrkshops W
JOIN iter_intlist_to_table (@selmeetings) i ON W.SetupNumb = i.number
WHERE (tblWrkshops.Exported is null) OR (tblWrkshops.Exported <> 1)


You find iter_intlist_to_table on
http://www.sommarskog.se/arrays-in-sql-2000.html#iter-list-of-integers

Note: that article is for SQL 2000. If you are on SQL 2005, there is
a separate article for SQL 2000.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]