sp_ExecuteSQL

Question

Post reply

sp_ExecuteSQL

Francis Apel

SSC Eights!

Points: 918
More actions
July 13, 2004 at 3:16 pm

#108562

Is it possible to get a value returned (like an output variable) from a call to sp_executeSQL?
If so, how?
I would think it would be something like this:
EXEC sp_Executesql @SQLText, @parmlist,
@Item1,
@Itemout OUTPUT
Although specifying OUTPUT here does not return the value. This doesn't work

TIA!!
Francis
Francis
-----------------
SQLRanger.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 1

hey francis,

you would need to do something like the example below:

-- [BEGIN] : Code Snippet

DECLARE @SQLText AS NVARCHAR(500)

SET @SQLText = N'SELECT @Itemout=some_field FROM Some_Table WHERE some_other_field = @Item1'

DECLARE @Item1 AS VARCHAR(20), @Itemout AS VARCHAR(20), @parmlist AS NVARCHAR(100)

SET @parmlist = N'@Item1 AS VARCHAR(20), @Itemout AS VARCHAR(20) OUTPUT'

EXEC sp_executesql @SQLText, @parmlist, @Item1, @Itemout OUTPUT

-- [END] : Code Snippet

hope this helps

Hamish McCreight-154771 Valued Member Points: 51 More actions · Answer 2

Hamish McCreight-154771

Valued Member

Points: 51

July 14, 2004 at 3:22 am

#514520

Using sp_ExecuteSQl isn't a good idea....

.....HMC

Francis Apel SSC Eights! Points: 918 More actions · Answer 3

Got it. Thanks!

Francis
-----------------
SQLRanger.com

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 4

if you are referring about the return value of a user-defined function or the value of an output parameter of a stored procedure, then the answer is yes ...

for a user-defined function,

SET @SQLText = N'SELECT @Itemout = fn_somefunction()'

for a stored procedure,

SET @SQLText = N'EXEC usp_someproc @Itemout'

dennis melbourn Grasshopper Points: 15 More actions · Answer 5

DECLARE @SQLString NVARCHAR(500)

DECLARE @ParmDefinition NVARCHAR(500)

DECLARE @Balance varchar(30)

SET @SQLString = N'SELECT @balOUT = balance from UserAccount where accId = 2 and userId = 1001'

SET @ParmDefinition = N'@balOUT varchar(30) OUTPUT'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @balOUT=@balance OUTPUT

print '--balance on next line--'

print @Balance

Francis Apel SSC Eights! Points: 918 More actions · Answer 6

Yep, thats it. JourneymanProgrammer had it too, but it didn't sink in until I saw another example

thanks!

Francis
-----------------
SQLRanger.com