IF ELSE not compiling

I can't seem to figure out why this SP would not compile. Your assistance i
s
much appreciated.
CREATE PROCEDURE sp_getPubId
@.name varchar, @.pubCode varchar, @.pubId smallint Output
AS
DECLARE maxPubId smallint
if (exists
(select publication_id from publication where code =@.pubCode))
begin
@.maxPubId=(select publication_id from publication where code =@.pubCode)
@.pubId=@.maxPubId
end
else begin
@.maxPubId=select max(publication_id) from publication
insert into publication(publication_id, name, code, creation_date,
update_date, last_user_id, status, transmit_app, application_type,
flags)values(@.maxPubId, @.name, @.pubCode, getDate(), getDate(), 13, 1, 1, 1,0
)
@.pubId=@.maxPubId+1
end
return
--
bicHi
Just try it this way:
SET @.maxPubId=(select publication_id from publication where code =@.pubCode)
SET @.pubId=@.maxPubId
.
.
.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"bic" wrote:

> I can't seem to figure out why this SP would not compile. Your assistance
is
> much appreciated.
> CREATE PROCEDURE sp_getPubId
> @.name varchar, @.pubCode varchar, @.pubId smallint Output
> AS
> DECLARE maxPubId smallint
> if (exists
> (select publication_id from publication where code =@.pubCode))
> begin
> @.maxPubId=(select publication_id from publication where code =@.pubCode)
> @.pubId=@.maxPubId
> end
> else begin
> @.maxPubId=select max(publication_id) from publication
> insert into publication(publication_id, name, code, creation_date,
> update_date, last_user_id, status, transmit_app, application_type,
> flags)values(@.maxPubId, @.name, @.pubCode, getDate(), getDate(), 13, 1, 1, 1
,0)
> @.pubId=@.maxPubId+1
> end
> return
> --
> bic|||Thanks so much. You've solved a problem which plagued me for better part of
today. Thanks again.
--
bic
"bic" wrote:

> I can't seem to figure out why this SP would not compile. Your assistance
is
> much appreciated.
> CREATE PROCEDURE sp_getPubId
> @.name varchar, @.pubCode varchar, @.pubId smallint Output
> AS
> DECLARE maxPubId smallint
> if (exists
> (select publication_id from publication where code =@.pubCode))
> begin
> @.maxPubId=(select publication_id from publication where code =@.pubCode)
> @.pubId=@.maxPubId
> end
> else begin
> @.maxPubId=select max(publication_id) from publication
> insert into publication(publication_id, name, code, creation_date,
> update_date, last_user_id, status, transmit_app, application_type,
> flags)values(@.maxPubId, @.name, @.pubCode, getDate(), getDate(), 13, 1, 1, 1
,0)
> @.pubId=@.maxPubId+1
> end
> return
> --
> bic

IF Command

Hi All,

I am having a few problems writing a sp for the sales team, I have to display a target figure from a customer dates

start_date target_year1 target_year2 target_year3 target_year4 target_year5 01/01/2004 100 150 120 150 170 01/01/2003 90 20 30 30 30

the start date for each customer is different so the target year 1 ,2 ect will be different time periods.

i need to display the start_date and the target year which relates to todays date:

so no 1 would be year 3 and no2 will be year 4.

any help would be much needed!!

rich

Hi,

To do this you can use case statements.

try this:

select startdate, case datediff(yy, startdate, getdate())

when 0 then targetyear1

when 1 then targetyear 2

.

.

etc

from yourtable

Regards

|||thanks for your help, worked a treat!|||

JMattias

sorry the example was not very good the month my change,

so I would change the yy for month...the next problem is in the selecting between months

select startdate, case datediff(mm, startdate, getdate())

when <12 then targetyear1

when >= 12 and < 24 then targetyear 2

ect..

But this does not work ......

|||

use the datediff statement in every when

Like this

select startdate, case when datediff...... then when datediff..... then etc

Regards

|||thanks ...now that did do the job!!|||Good, I'm glad to help!

IDENTITY ON CRETE TABLE HELP PLEASE!

Below is the snippet of code that matters. Currently Figure 1 works great. I need to make Figure 2 work. Notice the Identity Seed vaue I need changed. Any ideas?

FIGURE 1:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY (1, 1),

Figure 2:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY ((SELECT NBRCOLUMN FROM TABLE WHERE BLAH = 'BLAH'), 1),

I need this desperately...
Thanks in advance.

JoeWhat exactly is the business requirement here?

If you needed to know, at run time, what the seed was, you could always create a dynamic sql string and exectute it.

But I have no idea why you would need to do this...|||I have to perform a query on a table which will retrieve a 4 digit number. This 4 digit number will become the identity seed for the purpose of this script. Once the data has been imported and the script completes, we will reverse the process and remove the Identity value on the column. Does that help?

Joe