CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
ARZ
ARZ

Reputation: 2491

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

Upvotes: 0

Views: 1710

Answers (3)

Keith
Keith

Reputation: 21244

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.

Upvotes: 0

ARZ
ARZ

Reputation: 2491

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

Upvotes: 1

user751757
user751757

Reputation: 1

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

Upvotes: 0

Related Questions