February 13, 2008 at 9:04 pm
I need to declare a local variable in my SP that can hold an Image.
Since I can't declare it as an image datatype, I would say it should be varbinary(8000).
But what if that size isn't enough?
I can't use max, right?
What are my options here?
Any help would be greatly appreciated! Thanks!
February 13, 2008 at 10:03 pm
Ok this is the exact scenario:
I have a staging table for Ads. It stores Ad_ID, Description, etc.
We have an SP that imports these Ads from the staging table into our Ad_Master. However, until now we were not being given images.
The Ad_Image field added in the staging table is of type IMAGE.
We have Ad_Image field now in Ad_Master too, of type IMAGE.
Problem is, the import SP works with a cursor for some reason.
I need to declare a variable now for this Ad_Image.
Using VARBINARY(8000) limits me to less than 8kb! That really is a limitation! What do I do??
February 13, 2008 at 10:16 pm
Can you post the import proc please?
There are serveral people here very good at 'fixing' cursors (by replacing them with set-based code)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2008 at 11:44 pm
I know this does look messy but now since I've been asked to look into it,
I was wondering whether I should just leave it as it is and introduce the new variable for IMAGE.
Now since you're saying someone could help change it better to set-based.... It would be great.
ALTER PROCEDURE [dbo].[sp_Import_Ads]
@User_Name NVARCHAR(65),
@Received_Ads NUMERIC OUT,
@Valid_Ads NUMERIC OUT,
@Imported_Ads NUMERIC OUT
AS
DECLARE
@uid_design CHAR(32),
@str_design_desc CHAR(50),
@str_name CHAR(50),
@str_modify_code CHAR(1),/*1 = Fresh Record , 2 = Imported , 3 = Updated/Modified */
@Record_Cnt INT,
@Err_Flag BIT,
@str_Image VARBINARY(8000)
SET @Err_Flag = 0
SET @Valid_Ads = 0
SET @Imported_Ads = 0
SET @Received_Ads = 0
SELECT @Received_Ads = COUNT(*) FROM Ad_Master_Import WHERE str_modify_code = '1' OR str_modify_code = '3'
DECLARE Ad_Master_Import_Cursor CURSOR FOR
SELECT uid_design, str_design_desc, str_Image,str_name, str_modify_code FROM Ad_Master_Import
OPEN Ad_Master_Import_Cursor
FETCH NEXT FROM Ad_Master_Import_Cursor INTO
@uid_design, @str_design_desc,@str_Image, @str_name, @str_modify_code
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
SELECT @Record_Cnt = COUNT(*) FROM tbl_AD_MASTER WHERE Ad_Name = @uid_design
IF @@ERROR <> 0 GOTO Err_Handler
IF @Record_Cnt = 0 -- RECORD DOES NOT EXIST
BEGIN
IF @str_modify_code = '1'
BEGIN
SET @Valid_Ads = @Valid_Ads + 1
-- ADD RECORD
INSERT INTO tbl_AD_MASTER (Ad_Name, Ad_Description,Ad_Image, Customer_Name, Imported_Flag, Record_Status_Flag, Created_By, Created_Datetime,Updated_By,Updated_Datetime)
VALUES (@uid_design, @str_design_desc,@str_Image, @str_name, 1, 1, @User_Name, GETDATE(), @User_Name, GETDATE())
IF @@ERROR <> 0 GOTO Err_Handler
UPDATE Ad_Master_Import SET str_modify_code = '2' WHERE uid_design = @uid_design
IF @@ERROR <> 0 GOTO Err_Handler
SET @Imported_Ads = @Imported_Ads + 1
END
END
ELSE -- RECORD EXISTS
BEGIN
IF @str_modify_code = '1'
BEGIN
SET @Valid_Ads = @Valid_Ads + 1
-- UPDATE RECORD
UPDATE tbl_AD_MASTER SET
Ad_Description = @str_design_desc,
Ad_Image = @str_Image,
Customer_Name = @str_name,
Record_Status_Flag = 2,
Updated_By = @User_Name,
Updated_Datetime = GETDATE()
WHERE Ad_Name = @uid_design
IF @@ERROR <> 0 GOTO Err_Handler
UPDATE Ad_Master_Import SET str_modify_code = '2' WHERE uid_design = @uid_design
IF @@ERROR <> 0 GOTO Err_Handler
SET @Imported_Ads = @Imported_Ads + 1
END
ELSE IF @str_modify_code = '3'
BEGIN
SET @Valid_Ads = @Valid_Ads + 1
-- UPDATE RECORD
UPDATE tbl_AD_MASTER SET
Ad_Description = @str_design_desc,
Ad_Image = @str_Image,
Customer_Name = @str_name,
Record_Status_Flag = 3,
Updated_By = @User_Name,
Updated_Datetime = GETDATE()
WHERE Ad_Name = @uid_design
IF @@ERROR <> 0 GOTO Err_Handler
SET @Imported_Ads = @Imported_Ads + 1
END
END
COMMIT TRANSACTION
FetchNextLabel:
FETCH NEXT FROM Ad_Master_Import_Cursor INTO @uid_design, @str_design_desc,@str_Image, @str_name, @str_modify_code
END
CLOSE Ad_Master_Import_Cursor
DEALLOCATE Ad_Master_Import_Cursor
RETURN @Err_Flag
Err_Handler:
RAISERROR ('Error in importing Ad in sp_Import_Ads', 16, 1)
SET @Err_Flag = 1
ROLLBACK TRANSACTION
GOTO FetchNextLabel
February 14, 2008 at 12:19 am
The thing is, you can't create a variable of type image. If it were SQL 2005, you could use varbinar(max) but that's not an option on 2000. So the suggested solution is to rewrite to avoid need.
This looks like a fairly simple update if matched and insert if not. Am I wrong?
This is a fairly quick first try. I have a meeting shortly.
SELECT uid_design, str_design_desc, str_Image,str_name, str_modify_code FROM Ad_Master_Import
DECLARE @NumberUpdated int, @NumberInserted INT
-- If matched
UPDATE tbl_AD_MASTER SET
Ad_Description = str_design_desc,
Ad_Image = str_Image,
Customer_Name = str_name,
Record_Status_Flag = CASE str_modify_code WHEN '1' THEN '2' WHEN '3' THEN '3' END,
Updated_By = @User_Name,
Updated_Datetime = GETDATE()
FROM Ad_Master_Import
WHERE tbl_AD_MASTER.Ad_Name = Ad_Master_Import.uid_design
AND str_modify_code IN ('1','3')
SET @NumberUpdated = @@RowCount
-- If Not matched
INSERT INTO tbl_AD_MASTER (Ad_Name, Ad_Description,Ad_Image, Customer_Name, Imported_Flag, Record_Status_Flag, Created_By, Created_Datetime,Updated_By,Updated_Datetime)
SELECT uid_design, str_design_desc, str_Image,str_name, 1, 1, @User_Name, GETDATE(), @User_Name, GETDATE()
FROM Ad_Master_Import
WHERE uid_design NOT IN (SELECT Ad_Name FROM tbl_AD_MASTER) AND str_modify_code = '1'
SET @NumberInserted = @@rowcount
-- Now the status of the Import file
UPDATE Ad_Master_Import SET str_modify_code = 2 WHERE str_modify_code IN ('1','3')
AND uid_design IN (SELECT Ad_Name FROM tbl_AD_MASTER) -- So that we only get ones that have been imported.
You can then use the Number inserted and number updated to calc the imported ads.
I've done no error handling. It's left as an exercise to the reader
Does this help at all?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2008 at 2:20 am
That's perfect, Gail. That's exactly what I've done. Its only after I pasted the code here that I realized there was nothing in it except for update/insert into a single table 🙂
I guess I was just holding back from writing a new SP since I was on an online conference meeting for the demo.
So I quickly posted this query while demo-ing another module 🙂
Thanks a ton, Gail.... you're a gem!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply