Trouble declaring variable.

[sparkbyte]

I was trying to alter an example and can't seem to get past a problem decaring a new variable.

Update Test
Declare @Next Int
	Set @Next = dbo.fnGetNumbers(OfferID)+1;
	Set OfferID = 'Offer' + 1
		Where OfferID = (Select Max(dbo.fnGetNumbers(OfferID)) from Test)

Result:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Declare'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.

I created a table in Tempdb to mess with.

Use tempdb

Create Table Test
(
OfferID VarChar(10),
OfferName VarChar(12),
IsActive int
)
Insert Into Test Values('Offer01','March',0)
Insert Into Test Values('Offer02','April',0)
Insert Into Test Values('Offer03','May',1)


Thanks

John Fuhrman

 

[markros]

Can you explain what you want and what is your SQL Server version? You're probably looking for ROW_NUMBER() function.

As to your problem, you have
UPDATE TEST
and then DECLARE ...

You can not declare something in the middle of UPDATE.
In addition, your SET OfferOD should be SELECT OfferID =
(you can not have WHERE using SET command).

PluralSight Learning Library

 

[bborissov]

Several prblems here:
1. As markros saidm you can not declare a variable in the middle of the statmenet.
It should be declared otside.
2. Set OfferID = 'Offer' + 1 is not right.
OfferID is varchar but 1 is pure integer.

3. Where OfferID = (Select Max(dbo.fnGetNumbers(OfferID)) from Test)
Again OfferID but the function returns integer.



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[sparkbyte]

Sorry it should have been this.
I had been messing with it too much.

Declare @Next Int    
Set @Next = dbo.fnGetNumbers(OfferID)+1    

Update Test
Set OfferID = 'Offer' + @Next        
Where OfferID = (Select Max(dbo.fnGetNumbers(OfferID)) from Test)

the result should set the offerid to 'Offer04' being the next sequential number.

Thanks

John Fuhrman

 

[markros]

The above still will not work.

1. What is OfferID here

set @Next = ... -- you can not use field's name in this statement

2. 'Offer' + @Next will not work either, you need

'Offer' + convert(varchar(100),@Next)

3. What is the type of the field OfferID? If it's an integer field, how can you assign a character to it?

--------------------
Can you provide Test table DDL, some input data and desired
result after update ?




PluralSight Learning Library

 

[sparkbyte]

Well the thaught was with offer01, offer02,offer03, get the max numerical value after 'offer' and put it into a variable increasing it by one then append it to the end of 'offer' thus providing the next offer ID.

This would be used in a instead of insert to assign the next offer ID.

This is not my table design, but I am now the only "DBA" at our facility and I am trying to learn as much about SQL as I can and this seemed like a nice puzzle to work out. Unfortunately I am still stumbling on some of the workings of triggers.

Thanks

John Fuhrman