February 18, 2011 at 4:57 am
Hi All
I’m trying to write a query that would loop through ten variables and print the values of these variables.
Here are the variables set up.
declare @a1 as char(1),
@a2 as char(1),
@a3 as char(1),
@a4 as char(1),
@a5 as char(1),
@a6 as char(1),
@a7 as char(1),
@a8 as char(1),
@a9 as char(1),
@a10 as char(1)
set @a1 = 'X'
set @a2 = NULL
set @a3 = 'X'
set @a4 = 'X'
set @a5 = 'X'
set @a6 = NULL
set @a7 = 'X'
set @a8 = NULL
set @a9 = 'X'
set @a10 = 'X'
And here is what I want to do with the variables:
IF @a1 IS NOT NULL PRINT '@a1 = ' + @a1 + ' IS NOT NULL'
IF @a2 IS NOT NULL PRINT '@a2 = ' + @a2 + ' IS NOT NULL'
IF @a3 IS NOT NULL PRINT '@a3 = ' + @a3 + ' IS NOT NULL'
IF @a4 IS NOT NULL PRINT '@a4 = ' + @a4 + ' IS NOT NULL'
IF @a5 IS NOT NULL PRINT '@a5 = ' + @a5 + ' IS NOT NULL'
IF @a6 IS NOT NULL PRINT '@a6 = ' + @a6 + ' IS NOT NULL'
IF @a7 IS NOT NULL PRINT '@a7 = ' + @a7 + ' IS NOT NULL'
IF @a8 IS NOT NULL PRINT '@a8 = ' + @a8 + ' IS NOT NULL'
IF @a9 IS NOT NULL PRINT '@a9 = ' + @a9 + ' IS NOT NULL'
IF @a10 IS NOT NULL PRINT '@a10 = ' + @a10 + ' IS NOT NULL'
Output
@a1 = X IS NOT NULL
@a3 = X IS NOT NULL
@a4 = X IS NOT NULL
@a5 = X IS NOT NULL
@a7 = X IS NOT NULL
@a9 = X IS NOT NULL
@a10 = X IS NOT NULL
The above code is the long way to check each of the variables but I would like to do this in a loop.
something like the following:
DECLARE @Cnt int
SET @Cnt = 1
WHILE @Cnt < = 10
BEGIN
IF @a + @Cnt IS NOT NULL
BEGIN
PRINT '@A' + @Cnt + ' = ' + @a + @Cnt + ' IS NOT NULL'
END
SET @Cnt = @Cnt + 1
END
Now I know the above code doesn’t work as T-SQL does not like macro substitution.
So I’ve been trying to use the sp_executesql stored procedure but just don’t seem to be able to get it to work.
Any ideas anyone??
Many thanks
Nick.
February 20, 2011 at 9:22 am
Try this:
declare @a1 as char(1),
@a2 as char(1),
@a3 as char(1),
@a4 as char(1),
@a5 as char(1),
@a6 as char(1),
@a7 as char(1),
@a8 as char(1),
@a9 as char(1),
@a10 as char(1)
set @a1 = 'X'
set @a2 = NULL
set @a3 = 'X'
set @a4 = 'X'
set @a5 = 'X'
set @a6 = NULL
set @a7 = 'X'
set @a8 = NULL
set @a9 = 'X'
set @a10 = 'X'
CREATE TABLE #Table (Name VARCHAR(5), Value CHAR(1))
INSERT INTO #TABLE VALUES('@a1',@A1)
INSERT INTO #TABLE VALUES('@a2',@A2)
INSERT INTO #TABLE VALUES('@a3',@A3)
INSERT INTO #TABLE VALUES('@a4',@A4)
INSERT INTO #TABLE VALUES('@a5',@A5)
INSERT INTO #TABLE VALUES('@a6',@A6)
INSERT INTO #TABLE VALUES('@a7',@A7)
INSERT INTO #TABLE VALUES('@a8',@A8)
INSERT INTO #TABLE VALUES('@a9',@A9)
INSERT INTO #TABLE VALUES('@a10',@A10)
SELECT Name + ' = ' + Value + ' IS NOT NULL' FROM #TABLE WHERE Value IS NOT NULL
February 21, 2011 at 7:38 am
There is no direct way that I know of to do what you wish.
You could try creating a MASSIVELY complicated pile of dynamic sql to do it. Or you can put them into a table as someone else suggested.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply