Problem with Stored Procedure

  • 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

  • ok, i fixed it, i was inserting the wrong value. the code was fine, the operator was all jacked up

  • 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!!!

    😛

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply