Solved

Incorrect syntax near the keyword 'SELECT'.

Posted on 2007-04-05
2
710 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

680 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