dynamic sql - trying to save value

hey there, basically trying to get the [Field ID] saved into @fieldID so i can use it at another point in the SP. tried various combinations, but i mostly get errors of 'Conversion failed when converting the varchar value ... to data type int. The [field ID] is an int and primary key. i've even tried casting the [field id] just to make sure, it still fails.

Declare @fieldID as int
Declare @sql1 as varchar(1000)
se开发者_Go百科t @sql1 = 'select '+@fieldID+' = [Field ID] from ['+@DataBaseName+'].dbo.[Custom Field Definition] where [Name] = ''Roster Y/N'' and [Table Type] = 0'
  --print @sql1
  exec(@sql1)

if i remove the " '+@fieldID+' " i get the proper value back. i know this doesn't work, but is there something like, set @fieldID = exec(@sql1) ?

thanks for the help!

dave k.

---

set @fieldID = exec(@sql1)

you need to use SP_EXECUTESQL with OUTPUT

here is an example

DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'Authors'
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount
GO

So, try this

DECLARE @DataBaseName VARCHAR(100),
@fieldID INT,
@chvSQL NVARCHAR(300)

SELECT @chvSQL = N'SELECT @fieldID =[Field ID]  from ['+@DataBaseName+'].dbo.[Custom Field Definition] where [Name] = ''Roster Y/N'' and [Table Type] = 0'

EXEC sp_executesql @chvSQL, N'@fieldID INT OUTPUT', @fieldID OUTPUT

SELECT @fieldID
GO

---

Easy answer is to convert you fieldId to a varchar

 Declare @fieldID as int
 Declare @sql1 as varchar(10)
 set @sql1 = 'select '+ Convert(varchar(100),@fieldID)
 exec(@sql1)

But as SQLMenace suggests, use SP_ExecuteSQL