access variable outside dynamic query

Question

Viewing 5 posts - 1 through 4 (of 4 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try using a temp table instead. Table variables exist only in the scope they're defined in.

Take a look at this article[/url]: it's an excellent resource for a complete understanding of the topic.

Regards

Gianluca

-- Gianluca Sartori

Rajesh Patavardhan SSCrazy Points: 2801 More actions · Answer 2

Question, just out of curiosity, why the dynamic sql execution required in this scenario as direct sql seems to do the job?

or am I missing some thing - see below slightly modified code.

DECLARE @TMP2 TABLE (COL_NAMES VARCHAR(255))

DECLARE @TMP3 VARCHAR(5),@MySQL VARCHAR(255), @TBL_NAME VARCHAR(255)

SET @TBL_NAME = 'spt_monitor'

INSERT INTO @TMP2

SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TBL_NAME

SELECT * FROM @TMP2

Anamika SSCertifiable Points: 6202 More actions · Answer 3

Hi,

i end up with Rajesh's idea. and it works well.

i have to try Gianluca Sartori's suggestion also.

thnx guys,

regards,

viji

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Look up sp_executeSQL in books online. You can input and output variables to a dynamic query executed with the above.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll