Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

converting result of dynamic sql to int

Status
Not open for further replies.

kpeasley

Programmer
Apr 24, 2003
3
US
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.

Code:
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!
 
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.
 
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?
 
Please see this thread thread183-924395. They asked the same question.

Denny

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

[noevil]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top