Print selected values

Question

If there are several tables, I would like to find out the value of col1 when col2 is null from these tables, and print the values through a stored procedure.

col1	col2
a	A
b	null
b	B
c	C
a	null

Expected ouput: a,b

--what I did but can not work

create proc sp_getValue
@tableName varchar(30)
as
declare @result nvarchar(max)
select distinct col1=@result　from @tableName
print @result

Thank you for your help.

You want `a` and `b` just the way you printed or as a table? Anyway, I suggest you use `exec`. — Amir M, Jul 20 '21 at 09:47
I want to print them. I had tried to wirte the select statement as string and exec the string but can not work. — varen, Jul 20 '21 at 09:51
To find the rows, you need to use `WHERE col2 IS NULL`. However, the construct `from @tableName` is never going to work: the FROM clause does not accept @parameter values, the table name needs to be written explicitly in the query because the compiler checks if the table exists + all columns that you use from it, before any parameter values are evaluated. See [here](https://stackoverflow.com/q/2838490/1220550) for how to work around that. — Peter B, Jul 20 '21 at 09:52
Having multiple tables with the same schema is normally a design flaw — Charlieface, Jul 20 '21 at 11:03
Rarely is the use of PRINT in a stored procedure a useful technique to provide information to an application for consumption. It is acceptable for debugging or learning only IMO. — SMor, Jul 20 '21 at 12:07

Amir M · Accepted Answer · 2021-07-20T10:18:03.150

If you want to get the table name as an argument, you should use exec.

create proc sp_getValue
 @tableName varchar(30)
as
  exec('select distinct col1 from '+ quotename(@tableName) + ' where col2 is null');

To turn this table into a string you can output the result into some temp table, and traverse that using a cursor. Something like this:

DECLARE @value  NVARCHAR(30)
DECLARE @result NVARCHAR(100) = '';
CREATE TABLE #temp
(
    col1 NVARCHAR(30)
)

EXEC ('insert into #temp select col1 from ' + @tableName +' where col2 is null')

DECLARE myCur CURSOR FOR SELECT *
                         FROM #temp;
open myCur;
FETCH next from myCur  INTO @value;

WHILE @@FETCH_STATUS = 0
    BEGIN
        set @result = @result + @value + ',';
        FETCH next from myCur INTO @value;
    END

PRINT @result

close myCur;
DEALLOCATE myCur;

Thank you. If I want to print the output values,then transfer the values to string, and each value has a comma following, like this:`a,b`, what can I do? — varen, Jul 20 '21 at 10:00
I suggest running them through a cursor. I'll edit the answer. — Amir M, Jul 20 '21 at 10:01

Print selected values

1 Answers1