August 13, 2003 at 6:33 am
I have this procedure and this ugly way is the only way i can think of doing it, since there is not switch statement in sql (or is there?) To make matters worse the last option --updating or inserting into the Gi column does not work. Please help me!
CREATE PROCEDURE spUpdateLookupTable
@Isbn nvarchar(10),
@uvalue int,
@fieldName nvarchar(50)
AS
Declare @isbnCount int
Select @isbnCount=Count(Isbn) FROM BOOKLOOKUP WHERE Isbn=@Isbn
if @isbnCount>0
if @fieldName='Reviews'
Update BOOKLOOKUP set Reviews=@uvalue WHERE Isbn=@Isbn
else if @fieldName='Summary'
Update BOOKLOOKUP set Summary=@uvalue WHERE Isbn=@Isbn
else if @fieldName='Characters'
Update BOOKLOOKUP set Characters=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Topics'
Update BOOKLOOKUP set Topics=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Series'
Update BOOKLOOKUP set Series=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Genre'
Update BOOKLOOKUP set Genre=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Period'
Update BOOKLOOKUP set Period=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Setting'
Update BOOKLOOKUP set Setting=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Awards'
Update BOOKLOOKUP set Awards=@uvalue,Gi=1 WHERE Isbn=@Isbn
else if @fieldName='Toc'
Update BOOKLOOKUP set Toc=@uvalue WHERE Isbn=@Isbn
else if @fieldName='Excerpt'
Update BOOKLOOKUP set Excerpt=@uvalue WHERE Isbn=@Isbn
else if @fieldName='Anotes'
Update BOOKLOOKUP set Anotes=@uvalue WHERE Isbn=@Isbn
else if @fieldName='test'
Update BOOKLOOKUP set Gi=@uvalue where Isbn=@Isbn
else
if @fieldName='Reviews'
Insert into BOOKLOOKUP (ISBN,Reviews) VALUES(@Isbn,@uvalue)
else if @fieldName='Summary'
Insert into BOOKLOOKUP(ISBN,Summary)VALUES(@Isbn,@uvalue)
else if @fieldName='Characters'
Insert into BOOKLOOKUP(ISBN,Characters,Gi)VALUES(@Isbn,@uvalue,1)
else if @fieldName='Topics'
Insert into BOOKLOOKUP(ISBN,Topics,Gi) VALUES(@Isbn,@uvalue,1)
else if @fieldName='Series'
Insert into BOOKLOOKUP(ISBN,Series,Gi) VALUES(@Isbn,@uvalue,1)
else if @fieldName='Genre'
Insert into BOOKLOOKUP(ISBN,Genre,Gi) VALUES(@Isbn,@uvalue,1)
else if @fieldName='Period'
Insert into BOOKLOOKUP(ISBN,Period,Gi) VALUES(@Isbn,@uvalue,1)
else if @fieldName='Setting'
Insert into BOOKLOOKUP(ISBN,Setting,Gi) VALUES(@Isbn,@uvalue,1)
else if @fieldName='Awards'
Insert into BOOKLOOKUP(ISBN,Awards,Gi) VALUES(@Isbn,@uvalue,1)
else if @fieldName='Toc'
Insert into BOOKLOOKUP(ISBN,Toc) VALUES(@Isbn,@uvalue)
else if @fieldName='Excerpt'
Insert into BOOKLOOKUP(ISBN,Excerpt) VALUES(@Isbn,@uvalue)
else if @fieldName='Anotes'
Insert into BOOKLOOKUP(ISBN,Anotes) VALUES(@Isbn,@uvalue)
else if @fieldName='test'
Insert into BOOKLOOKUP(Isbn,Gi) VALUES(@Isbn,@uvalue)
GO
August 13, 2003 at 7:04 am
One thing you might consider is a case statement:
update BOOKLOOKUP
set Reviews =
case
when @field = 'Reviews# then @uvalue
else [Reviews] /* set to the same value - ie do not update */
end
etc
where ....
I guess you can use a similar statement for the inserts.
Jeremy
August 13, 2003 at 8:20 am
Thanks, will try that.
August 14, 2003 at 7:11 am
-- I would use dynamic SQL
-- Just take the PRINT statement out when you are done testing
-- Good Luck!
CREATE PROCEDURE spUpdateLookupTable
@Isbn nvarchar(10),
@uvalue int,
@fieldName nvarchar(50)
AS
Declare @isbnCount int, @sSQL varchar(2000)
Select @isbnCount=Count(Isbn) FROM BOOKLOOKUP WHERE Isbn=@Isbn
if @isbnCount>0
SET @sSQL = 'Update BOOKLOOKUP set [' + @fieldName + '] = ' + CONVERT(varchar, @uvalue) + ' WHERE Isbn= ''' + @Isbn + ''''
else
SET @sSQL = 'Insert into BOOKLOOKUP (ISBN, [' + @fieldName + ']) VALUES(''' + @Isbn + ''', ' + CONVERT(varchar, @uvalue) + ')'
PRINT @sSQL
EXEC (@sSQL)
GO
August 14, 2003 at 7:31 am
Thanks CColson, That's what i ended up using.
I actually tried it before but for one reason or another could not get it to work so i went with the long version as listed above.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply