Results 1 to 6 of 6
  1. #1
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65

    Using Parameters To Create Sequence Generator In SQL Server


    Hi Guys
    Is it possible to create a stored procedure in SQL Server that creates a Sequence and uses parameters in the create statement? I would like to be able to pass the counter name start value increment value and max values as parameters. Any assistance you can provide is appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It can be done in access vb to cycle thru creating records.
    But SQL may have a Stored proc. and event to do it too.

  3. #3
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    Thanks for the reply. Yes I understand that a Stored Procedure would be necessary using Server, but when I try substituting parameters in place of various parts of the create statement Server says no go. I suspect it has to do with a DDL limitation. I was hoping there was a way around it using dynamic SQL but I haven't been able to find an example online.

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    example of using dyna

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    sorry, example dynamic sql:
    Code:
     
    declare @StartValue int= 1
    declare  @NewValue int 
    declare @SQL nvarchar(max)
    declare @par nvarchar(max)set @SQL =N'select @NewValOUT = '+cast(@StartValue asnvarchar(max))+' + 1';  
    set @par =N'@NewValOut int OUTPUT ';  
    execute sp_executesql@SQL, @par, @NewValOUT  = @NewValue OUTPUT;
    select @NewValue;
     
    
    
    
    
    
    
    
    
    
    
    

  6. #6
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65
    Thanks for the reply. I've shelved my project for the time being until I figure out a better approach to debugging my stored procedures

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-31-2016, 07:59 AM
  2. how to create a text sequence generator in ms access
    By Alexander in forum Programming
    Replies: 16
    Last Post: 02-11-2015, 06:56 AM
  3. Replies: 1
    Last Post: 04-21-2014, 02:28 PM
  4. Replies: 4
    Last Post: 04-09-2013, 03:54 PM
  5. Replies: 2
    Last Post: 06-06-2012, 09:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums