Using SQL and Stored Procedures

[liddlem]

Hi All
I am trying to get my head around using stored procedures in SQL Server
On my form, I have a button that SHOULD add students from a specified formid(which is a combobox)

The following is the underlying code.

Private Sub Btn_Add_Studs_By_Form_Click()
Dim qdef As DAO.QueryDef
Dim err_msg As String
Dim err As DAO.Error
On Error GoTo err_lbl

'In the front end, I have a linked Pass Though Query called "PTQ" which is modified as follows. 

Set qdef = CurrentDb.QueryDefs("PTQ")
qdef.SQL = "EXEC SPI_ACT_ACTIVITYDTL " & Me.ID_ACT_ActivityHdr & ", '" & Trim(Me.Add_Form) & "'"

'Although the line above returns what seems to be the correct call with correct parameter info, 
'the following line returns a 3065 error (Cannot execute select query)
qdef.Execute dbFailOnError
end sub

My 'SPI_ACT_ACTIVITYDTL' Stored Procedure on the sql server is defined as :

USE [Demo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SPI_ACT_ACTIVITYDTL] @ID_ACT_ActivityHdr integer, @FMName nvarchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO dbo.ACT_ActivityDtl ( ID_Student, FmName, ID_ACT_ActivityHdr, ID_staff, ID_form )
SELECT ID_Student, FORM_Name, @ID_ACT_ActivityHdr, 0 AS Staff, ID_FORMID
FROM dbo.STU_Student
WHERE (((dbo.STU_Student.FORM_Name)=@FMName));
END
GO

As you can see, the SP is trying to INSERT data that is sourced from a SELECT query.
If I can only make calls to an ACTON query that does not contain a SELECT statement in it, then how can i make the SP work and then as a consequence of not being able to put SELECTS in my SP's, is there any point to using an sp?

Thanks

 

[Ranman256]

if you have to do large mass updates, then the SP works for you.
I dont think theres any gain on small sets

 

[pbaldy]

You might check the Returns Records property of the pass through query.

 

[liddlem]

Hi Pbaldy - Thanks for the suggestion, but I don't know how to return the records for an SP. can you point me in the right direction please.

 

[pbaldy]

I'm talking about a property of the saved query "PTQ".

 

[Galaxiom]

Does the procedure work when you run it on the server?

 

[Mark_]

Possible issue, is FMName unique within the database? Not sure where the error is coming form elsewhere, but if there is an ambiguous reference for which record your trying to select that can cause other issues.

 

[liddlem]

Hi All - Thanks for the input.
Let me try answer each of your comments in order.

Ranman256 : For me, this exercise is about 2 things.
1. Learning how to use SP's (and functions) on a SQL server. (I presume that if/when I convert my front end to a web platform, that I will need this skill?)
2. Reduce network traffic where possible. (see my comments to Pat Harmon below).

JUST A THOUGHT HERE Could / should I be using a UDF instead? Can I perform an action query inside a UDF?


PBaldy : If I edit the 'PTQ', it has changed to read ... EXEC SPI_ACT_ACTIVITYDTL 396, 'MS78A'
Which is what I expected.

Galaxiom : If I 'simulate' the SP (IE run the following in a NEW QUERY on the SQL server) it works.

declare @ID_ACT_ActivityHdr int
declare @FMName varchar(15)
set @FMName = 'MS9A'
set @ID_ACT_AtivityHdr = 326
SELECT ID_Student, FORM_Name, @ID_ACT_ActivityHdr, 0 AS Staff, ID_FORMID
FROM dbo.STU_Student
WHERE (((dbo.STU_Student.FORM_Name)=@FMName));

Mark_ : I have tried changing the name of the variable to a sequence of random letter (so no duplicate name)...makes no difference.

Pat Harmon : Okaaay?...My understanding is that SP's perform some action on the server side, whereas a [front end] query 'downloads' ALL the underlying data from the server before performing some action.
The output of which is then returned back to the server and then updated accordingly.
Surely this implies that there is a lot of unnecessary traffic on the network?

 

[Mark_]

If your code works when you give it an explicit FMName, where is your stored procedure getting this value from? Same with ID_ACT_ActivityHdr.

Regarding bandwidth, are you looking to add a student, several students, a dozen students, or tens of thousands of students with a single press? For the number of students normally found in a class you'd see no difference in server or bandwidth utilization if you did this from the front end.

Stored procedures are normally used to accomplish that which cannot be easily done from the front end. having the server update half a million records is one use. Having the server sift through several million to return a subset of 12 is a perfect use.

 

[Galaxiom]

Pat Harmon : Okaaay?...My understanding is that SP's perform some action on the server side, whereas a [front end] query 'downloads' ALL the underlying data from the server before performing some action.

That is a common misunderstanding. If the query can be translated via the ODBC interface it will run on the server.

Including local tables and user defined functions are the most common reasons why the records would need to be brought down.

If the UDF return value is required to select the records on the server then the overheads become hideous because all the records need to be processed on the front end.

 

[pbaldy]

Liddell: you still haven't addressed my question. Your pass through query has properties, one of which is Returns Records. Yours should be No, since it will not return records. I suspect it may be Yes, leading Access to believe it's a SELECT query and give you that error.