I have three integers 7, 3, and 2.
I want to write a script that loops changing my variable each time then executing a SQL request using that changed variable for each changes variable. I am not sure how to do this.
I know how to declare a variable
declare @myvariable int;
set @myvariable = 7
select *
from mytable
where field1 = @myvariable
Please help
You can use tablevariable for this.
Method-1 using While loop
DECLARE @TableVariable TABLE (
Id INT identity(1, 1)
,Variable INT
)
DECLARE @End INT = 0
,@start INT = 1
,@myvariable INT
INSERT INTO @TableVariable
VALUES (7)
,(3)
,(2)
WHILE @Start <= @End
BEGIN
SELECT *
FROM mytable
WHERE field1 IN (
SELECT variable
FROM @TableVariable
WHERE Id = @Start
)
SET @Start = @Start + 1
END
Method-2 using Join
DECLARE @TableVariable TABLE (
Id INT identity(1, 1)
,Variable INT
)
INSERT INTO @TableVariable
VALUES (7)
,(3)
,(2)
SELECT *
FROM mytable m
INNER JOIN @TableVariable t ON m.field1 = t.Variable
Ok the problem with Method 1 was the statement Whole @start<= @end when @end is 0. I changed it to 3 and it works great. Thanks for your help.
DECLARE @TableVariable TABLE ( Id INT identity(1, 1) ,Variable INT ) DECLARE @End INT = 3 --Original example had 0 ,@start INT = 1 ,@myvariable INT
INSERT INTO @TableVariable VALUES (7) ,(3) ,(2)
WHILE @Start <= @End BEGIN SELECT top 100 * FROM provider_status PS WHERE PS.provider_status_type_id IN (
SELECT variable
FROM @TableVariable
WHERE Id = @Start
)
SET @Start = @Start + 1
I still don't know how @myvariable gets set and used.
