February 7, 2009 at 11:18 pm
I have been killing myself over this code, any help would be greatly appreciated. first i created a udf to handle it, then discovered i cannot modify any permament tables in a UDF. so i moved it to a stored proc. first time writing a stored proc or a udf. i am running SQL Server 2008 Developer.
I call exec usp_getUsers @user_id=410720537 and it returns the expected records. however a query of visits afterwards reveals no changes have been made to visits.
If anybody would be able to tell me where i am screwing up, that would be great. i would prefer not to loop the insert, some resultsets have 500 records
visits structure
mem1 bigint, not null
mem2 bigint, not null
dStamp datetime, not null default value (GETDATE())
ipAddress varChar(16) null
id pk
alter PROCEDURE usp_getUsers
(
@user_id integer
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Declare the return variable here
DECLARE @myCount Integer
DECLARE @myTable TABLE (friendID bigint,mem_id bigint)
-- Add the T-SQL statements to compute the return value here
SELECT TOP 1 @myCount = mycount FROM db_accessadmin.planLimits WHERE friendID = @user_id
-- Return the result of the function
INSERT INTO @myTable
-- Broke up the SELECT STATEMENT for posting purposes
SELECT TOP (@myCount) friendID, mem_id from [db_accessadmin].[planLimits]
WHERE myCount < rLimit AND NOT EXISTS
(SELECT mem1,mem2 from visits WHERE mem1 = @user_id AND mem2 = mem_id) ORDER BY rLimit
-- This insert is not firing correctly
Insert into [dbo].[visits] (mem1,mem2) (select friendID, mem_id from @myTable)
END
SELECT * from @myTable
GO
February 8, 2009 at 1:49 am
ok, i fixed it, i was inserting the wrong value. the code was fine, the operator was all jacked up
February 18, 2009 at 1:20 am
sql (2/8/2009)
ok, i fixed it, i was inserting the wrong value. the code was fine, the operator was all jacked up
hehehe you are here sql:
BEGIN problem
....
....
....
COMMIT solution! - sql's position
:hehe::hehe::hehe: :w00t::w00t::w00t: :hehe::hehe::hehe:
You committed the thread, well done!!!
😛
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply