Is there any way to set a variable off of dynamic sql? 1

[VVVA]

Is there any way I can do something like this without dropping out to a UDF?

declare @CMD varchar(4000)
declare @Field1 varchar (20)
declare @Result varchar (40)

set @Field1 = 'Name'

set @CMD = 'select top 1 '+@Field1+' from MyTable'
print @CMD
set @Result = (exec (@CMD))

 

[SQLDenis]

That is very easy with sp_executesql

DECLARE @Field1 VARCHAR(100),
@Result varchar(20),
@chvSQL NVARCHAR(100)

set @Field1 = 'au_lname'
SELECT @chvSQL = N'SELECT top 1 @Result =' + @Field1 + ' from Authors'

EXEC sp_executesql @chvSQL, N'@Result varchar(20) OUTPUT', @Result OUTPUT

SELECT @Result
GO

so in your case

DECLARE @Field1 VARCHAR(100),
@Result varchar(20),
@chvSQL NVARCHAR(100)

set @Field1 = 'Name'
SELECT @chvSQL = N'SELECT top 1 @Result =' + @Field1 + ' from MyTable'

EXEC sp_executesql @chvSQL, N'@Result varchar(20) OUTPUT', @Result OUTPUT

SELECT @Result
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

btw the first example use the pubs database
here it is again for anyone who wants to try it

USE PUBS
GO


DECLARE @Field1 VARCHAR(100),
@Result varchar(20),
@chvSQL NVARCHAR(100)

set @Field1 = 'au_lname'
SELECT @chvSQL = N'SELECT top 1 @Result =' + @Field1 + ' from Authors'

EXEC sp_executesql @chvSQL, N'@Result varchar(20) OUTPUT', @Result OUTPUT

SELECT @Result
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[VVVA]

Sweet! Thanks!