Use Stored procedure like a function

I need to deal with the table name as a variable.Then I must using dynamic sql and therefore I must using Stored procedure. But the problem that how can I use the stored procedure like a custom sql function.

e.g: select col1,(Exec sp1 param1,'tbName') from table1

Finally,I changed my design and and use dynamic SQL in one upper level.

This will be posible in sql server denali that introduces the new keywords "WITH RESULTSET".

The alternative on current sql versions is passing a temp-table to the stored procedure

Stored procedures can return scalar values through output parameters. Here's an example (from here).

Create the stored procedure like this:

CREATE PROCEDURE _4P_test 
    @intInput INT,
    @intOutput INT OUTPUT
AS
    SET @intOutput = @intInput + 1 

Call it like this:

DECLARE @intResult INT

EXEC _4P_test 3, @intResult OUT

SELECT @intResult

However you should try to design your system so that you don't have to use dynamic SQL in the way you described.