September 20, 2014 at 8:30 am
I have a storedprocedure using a temp table.
(CREATE TABLE #T( ID varchar(20) COLLATE database_default ... etc)
All works fine.
The procedure is called from within a VBA procedure in Excel, to return a table of data.
I have 12 users each with their own Excel workbook calling the storedprocedure.
Q: if more than one user calls the storedprocedure at the same time will this cause problems?
TIA,
Julian
Netherlands
September 20, 2014 at 9:11 am
No problem there, temp tables are isolated by sessions, as long as they are not global (start with a double hash ##).
😎
September 20, 2014 at 9:18 am
Great,
Thank you!
J.
PS: why do I need
SET NOCOUNT ON
in this Procedure? Ommitting it from other sprocs didn't cause any problems. Only difference is that they do not use temp tables....
CREATE PROCEDURE spGegevensDashboard @IDHUIS VARCHAR(4), @JAAR INT, @WEEKBEGIN INT, @WEEKEND INT
AS
BEGIN
SET NOCOUNT ON; -- NB: DO NOT FORGET THIS, OTHERWISE VBA GETRECORDSET WILL NOT RETURN RECORDS, AND GIVE AN ERROR
--ID,IDHuis,IDAfd,Jaar,Week,Inzet,Spiegel,Werk,HrsBR,WvsI,Formatie,ZgVsSpLo,ZgVsSpHi,GeenZg,AantalClienten,HV2NormExceed,
--HrsPNIL,HrsFLEX,VAST,AFW,Sta37,ISta
DECLARE @STA FLOAT
SET @STA = 0.37
CREATE TABLE #T( ID varchar(20) COLLATE database_default
, IDHuis varchar(2) COLLATE database_default
, IDAfd varchar(4) COLLATE database_default
, Jaar int, Week int
September 20, 2014 at 11:58 am
Setting the nocount on eliminates network jitter such as count of affected rows, generally a good practice.
😎
September 20, 2014 at 12:00 pm
Thank you.
Cheers,
Julian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply