Using procedure results as table

[Genken]

Hi i recently wrote a seach engine type website using access. The problem is now i have to convert all my queries to stored procedures; some of my vbscripting code calls certain queries as tables.

1. Can i call a procedure the same way i call a querie in access for example.
Select x,y,b from queryletters order by y .

Say i have a procedure called sp_getNames which is for example written in the follwing way

CREATE PROCEDURE dbo.sp_StaffDetails
AS SELECT
tblStaff.Staff_Code,tblStaff.Staff_Name,tblStaff.S taff_Surname,tblStaff.Business_Unit,tblStaff.Line_ Manager
FROM dasuser.tblStaff
GO

And then call this procedure from another procedure in my asp code?
For example if i have a procededure

CREATE PROCEDURE dbo.getStaffSpecific
(@ID varchar(8)
AS
select * from sp_StaffDetails where staff_code = @id
GO??
Please help

[Vidhura]

Hi i recently wrote a seach engine type website using access. The problem is now i have to convert all my queries to stored procedures; some of my vbscripting code calls certain queries as tables.

1. Can i call a procedure the same way i call a querie in access for example.
Select x,y,b from queryletters order by y .

Say i have a procedure called sp_getNames which is for example written in the follwing way

CREATE PROCEDURE dbo.sp_StaffDetails
AS SELECT
tblStaff.Staff_Code,tblStaff.Staff_Name,tblStaff.S taff_Surname,tblStaff.Business_Unit,tblStaff.Line_ Manager
FROM dasuser.tblStaff
GO

And then call this procedure from another procedure in my asp code?
For example if i have a procededure

CREATE PROCEDURE dbo.getStaffSpecific
(@ID varchar(8)
AS
select * from sp_StaffDetails where staff_code = @id
GO??
Please help

You have to use Exec dbo.sp_StaffDetails @ID

Refer the following

http://www.aspfree.com/c/a/ASP.NET-C...return-values/


You can use SQL function instead for first stored procedure

CREATE FUNCTION dbo.UDFStaffDetails
(@ID int)
-- Procedure B
RETURNS TABLE
AS
RETURN(SELECT
tblStaff.Staff_Code,tblStaff.Staff_Name,tblStaff.S taff_Surname,tblStaff.Business_Unit,tblStaff.Line_ Manager
FROM dasuser.tblStaff
)
GO

CREATE PROCEDURE dbo.getStaffSpecific
(@ID varchar(8)
AS
begin
select * from UDFStaffDetails (@ID)
end