Hi there,

I need to do something which sounds quite simple yet the solution is eluding me. I want to create a stored proc or function that takes a table name and column name as inputs and returns a random value from the table and column specified.

My problem right now is figuring out how to return the value from a dynamic SQL statement to a variable... as a function won't let me create a table to hold it in I'm not sure the best way to go about this. Stored proc with an output parameter?

Help much appreciated.

seis

Jeff,

I need it work like this though...

SELECT @OUTPUT_VAR = TOP 1 @COLUMN_NAME
FROM @TABLE_NAME
ORDER BY NEWID()

I make that all part of a string and tried EXEC(@SQLString) but I can't seem to make it work. Trips up on the TOP 1 for some reason when it's dynamic SQL.

It's part of a script that I'm using to help build a data dictionary, and something I'd like to gather are some example column values. I've tried sp_executeSQL already and ran into the same problems I did using an EXEC() command. I will likely end up gathering the values manually as it's just not going to be worthwhile to put too much time into trying to figure this script out!

Thanks anyways Jeff.

Thanks for your help Jeff. My needs have changed slightly - as you might expect one example value isn't going to be enough so I'm going to try to tweak this script into something outputs 3 example values from the column put into a string like so... "example1, example2, example3".

Based on the code you've provided me with I think that should be doable, though truthfully I'm still a little confused with this code:

EXEC dbo.sp_ExecuteSQL @SQL,
        N'@Output_Var VARCHAR(8000) OUT',
        @Output_Var OUT

I will play more and post should I run into more trouble. Not reading or not playing with the code enough may be true - time is sometimes a luxury I don't have on top of the rest of "life" right now.

Your help IS appreciated though - thanks!

seis