T-SQL problems - stored procedure calling stored procedures

Hello

I have 3 stored procedures in total:

ALTER PROCEDURE svimwges_miv.test_UploadImage
 @ImageData binary,
 @ImageWidth int,
 @ImageHeight int,
 @PreviewImageData binary,
 @PreviewImageWidth int,
 @PreviewImageHeight int,
 @ImageFormat nvarchar(255),
 @ImageId uniqueidentifier OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- Insert statements for procedure here 
 BEGIN 
 DECLARE @MainImageId uniqueidentifier
 EXEC @MainImageId = svimwges_miv.test_UploadMedia 0,@ImageData,@ImageFormat
 EXEC svimwges_miv.test_AddMediasAttributes @MainImageId,'Width',@PreviewImageWidth
 EXEC svimwges_miv.test_AddMediasAttributes @MainImageId,'Height',@PreviewImageHeight
 DECLARE @PreviewImageId uniqueidentifier
 EXEC @PreviewImageId = svimwges_miv.test_UploadMedia 0,@PreviewImageData,@ImageFormat
 EXEC svimwges_miv.test_AddMediasAttributes @PreviewImageId,'Width',@PreviewImageWidth
 EXEC svimwges_miv.test_AddMediasAttributes @PreviewImageId,'Height',@PreviewImageHeight
 EXEC svimwges_miv.test_AddMediasAttributes @MainImageId,'PreviewImageId',@PreviewImageId
 EXEC @ImageId = @MainImageId
 END 
END

Open in new window

calling these two:
ALTER PROCEDURE svimwges_miv.test_UploadMedia
 @MediaType tinyint,
 @MediaData binary,
 @MediaFormat nchar(4),
 @MediaId uniqueidentifier OUTPUT
AS
DECLARE @err_message nvarchar(255)
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here 
 BEGIN 
  SET @MediaId = NEWID()
	IF @MediaType < 0 OR @MediaType > 3 -- 0 = image, 1 = video, 2 = audio
    BEGIN 
		SET @err_message = @MediaType + ' is not a valid media type!'
		RAISERROR (@err_message, 11,1)
    END

  INSERT INTO miv_Medias(MediaId, MediaType, MediaData, MediaFormat, CreateDate) 
  VALUES(@MediaId, @MediaType, @MediaData, @MediaFormat, GETUTCDATE())
 END 
END

Open in new window

ALTER PROCEDURE svimwges_miv.test_AddMediasAttributes
 @MediaId uniqueidentifier,
 @AttributeName nvarchar(MAX),
 @AttributeValue nvarchar(MAX)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here 
 BEGIN

 IF EXISTS (SELECT 1 FROM miv_MediasAttributes WHERE MediaId = @MediaId AND AttributeName = @AttributeName)
	BEGIN
		UPDATE miv_MediasAttributes SET AttributeName = @AttributeName, AttributeValue = @AttributeValue WHERE MediaId = @MediaId AND AttributeName = @AttributeName
	END
 ELSE
	INSERT INTO miv_MediasAttributes(MediaId, AttributeName, AttributeValue, CreateDate) 
  VALUES(@MediaId, @AttributeName, @AttributeValue, GETUTCDATE())
 END 
END

Open in new window


The UploadImage procedure, first of all expects a input value for the value defined as output? why is that and is there more problems with the scripts?
JoachimPetersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi Joachim
In the following procedure
ALTER PROCEDURE svimwges_miv.test_UploadMedia
 @MediaType tinyint,
 @MediaData binary,
 @MediaFormat nchar(4),
 @MediaId uniqueidentifier OUTPUT
AS
......

the last parameter is an OUTPUT param so this procedure would have to be called something like the following
DECLARE @MainImageId uniqueidentifier
 EXEC svimwges_miv.test_UploadMedia 0,@ImageData,@ImageFormat, @MainImageId OUTPUT
0
JoachimPetersenAuthor Commented:
Thank you, it seems that was the problem, but I do have a little problem before I can be sure:
Procedure or function 'test_UploadImage' expects parameter '@ImageId', which was not supplied.
Should I really provide some bogus value to ImageId, before I can execute it?
0
Barry CunneyCommented:
Hi Joachim,
The correct approach for PROCEDURE svimwges_miv.test_UploadImage is also to declare a parameter before the call to the procedure which gets the OUTPUT

so as follows:
DECLARE @ImageId uniqueidentifier
EXEC svimwges_miv.test_UploadImage 0,@ImageData,@ImageFormat, @ImageId OUTPUT

It is not best practice to just provide some bogus value to any parameter just for the sake of it - if there is a parameter defined in a procedure(either INPUT or OUTPUT parameter) the parameter values should be validly handled and if a parameter is not really needed then an assessment should be made if it should be defined in the procedure or not

Please let me know if you have any further questions
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JoachimPetersenAuthor Commented:
Okay, so I should not declare the output parameter before it gets a value, on another subject, 'Violation of PRIMARY KEY constraint 'PK__test___B2C2B5CF2A57D947'. Cannot insert duplicate key in object 'svimwges_miv.test_MediasAttributes'. The duplicate key value is (7d967ccb-98e6-476a-b37e-40b1973bef87).'
Should I set some setting for a uniqueidentifier datatype to allow it to be duplicated in a table?
0
Barry CunneyCommented:
Hi Joachim,
At the call to your top level(first) procedure yes you need to declare a variable for the @ImageId OUTPUT parameter, otherwise when you call svimwges_miv.test_UploadImage you will not have a variable which gets the ImageID value back from the procedure

