Posted by Cindy on 06/12/07 01:41

As it turns out, the SelectType, the SelectDate, and the Top XX are
all variables - I posted a shortened version in the 'solution'... I
think the Top XX is the biggest one that makes me have to go for
dynamic SQL.

This isn't going to be run that often (a couple times a month), and
there aren't going to be that many records, so I'm hoping all will be
okay with it as is. Thanks though - and many thanks for the article
link. I started this out originally with parameters, and with the two
small examples in BOL I couldn't get it to work - it sounds like your
article was just what I needed, and will come in handy down the road.

Cindy


On Jun 11, 5:35 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Cindy (ckspot-t...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]