[dba-SQLServer] SP read only

If I execute the following SP it is also read-only.  This is just a select
*.  This doesn't bode well for updating a select sp

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spPeople]
	-- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from tblPeople
END 


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Martin Reid
Sent: Thursday, January 26, 2006 4:18 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SP read only

John
 
Remove the OrderBY and try it
 
Martin
 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974477
 

________________________________

From: dba-sqlserver-bounces at databaseadvisors.com on behalf of John Colby
Sent: Thu 26/01/2006 20:59
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SP read only



Folks,

I have the following SP.  It selects data from a single table, filtered on
two fields.  Pretty simple.  It is also read-only inside the SMS Express
tool.  The table itself is editable, i.e. if I just open the table I can
modify values.  Does anyone know if parameterized SPs are supposed to be
editable? 
If so, what can cause the SP result set to be Read Only?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE usp_FrmCallListFiltered
        -- Add the parameters for the stored procedure here
        @STID int,
        @DIID int
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        SELECT tblPeople.PE_ID, tblPeople.PE_IDCRF, tblPeople.PE_Specialty,
tblPeople.PE_LName, tblPeople.PE_FName, tblPeople.PE_MName,
tblPeople.PE_Addr1, tblPeople.PE_Addr2, tblPeople.PE_City,
tblPeople.PE_Zipcode1, tblPeople.PE_Zipcode2, tblPeople.PE_IDST,
tblPeople.PE_PhoneH, tblPeople.PE_PhoneW, tblPeople.PE_Currempl,
tblPeople.PE_Contact1, tblPeople.PE_Licotherstates, tblPeople.PE_RateInfo,
tblPeople.PE_Worktime, tblPeople.PE_Email, tblPeople.PE_BusAddress,
tblPeople.PE_Certification, tblPeople.PE_LIC_Licno,
tblPeople.PE_DateStampName, tblPeople.PE_DateStampAddress,
tblPeople.PE_DateStampPhoneH, tblPeople.PE_PhoneCell, tblPeople.PE_IDDNC,
tblPeople.PE_IDIWH, tblPeople.PE_MBIF, tblPeople.PE_MBIFFollowUpDate,
tblPeople.PE_HotLead, tblPeople.PE_HotLeadNotes, tblPeople.PE_Inactive,
tblPeople.PE_NoHomePhoneInfo, tblPeople.PE_FOE, tblPeople.PE_LIC_IDST,
tblPeople.PE_LIC_IDDI
        FROM tblPeople
        WHERE (((tblPeople.PE_LIC_IDST)=@STID) AND
((tblPeople.PE_LIC_IDDI)=@DIID))
        ORDER BY tblPeople.PE_LName, tblPeople.PE_FName;

END
GO

John W. Colby
www.ColbyConsulting.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com