Click here to Skip to main content
14,635,966 members
Rate this:
Please Sign up or sign in to vote.
See more:
This is a snipbit of the entire project. What I'm trying to do is select @Begin execute @Begin and then put those results of the While loop into the #While_Loop temptable but I keep coming up with nothing being put into the Temp or just the string that @begin getting put into the Temp. Essentially I just need help with the Syntax of the 'Insert into' section

Declare @TTWhile varchar(30)
If @ProgID <> 0 Select @TTwhile = '#temp_prog_course_list_GR'
If @ConcID <> 0 Select @TTWhile = '#temp_prog_course_list_UG'

declare @Begin nvarchar(max)

Select @Begin = '
declare @ctr int, @max int, @Coursecode varchar(25), @Coursecode2 varchar(50), @courseCodeList varchar(max), @CourseCodeList2 varchar(max)
set @ctr = 1

Select @max = max(RecordID) From ' + @TTWhile + '

While @ctr <= @max

Select @CourseCode = ltrim(rtrim(courseCode))
From ' + @TTwhile + ' where RecordID = @ctr

Select @courseCodeList = coalesce(@courseCodeList + '','' + @CourseCode, @CourseCode)

Select @CourseCode2 = '' max(''+ltrim(rtrim(courseCode))+'') as [''+ltrim(rtrim(courseCode))+'']''
From '+ @TTwhile + ' where RecordID = @ctr

Select @courseCodeList2 = coalesce(@courseCodeList2 + '','' + @courseCode2, @courseCode2)

Select @ctr = @ctr + 1

exec (@begin)

Create Table #While_Loop
(cc1 nvarchar(max),
cc2 nvarchar(max))

Insert into #While_Loop --select (cc1, cc2)
From @begin

Declare @cc1 varchar(max), @cc2 varchar(max)
select @cc1 = (Select cc1 From #While_Loop)
select @cc2 = (Select cc2 From #While_Loop)

What I have tried:

select * into

Insert into ... exec

Updated 10-Aug-18 12:03pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

As much as I dislike just throwing code out there.
Insert into #While_Loop (@courseCodeList, @courseCodeList2)

You execute the varchar @begin as a sql script, so the variables you create inside it exist in the environment you ran it from. They are availible on their own after it is run, not as part of some micro-environment referenced by the varchar's name.

You should probably refactor that whole @begin business into a stored procedure that return the 2 variables you are interested in as output parameters.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100