Posted by Erland Sommarskog on 06/11/07 21:35

Cindy (ckspot-temp@yahoo.com) writes:
> So sorry - something about typing up the request helped me think of a
> different solution -
>
> I changed the SQL to
> SELECT @SQLString = N'UPDATE FD__UR_Randoms SET RecordChosen = ''' +
> 'T' + ''''
> SELECT @SQLString = @SQLString + N'WHERE SelectDate = ''' + @TodaySt
> + '''' + ' AND SelectType = 1 AND Admit_DOCID IN '
> SELECT @SQLString = @SQLString + N' (SELECT TOP 12 Admit_DOCID FROM
> FD__UR_Randoms ORDER BY NEWID())'

Don't interpolate the values into the query string, but use parameters
instead. This saves you from being entangled in a mess of quotes, and
saves you from a lot of other problems as well.

See here for details:
http://www.sommarskog.se/dynamic_sql.html#sp_executesql

....and if you are on SQL 2005, you can use SELECT TOP(@var) in which
cases there is no need for dynamic SQL at all, as far as I can see.


--
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]