using EXEC on SQL statements

Question

qryS
Select avg(len(Question)) FROM dbo.Student_Question
Select avg(len(Answer)) FROM dbo.Student_Question
Select avg(len(Learner_Id)) FROM dbo.Student_Question

I’ve got a table with the following column (it also has other columns). I want to select all the columns, but execute the queries in Column qryS. I’ve mostly achieved it in the Cursor below, but am having issues putting the EXEC ( @strqry) result into a variable

Much appreciation if someone could point out where I’m going wrong.

DECLARE c1 Cursor
FOR

select
d.SchemaName, d.Table_Name, d.Column_Name, d.Column_Position, d.DataType, d.max_length, d.qryS
from dbo.Test2020 d
Open c1

DECLARE @strSchema VARCHAR(50);
DECLARE @strTable VARCHAR(50);
DECLARE @strColumn VARCHAR(50);
DECLARE @ColPos int;
DECLARE @strDataType VARCHAR(50);
DECLARE @MaxLen int;
DECLARE @strqry VARCHAR(MAX);

Fetch NEXT FROM c1 INTO @strSchema
,@strTable
,@strColumn
,@ColPos
,@strDataType
,@MaxLen
,@strqry

While (@@Fetch _STATUS <> -1)
BEGIN
IF (@@Fetch _STATUS <> -2)

Declare @x int
Set @x=EXEC ( @strqry )

--EXEC @x=YourStoredProcedure 1,null, @OutputParameter OUTPUT

insert into dbo.Test2020_a (SchemaName, Table_Name, Column_Name, Column_Position, DataType, max_length, qryResult )
values( @strTable ,@strColumn ,@ColPos ,@strDataType ,@MaxLen , @x )

FETCH NEXT FROM c1 INTO @strSchema,@strTable ,@strColumn ,@ColPos ,@strDataType ,@MaxLen ,@strqry
END
CLOSE c1
DEALLOCATE c1

GO

Thanks in advance (it’s been a long day)
Roy

Answer 1

You don't use EXEC, you use sp_executesql. Start reading my article on dynamic SQL, and you will soon find the answer:
http://www.sommarskog.se/dynamic_sql.html

Answer 2

Thanks Erland. I've figured a work arount, but will definitely read the article ...