[dba-SQLServer] Assign Select to local variable

Found the answer - it was using

sp_executesql

See http://www.sommarskog.se/dynamic_sql.html for interesting information.

David.

At 3/08/2009, you wrote:
>Not quite.  When the @qry is run @FieldName has been replaced by the
>actual field.  EG If FieldName = 'ClientAge' then @qry becomes
>
>'SELECT ClientAge FROM dbo.tblBWSClient WHERE BWSClientID = 12345'
>
>Now what I need to do is get the value of ClientAge as returned by
>this query into @OldValue
>
>David
>
>At 3/08/2009, you wrote:
> >David,
> >
> >If I understand your question correctly, after running the exec, all you
> >have to do is:
> >
> >SET @OldValue = @FieldName
> >
> >Bobby
> >
> >
> >-----Original Message-----
> >From: dba-sqlserver-bounces at databaseadvisors.com
> >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
> >Emerson
> >Sent: Sunday, August 02, 2009 5:33 PM
> >To: dba-SQLServer at databaseadvisors.com
> >Subject: [dba-SQLServer] Assign Select to local variable
> >
> >I have a table full of field names.  Having selected a field name I
> >would like to put the value of the field into a local variable.
> >
> >Here is my code so far:
> >
> >SELECT @FieldName = FieldName FROM @tblFieldList WHERE ID = @FieldListID
> >
> >SET @qry = 'SELECT ' + @FieldName + ' FROM dbo.tblBWSClient WHERE
> >BWSClientID = ' + CAST(@BaseLineRevisionID AS varchar(20))
> >EXEC @qry
> >
> >This gives me the value of the @FieldName field.  How can I store
> >this value in a variable called @OldValue?