Solved

Incorrect syntax near the keyword 'SELECT'.

Posted on 2007-04-05
2
704 Views
Last Modified: 2008-02-01
hi,

Does anyone know how to implement this:

I have two tables(table a and table b) which my client did not set to auto increment, both table have one similar column
called prodID,
so if i insert prodID 100 in table a, then it should prodID in table b.

i could implement this if the tables were set to increment, but i have been asked not to!!!

i got this error  

Incorrect syntax near the keyword 'SELECT'.

this line     SET @ProdID = SELECT MAX(prodID) FROM [ALKLicense].[dbo].[product]

 can't quite find the error, thanks
===============full code=========================================
ALTER PROCEDURE SPR_INSERT_PRODUCTS
      (
     
      @prodCode       varchar(10),
      @prodName       varchar(30),
      @prodDesc       varchar(200),
      @pfID           INT,
      @prodLicName    varchar(50),
      @prodLicEncKey  varchar(20),
      @supported      smallint,
      @licVersion     smallint,
      @prodAltCode    varchar(10),
      @numUniqueDevices int,
      @matchBaseLicOnly  int
      )
AS
BEGIN

DECLARE  @prodID  INT
Declare @Inserted int
IF EXISTS(Select * from product where prodName = @prodName)
BEGIN
  select @Inserted = 0
END
ELSE
    BEGIN
        SELECT @Inserted = 1
SET @ProdID = SELECT MAX(prodID) FROM [ALKLicense].[dbo].[product]
SET @ProdID = @ProdID + 1
INSERT INTO [ALKLicense].[dbo].[product]( [prodID], [prodCode], [prodName], [prodDesc], [pfID], [prodLicName],
 [prodLicEncKey], [supported], [licVersion], [prodAltCode] )
VALUES(
            @prodID,
            @prodCode,
            @prodName,
            @prodDesc,
            @pfID,
            @prodLicName,
            @prodLicEncKey,
            @supported,
            @licVersion,
            @prodAltCode
)

--SET @prodID = scope_identity()  

INSERT INTO [ALKLicense].[dbo].[licPolicy]([prodID],[numUniqueDevices],[matchBaseLicOnly])
VALUES(
         @prodID,
         @numUniqueDevices,  
         @matchBaseLicOnly
)
END
return @inserted
END

0
Comment
Question by:SirReadAlot
2 Comments
 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 500 total points
ID: 18857407
===============full code=========================================
ALTER PROCEDURE SPR_INSERT_PRODUCTS
      (
     
      @prodCode       varchar(10),
      @prodName       varchar(30),
      @prodDesc       varchar(200),
      @pfID           INT,
      @prodLicName    varchar(50),
      @prodLicEncKey  varchar(20),
      @supported      smallint,
      @licVersion     smallint,
      @prodAltCode    varchar(10),
      @numUniqueDevices int,
      @matchBaseLicOnly  int
      )
AS
BEGIN

DECLARE  @prodID  INT
Declare @Inserted int
IF EXISTS(Select * from product where prodName = @prodName)
BEGIN
  select @Inserted = 0
END
ELSE
    BEGIN
        SELECT @Inserted = 1
SET @ProdID = (SELECT MAX(prodID) FROM [ALKLicense].[dbo].[product])
SET @ProdID = @ProdID + 1
INSERT INTO [ALKLicense].[dbo].[product]( [prodID], [prodCode], [prodName], [prodDesc], [pfID], [prodLicName],
 [prodLicEncKey], [supported], [licVersion], [prodAltCode] )
VALUES(
            @prodID,
            @prodCode,
            @prodName,
            @prodDesc,
            @pfID,
            @prodLicName,
            @prodLicEncKey,
            @supported,
            @licVersion,
            @prodAltCode
)

--SET @prodID = scope_identity()  

INSERT INTO [ALKLicense].[dbo].[licPolicy]([prodID],[numUniqueDevices],[matchBaseLicOnly])
VALUES(
         @prodID,
         @numUniqueDevices,  
         @matchBaseLicOnly
)
END
return @inserted
END


0
 

Author Comment

by:SirReadAlot
ID: 18857420
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question