?
Solved

Incorrect syntax near the keyword 'SELECT'.

Posted on 2007-04-05
2
Medium Priority
?
716 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

764 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