Geek asks and answers

How to get specific value when table name, column specified in SQL procedure

declare @id int
execute getLastRaw 'subjectID','tblSubject', @id output
print @id

alter procedure getLastRaw @column char(20), @tbl Char(20),
@return int output
as
declare @dynSQL varchar(100)
select @dynSQL ='SELECT TOP 1 '+@return+'='+ @column + ' FROM ' + @tbl + ' ORDER BY ' + @column + ' DESC'
exec(@dynsQL)

I want to get the value that is selected from the select statement. But it says:

Msg 245, Level 16, State 1, Procedure getLastRaw, Line 5
Conversion failed when converting the varchar value 'SELECT TOP 1 ' to data type int.
+3
source share
1 answer

  • When joining @return, SQL Server tries to convert the whole expression to int from datatype priority

  • You cannot assign @returnurn outside or inside dynamic SQL because of scope: stored proc and dynamic SQL are different. Local variables are local to region only

You will need to use a temporary table: this is for each connection and is available for internal areas (dynamic SQL)

...
declare @dynSQL varchar(100)

select @dynSQL ='SELECT TOP 1 '+ @column + ' FROM ' + @tbl + ' ORDER BY ' + @column + ' DESC'

CREATE TABLE #result (rtn int)

INSERT #result (rtn)
exec(@dynsQL)

SELECT @return = rtn FROM #result
GO

Although it is just as simple and more correct to use the correct SELECT ...

declare @id int
SELECT @id = subjectID FROM tblSubject ORDER BY subjectID DESC
print @id
+2
source

More articles:


All Articles