Databases
StackOverflow2013
Postsdbo
POHow to create a Stored Procedure to count licences recursively?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to create a Stored Procedure to count licences recursively?
    primarykey
    data
    text
    <p>I have only a very little experience with SQL and now i'm stuck.</p> <p>So what i want is the following. We make installation packages, to install applications preconfigured. These packages will create a registry entry with the application's name. I read this registry entries and then i'm inserting it into an SQL database.</p> <p>My Licences table has the following columns.<br> - PackageName<br> - DisplayName<br> - Bought<br> - Free<br> - SubstituteLicense<br> - Custom </p> <p>The problem is with this SubstitueLicense. So i want to make a stored procedure which should work in the following way. If a record's substitutelicense has a value and Free is 0, then the substitulicense's Free value should be decremented. I made a stored procedure which works fine if substitulicense has no value, but i don't know how to do the rest.</p> <pre><code>USE [HWSW_Inventory] GO /****** Object: StoredProcedure [dbo].[InsertWrapAppAndDecrementLicence] Script Date: 12/30/2011 22:45:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertWrapAppAndDecrementLicence] -- Add the parameters for the stored procedure here @String varchar( 8000 ), @HostName varchar( 50 ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @LineIdx int declare @PackageIdx int declare @Line varchar( 8000 ) declare @InsertValues varchar( 4000 ) declare @PackageName varchar( 200 ) declare @Command varchar( 8000 ) select @LineIdx = 1 if ( ( len( @String ) &lt; 1 ) or ( @String is null ) ) return while ( @LineIdx != 0 ) begin --set LineIdx to the first occurence of a line separator set @LineIdx = charindex( ';', @String ) if ( @LineIdx != 0 ) set @Line = left( @String, @LineIdx - 1 ) else set @Line = @String --replace value delimeters and concatenate the hostname so we have a string what we can insert into the database set @InsertValues = '''' + REPLACE( @Line, '|', ''', ''') + ''', ''' + @HostName + '''' --get the first occurence of a ',' from the string what we want to insert, because it is the PackageName set @PackageIdx = CHARINDEX( '|', @String ) if ( @PackageIdx != 0 ) set @PackageName = left( @String, @PackageIdx - 1 ) else set @PackageName = @String set @Command = 'insert into dbo.WrapperApplications values ( ' + @InsertValues + ' )' exec ( @Command ) update Licences set Free = Free - 1 where ( Licences.PackageName = @PackageName ) set @String = right( @String, len( @String ) - @LineIdx ) if ( len( @String ) = 0 ) break end--while END--main </code></pre> <p>Could someone help me to solve this?</p> <p>Thanks!</p> <p><strong>Update:</strong></p> <p>Sorry i forgot to tell, that substitulicense is nvarchar type. It contains another package name. I need this, because for example we have 100 pcs Office 2007 Licences and 50 pcs Office 2010 Licences. But Office 2010 licences can be used for office 2007 too, and so we can use 150 pcs of Office 2007. Of course the packaga what substitilicense value is exists also in this table, and this value is what i want to decrement. Hope you understand now how i meant</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkampi
    1. USkampi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to create a Stored Procedure to count licences recursively?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COIt feels like some of the issues you're having are related to the table architecture you're using. Is it possible to add/remove/edit tables, or are you stuck using what you've got?
      singulars
      1. POHow to create a Stored Procedure to count licences recursively?
      1. USlyrisey
    2. CO@mikurski: No, i can edit the table, and also create new ones if necessary. It was just an idea, that it should work, but now i see, that maybe i'm worng. Do you have a better idee how should i do it?
      singulars
      1. POHow to create a Stored Procedure to count licences recursively?
      1. USkampi
    3. COI think I have some ideas, but it'll take me a little time to write up. Does your Licenses table store a separate registration on each row of the table?
      singulars
      1. POHow to create a Stored Procedure to count licences recursively?
      1. USlyrisey
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload