Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12

    assigning values by select within a procedure


    I am a little rough with sql. I cannot remember how to assign a value that is returned from a select

    Here ithe line of code that doesn't work
    Code:
     
    Set @nextID = Select (currentNumber + 1) From IDGenerator Where tableName = @tableName
    Thanks for the help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    ok I figured that one out, but now I have a question on procedures. I want to return a value from a procedure. I don't know if I have to use a function instead ,or even if SQL has functions.

    Here is what I have
    Select @playerID = GetNextID ('PoolPlayers')

    where GetNextID returns a value
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    You can use the return value similar to visual basic to return an integer value.
    ...
    You can also use output parameters.
    Last edited by victorpendleton; January 21st, 2004 at 12:10 AM.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    Here is what I have so far, but I am getting errors like crazy. Can you not throw errors in a function, because there is a lot of errors regarding the raiserror.

    Here is what I have so far
    Code:
    Create Function GetNextID (@tableName as varchar(40) = null)
    
    Returns Integer
    As
    Begin
    	
    	Declare @nextID integer
    
    	Set @tableName = rtrim(ltrim(@tableName))
    
    	if (@tableName is null or @tableName = '')
    		raiserror('isValidStatus - You must pass in a player status',16,1)
    	
    	Begin Transaction
    		--obtain the (MAX id  +1) of the specified table
    		Select @nextID = (currentNumber + 1) From IDGenerator Where tableName = @tableName
    
    		--insert the new
    		Update IDGenerator Set currentNumber = @nextID
    
    		--catch error
    		if (@@error <> 0)
    		begin
    			Rollback Transaction
    			raiserror('GetNextID - Error upon updating the ID table',16,1)
    		end
    		else
    		Begin
    			Commit Transaction
    			return @nextID
    		End
    End
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    The value returned from the stored procedure is the last value selected. It can be a scalar like select 1 which would return the scalar value 1. Or it could return a single row or several rows of data like select col1, col2, col3 from dbo.fooBar where col4 between 10 and 20.

    MS-SQL, and some other RDBMS, do have functions. Be careful when thinking a sproc is like a function. To keep it simple... A function returns a scalar value, a single value, which can be used inline in a select statement, like a trim or substring function. While a stored procedure can either return a result set or nothing and cannot be used inline.

    Stored procedures can also use output parameters to return values.

    The two examples below should show some of the differences.

    Adam

    Function example
    Code:
    create function dbo.fGreatest(@num1 numeric, @num2 numeric)
    returns numeric
    as
    begin
        if @num1 >= @num2
            return @num1
        else
            return @num2
    end
    go
    
    select dbo.fGreatest( 3, 4 )
    go
    Example of the same as a stored procedure... note the stored procedure cannot be used inline in a select statement.
    Code:
    create procedure dbo.sprocGreatest( @num1 numeric, @num2 numeric )
    as
    begin
        if @num1 >= @num2
             select @num1
        else
             select @num2
    end
    go
    
    exec dbo.fGreatest 3, 4
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    are transactions not allowed in a function or procedure? that is where most of my errors are
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    Originally posted by iso
    Here is what I have so far, but I am getting errors like crazy. Can you not throw errors in a function, because there is a lot of errors regarding the raiserror.
    You cannot use transactions or raise errors in functions. You would be better served by using a stored procedure for this type of code. A function is more synomomous with a view, not a procedure.

    Here is a quick link about functions.
    http://www.novicksoftware.com/UDFofWeek/UDF_FAQ.htm

    Here is an article about creating sequence tables.
    http://jamesthornton.com/software/co...n/nextval.html

    Hope this helps,

    Adam
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    Changing to procedures worked well Thanks for the help, and I will take a look at those articles.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    glad I could help iso
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    ok back to this question

    from what I understand since functions only return simple datatype you can use them much like in other languages
    ex.
    @someVar = functionName(param1, etc)

    but procedures either don'r return anything or return lots (recordset)

    so
    @someVar = procedureName(param1, etc)
    is no longer possible, is that correct.

    In my IDGenerator procedure I would like to have it return the newly created value so I can do much the same as the first example, and return it directly to a variable. maybe what I am trying to do cannot be done?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    OK. You need to use the output parameter to capture the values. Are you familar with declaring an output parameter in TSQL?
    ...
    create proc pr_test
    (@empID int, @userName varchar output)
    as
    select @userName = firstName
    from employees
    where employeeID = @empID
    return

    declare @userName varchar
    execute pr_test 1, @userName
    select @userName
    ...
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    actually I am reading the last article that was posted and it talks about it, but it is new to me.

    This should work, thanks
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    If you use the technique discussed the article you would make two sql calls to insert the row. Here is a quick example.

    Code:
    create procedure sprocInsertFooBar( @firstName varchar(10), @lastName varchar(10) )
    as
    begin
        declare @nid int
        exec dbo.nextval( 'FooBar', @nid output )
        
        insert into dbo.FooBar ( id, firstName, lastName )
            values( @nid, @firstName, @lastName )
    
        select @nid
    end
    From your code, ASP or PHP you simply pass the column values and this returns the id of the newly inserted.

    Or you can call the stored procedure directly from your scripting page and re-write the insert procedure to take the new id as parameter.

    Not sure if PHP supports stored procedure output parameters, but ASP with ADO does.

    Adam
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2003
    Posts
    506
    Rep Power
    12
    definatley learned a little from that article. For some reason it never returns a number but always a nulll value.

    here is the code
    Code:
    Create Procedure GetNextID2 (@tableName as varchar(40) = null, @nextID int output)
    As
    Begin
    		--insert the new
    		Update IDGenerator
    		Set @nextID = currentNumber = currentNumber + 1
    		Where tableName = @tableName
    
    End
    Return @nextID
    -----------------------------
    declare @nextID integer
    Exec GetNextID2 'hockeyPlayers', @nextID
    select @nextID
    I can't figure this out. I checked the article and what was in the previous post, and everything seems to jive, but I keep getting the null value returned. And the currentNumber value is not null (15 I think at the moment)
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11

    Simulating Oracle Sequence In MS-SQL


    Try this.
    Code:
    Create Procedure GetNextID2 (@tableName as varchar(40) = null, @nextID int output)
    As
    Begin
    		--insert the new
    		Update IDGenerator
    		Set @nextID = currentNumber = currentNumber + 1
    		Where tableName = @tableName
    
    End
    Return @nextID
    -----------------------------
    declare @nextID integer
    Exec GetNextID2 'hockeyPlayers', @nextID output
    select @nextID
    Here is my setup.
    Code:
    CREATE TABLE dbo.SEQUENCES
    (
    	/* Simulates and Oracle sequence.
    	   This is the prefered method to generate unique integer keys
    	   compared to identity.  Identity fields cannot be updated and can
    	   cause serious issues with replication and data movement.
    	   
    	   This table will be pinned to memory when the db starts
    	   
    	   See Stored Procedures
    		dbo.CREATE_SEQUENCE
    		dbo.NEXTVAL
    	*/
    	SEQ	varchar(100)	not null	primary key,
    	SEQ_SEED	int	not null	default 1,
    	SEQ_INCREMENT	int	not null	default 1,
    	SEQ_ID	int	not null	default 1
    )
    GO
    
    CREATE Procedure dbo.CREATE_SEQUENCE
    	@sequence varchar(100),
        @seed INT,
        @increment INT
    AS
    INSERT INTO dbo.SEQUENCES ( SEQ, SEQ_SEED, SEQ_INCREMENT, SEQ_ID )
    	VALUES( @sequence, @seed, @increment, @seed - @increment )
    
    GO
    
    CREATE Procedure dbo.DROP_SEQUENCE
    	@sequence varchar(100)
    AS
    DELETE FROM dbo.SEQUENCES
    	WHERE SEQ = @sequence
    
    GO
    
    CREATE Procedure dbo.NEXTVAL
    	@sequence varchar(100),
        @sequence_id INT OUTPUT
    AS
    SET @sequence_id = null
                  
    UPDATE dbo.SEQUENCES
    SET    @sequence_id = SEQ_ID = SEQ_ID + SEQ_INCREMENT
    WHERE  SEQ = @sequence
    
    RETURN @sequence_id
    
    GO
    
    exec dbo.CREATE_SEQUENCE 'MyTest', 1, 1
    begin
    declare @nid int
    exec dbo.NEXTVAL 'MyTest', @nid output
    select @nid
    end
    Last edited by abombss; January 21st, 2004 at 02:49 AM.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo