Calling a SP in a Function...?

Can I call a Stored Procedure in a function?

I created a SP:

CREATE PROCEDURE dbo.test_sp AS
BEGIN
select * from STOCK

END
GO

and I tried to create a function like that:

CREATE FUNCTION dbo.test_fn ()
RETURNS int AS
BEGIN
declare @.a int
select @.a = sto_id from dbo.test_sp
return @.a
END

as seen the function uses the test_sp.but that gives syntax error. How can I do this?

thanks.

Short answer is - No, you cannot do it like that.

Is there a real problem behind the example, or is just an academic question?
If it's real, can you explain what you're trying to do?

/Kenneth

|||

There's a real problem.Let me explain it:

I'm writing a program for WinCE 5.0.I'm using RDA method to access SQL Server 2000. I need to insert records to a table which is in the server side. I tried to do that using a function but sql server complained abut that. As I learned soon, INSERT is not allowed in sql functions.Now, I want to do that.

TableA < SP <-Func<-My Program

Namely, I will pass the parameters to the sql function and it will pass the parameters to the SP and SP will write to the table.

I wish I could explain the problem..

|||

Why do you need the function?

Assuming the procedure can write to the table (ie it contains an INSERT statement), why not just do:

Your program (sends parameters/executes) => SP (contains code that inserts data into tableA)

In general, you don't mix functions and procedures 'at the same time' so to speak. They are designed for different purposes, so generally you want to use one or the other.

/Kenneth