converting result of dynamic sql to int

[kpeasley]

I am trying to insert the result of a dynamic SQL query into a table. I need to convert the result to int but my code is trying to convert the dynamic SQL string to int (i.e. error converting 'select appid...') instead of the value returned by the query.

declare @fieldnm nvarchar(5), @value nvarchar(100)
set @fieldnm = 'd' + @dept

set @value = 'Select appid from secapp where ' + @fieldnm + ' = 1'
[b]exec(@value)[/b]

insert into secappuser( appid, uid, dateadded, enabled)
values ( [b]convert(int, @value)[/b], @uid, getdate(), 1)

I submit this to the experts. Please help!

 

[apexbs]

You could try two thing;

1- Try to use Cast here in your insert

2- Try to using another variable i.e. int and setting it to your results from dynamic sql @value variable.

e.g. @ValueInt = @value

Post your result again.

 

[kpeasley]

I've tried both of these things (cast and another variable) and I get the same error.

Syntax error converting the nvarchar value 'Select appid from appsecurity.dbo.secapp where d095 = 1' to a column of data type int.

Can I use an output variable with in a stored procedure somehow?

 

[mrdenny]

Please see this thread thread183-924395. They asked the same question.

Denny

--Anything is possible. All it takes is a little research. (Me)