If you are getting a PRIMARY key violation, you should first look at what you are doing from a design point of view - make an assessment of the purpose of the test_MediasAttributes table and what type and level of data it is meant to store - If there was a primary key with a Uniqueidentifier data type it needs to be established if there was a good reason for designing it this way - a better practice is to try and have a true unique primary key on the table - i.e. what identifies a true unique/distinct single 'MediaAttributes' record?
0
JoachimPetersenAuthor Commented:
The table has the uniqueiedentifier field to know which attributes that is for the specific media.
How can I allow a uniqueiedentifier to be present in multiple rows in a table so I can insert the rest of the data/attributes that applies for the media?
0
Barry CunneyCommented:
Hi Joachim,
If possible, please post up some sample records from the test_MediasAttributes table provided you are not violating any company disclosure or confidentiality rules. I am thinking that maybe this table should have a better primary key design - and maybe as well you can explain in business terms what this data is - I am thinking that maybe a more true unique primary key for this table would be a composite key  made up of both [Media_id] and [Attribute_id] or something like that.
0
JoachimPetersenAuthor Commented:
Okay, to get in perspective of what my initial idea with the tables designed as they are:
First off, I have 2 tables to store the data for the different types of medias:
I want the table named MediasAttributes to contain data like this:
8decfb83-779f-4807-bdf6-d329125d5b99      Width      1022      5/15/2015 7:40:52 PM
8decfb83-779f-4807-bdf6-d329125d5b99      Height      1022      5/15/2015 7:40:52 PM
f4ecfb83-779f-4807-bdf6-d329125d5b99      Width      122      5/15/2015 7:40:52 PM
f4ecfb83-779f-4807-bdf6-d329125d5b99      Height      122      5/15/2015 7:40:52 PM
8decfb83-779f-4807-bdf6-d329125d5b99      PreviewImageId      f4ecfb83-779f-4807-bdf6-d329125d5b99      5/15/2015 7:40:52 PM
In my above example the UploadImage has been populating the table with the width and height for both the full size image and the preview image, this will then corospond to the two entris in Medias table, which will contain this data based on above data sample:
8decfb83-779f-4807-bdf6-d329125d5b9      0      binarydata      jpeg      5/15/2015 7:40:52 PM
f4ecfb83-779f-4807-bdf6-d329125d5b99      0      binarydata      jpeg      5/15/2015 7:40:52 PM
So that is how it will work, two images entries (will also work for other media types, as audio and video, as the 0 in the table represents image type and then the attributes height and width for images and for the full size, a attribute which links to the preview image id.
By this way, I can simply search for the  PreviewImageId in attributes for the MediaID, that is why I designed this way, but again, how can I allow the MediaId column in the MediasAttributes to be present in multiple rows in a table so I can insert the rest of the data/attributes that applies for the media and stopping the 'Violation of PRIMARY KEY constraint' which only allows for 1 unique key in each column and therefore leaves the entries in the MediasAttributes incomplete as it terminates the statement.
0
JoachimPetersenAuthor Commented:
*Ignore above, apprently the first uniqueidentifier will become a primary key, just removed that option from it*
When Looking at the final script:
ALTER PROCEDURE svimwges_miv.test_UploadImage
 @ImageData binary,
 @ImageWidth int,
 @ImageHeight int,
 @PreviewImageData binary,
 @PreviewImageWidth int,
 @PreviewImageHeight int,
 @ImageFormat nvarchar(255),
 @MediaId uniqueidentifier = null OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- Insert statements for procedure here 
 BEGIN
 DECLARE @ImageId uniqueidentifier 
 DECLARE @MainImageId uniqueidentifier
 EXEC svimwges_miv.test_UploadMedia 0,@ImageData,@ImageFormat, @MainImageId OUTPUT 
 EXEC svimwges_miv.test_AddMediasAttributes @MainImageId,'Width',@PreviewImageWidth
 EXEC svimwges_miv.test_AddMediasAttributes @MainImageId,'Height',@PreviewImageHeight
 DECLARE @PreviewImageId uniqueidentifier
 EXEC svimwges_miv.test_UploadMedia 0,@PreviewImageData,@ImageFormat, @PreviewImageId OUTPUT 
 EXEC svimwges_miv.test_AddMediasAttributes @PreviewImageId,'Width',@PreviewImageWidth
 EXEC svimwges_miv.test_AddMediasAttributes @PreviewImageId,'Height',@PreviewImageHeight
 EXEC svimwges_miv.test_AddMediasAttributes @MainImageId,'PreviewImageId',@PreviewImageId
 SELECT @ImageId = @MainImageId
 END 
END

Open in new window

The output is:
Running [svimwges_miv].[test_UploadImage] ( @ImageData = aa, @ImageWidth = 1122, @ImageHeight = 1122, @PreviewImageData = bb, @PreviewImageWidth = 11, @PreviewImageHeight = 11, @ImageFormat = jpeg, @MediaId = <DEFAULT> ).

No rows affected.
(0 row(s) returned)
@MediaId = <NULL>
@RETURN_VALUE = 0
Finished running [svimwges_miv].[test_UploadImage].

Open in new window


Still the output is null, can you explain why the above is not set to @MainImageId and therefore should not be NULL?
0
Barry CunneyCommented:
Hi Joachim
The following is the approach which I would take on this:

First do a whole walkthrough manually doing each required INSERT/UPDATE in sequence, to verify what the process should be and if it is valid.

Then if this is valid and proves to be ok, manually run each individual stored procedure with relevant values and verify if there is a breakdown at any point.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.