Solved

Incorrect syntax near the keyword 'SELECT'.

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now