You cannot do this directly. There isn't a capability for T-SQL to do something like "select x from @mytable".
If you want something like:
declare @Product2Count int
exec @Product2Count = CheckTableCountSP @Table = 'SalesOrderDetail', @column = 'ProductID', @val = 2
You could do this:
CREATE OR ALTER PROCEDURE CheckTableCountSP
DECLARE @i INT,
SELECT @cmd1 = N'select count(*) from ' + @Table + N' where ' + @column + N' = ' + CAST(@val AS NVARCHAR(10));
SELECT @cmd = CAST(@cmd1 AS NVARCHAR(4000));
EXEC sp_executesql @cmd, N'@i int output', @i OUTPUT;
We are creating a dynamic string here, which I try to avoid, but this works. The key here, is are you looking for specific types of checks that make sense in a proc? Also, how often does this run?
The reason is that this dynamic string needs to be compiled each time, which can be expensive relative to the cost of running the query. In many cases, I might write a proc generator that would generate a series of procs to do this for specific tables/columns, as I doubt you need this for every table/column combination. That way if requirements changed, I could also the generator rather than each proc.
This also has the potential of SQL injection when this proc is used, as I'm not sanitizing the params. You could add some checks, strip out things like semicolons, but there is a danger someone will use this in an unintended way if you are not careful.