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
using EXEC on SQL statements
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
-
Erland Sommarskog 102.6K Reputation points
2020-09-08T22:00:03.537+00:00
1 additional answer
Sort by: Most helpful
-
RoyB09 306 Reputation points
2020-09-08T22:35:05.783+00:00 Thanks Erland. I've figured a work arount, but will definitely read the article ...