Stored Procedure return 2 result sets 3

[Halliarse]

Hi

I have created a stored procedure which I am calling via a VB application and it was returning an error. I have run it manually and it appears to be returning 2 responses, a return value of 0 suggesting the record is inserted correctly, but also a (No column name) value of -1. The record inserted on the table is correct so I am very much at a loss as to what the issue is! Can anyone please advise? The Stored Procedure is below:

USE [Snip-IT]
GO

/****** Object: StoredProcedure [dbo].[Insert_Salon] Script Date: 09/08/2014 11:25:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[Insert_Salon]
@SalonID INT,
@TradingName NVARCHAR(30),
@AddressLine1 NVARCHAR(30),
@AddressLine2 NVARCHAR(30),
@AddressLine3 NVARCHAR(30),
@City NVARCHAR(20),
@County NVARCHAR(20),
@Postcode NVARCHAR(10),
@Telephone NVARCHAR(11),
@Fascimile NVARCHAR(11),
@EmailAddress NVARCHAR(50),
@OAPDay INT,
@ClosedDay INT,
@Active INT

AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT TradingName FROM Salon WHERE TradingName = @TradingName)
BEGIN
SELECT -1 -- Salon exists.
END
ELSE
BEGIN
INSERT INTO [Salon]
([SalonID]
,[TradingName]
,[AddressLine1]
,[AddressLine2]
,[AddressLine3]
,[City]
,[County]
,[Postcode]
,[Telephone]
,[Fascimile]
,[EmailAddress]
,[OAPDay]
,[ClosedDay]
,[Active])
VALUES
(@salonID
,@TradingName
,@AddressLine1
,@AddressLine2
,@AddressLine3
,@City
,@County
,@Postcode
,@Telephone
,@Fascimile
,@EmailAddress
,@OAPDay
,@ClosedDay
,@Active)

SELECT SCOPE_IDENTITY() -- SalonID
END
END


GO

Many thanks

Steve

 

[gmmastros]

Do you have any triggers on the Salon table? To check, run this...

sp_helptrigger 'Salon'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Halliarse]

No triggers on Salon

 

[gk53]

What is your table structure? Do you have identity column in your table?

 

[Halliarse]

Hi

Please see the script below for the table structure:

USE [Snip-IT]
GO

/****** Object: Table [dbo].[Salon] Script Date: 09/08/2014 14:58:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Salon](
[SalonID] [int] NOT NULL,
[TradingName] [varchar](30) NULL,
[AddressLine1] [varchar](30) NULL,
[AddressLine2] [varchar](30) NULL,
[AddressLine3] [varchar](30) NULL,
[City] [varchar](20) NULL,
[County] [varchar](20) NULL,
[Postcode] [varchar](10) NULL,
[Telephone] [varchar](11) NULL,
[Fascimile] [varchar](11) NULL,
[EmailAddress] [varchar](50) NULL,
[OAPDay] [int] NULL,
[ClosedDay] [int] NULL,
[Active] [int] NULL,
CONSTRAINT [PK_Salon] PRIMARY KEY CLUSTERED
(
[SalonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Salon] WITH CHECK ADD CONSTRAINT [FK_Salon_Salon] FOREIGN KEY([SalonID])
REFERENCES [dbo].[Salon] ([SalonID])
GO

ALTER TABLE [dbo].[Salon] CHECK CONSTRAINT [FK_Salon_Salon]
GO

 

[Olaf Doschke]

Well, your code suggests a -1 is returned in case the salon already exists, which only is determined by checking the TradingName already exists. Also a 0 should never be returend, SCOPE_IDENTEIY() would return the new salon ID, even for a new empty table that would normally start with 1.

So from that perspective you haven't inserted a new salon but simply see some already existing Salon.

What VB Code is using the stored procedure and what return values did you inspect? If you use SqlCommand.ExecuteNonQuery and it's return value is 0 it means no rows effected, so no new row and with the return value of -1 from the stored proc together it means you called it with an already existing TradingName.

Bye, Olaf.

 

[Halliarse]

Hi Olaf

The -1 if a Salon exists based on Trading Name is correct, however I'm testing with unique names and still the -1 is returned. However, I just tested again and this has now changed....please see the attached screen shot.

 

[gk53]

I do not see any identity column in your table...
salonId is primary key, but not identity column, so you are sending @salonId value in your stored procedure as parameter...
and as mention OlafDoschke you chacking if record exist by TradingName which is nullable field and not unique in your table

 

[DaveInIowa]

What error is being returned? Your post title indicates you are returning 2 result sets but I'm only seeing 1 result set and 1 implicit return value (which is always 0). Your result set will be an unnamed column of -1 if the trade name parameter exists and NULL if a Salon row is added. It will be NULL because, as gk53 pointed out, you don't have an identity column in your table.

 

[Halliarse]

Thanks all for pointing me in the right direction, I have now got this working perfectly as I have changed the structure of my Salon table and included the SalonID as an Identity column. However, I now have another stored procedure which is set to update a single column on the same table and whilst running this manually, works perfectly and updates the field correctly but once again, when I call it from the VB, it generates an error! The stored procedure is as below:

USE [Snip-IT]
GO

/****** Object: StoredProcedure [dbo].[Activate_Salon] Script Date: 09/09/2014 15:43:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[Activate_Salon]
@SalonID INT,
@SalonStatus INT

AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT TradingName FROM Salon WHERE SalonID = @SalonID)
BEGIN
Update [Salon]
Set Active = @SalonStatus
Where SalonID = @SalonID

SELECT SCOPE_IDENTITY() -- SalonID
END
Else
BEGIN
SELECT -1 -- Salon does not exists.
END

END

GO

The vb calls the stored procedure via the following line of code - Activate = Convert.ToInt32(cmd.ExecuteScalar())

The error message genrated is as follows: [InvalidCastException: Object cannot be cast from DBNull to other types.]

There are no links or foreign keys to any other table so I'm not sure what I'm missing here...any help will be very much appreciated.

Many thanks

Steve

 

[Olaf Doschke]

SCOPE_IDENTITY returns null in case you don't insert a record. And you don't insert a record, you update now with your new code. You could return the SalonID by SELECT @SalonID, then -1 indicates no update and getting the salonid passed in as return value indicates this salon is updated.

Bye, Olaf.

 

[Halliarse]

Olaf, you are a legend!!

 

[Olaf Doschke]

Well, I'm not dead yet, but thanks!

 

[Olaf Doschke]

Bonus" answer: You could also do without the EXISTS test:

USE [Snip-IT]
GO

/****** Object: StoredProcedure [dbo].[Activate_Salon] Script Date: 09/09/2014 15:43:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Activate_Salon]
@SalonID INT,
@SalonStatus INT

AS
BEGIN
SET NOCOUNT ON;
Update [Salon]
Set Active = @SalonStatus
Where SalonID = @SalonID

SELECT CASE WHEN @@ROWCOUNT=0 THEN -1 ELSE @SalonID END
END

GO

Reasoning: When you do the EXISTS test SQL Server will lookup the @alonID in an index it also uses for the UPDATE. And since that is what you do, when EXISTS returns true, you can do it right away. An UPDATE will not error, if no record fullfills the where condition and it's also not most expensive in that case. Doing EXISTS and UPDATE will let SQL Server lookup the same @SalonID twice in the normal case. I assume you pass in a @SalonID you got from a previous query anyway.

Also notice @@ROWCOUNT is also set, if you SET NOCOUNT ON.

With this change you could also simply return @@ROWCOUNT via SELECT @@ROWCOUNT, as 0 indicates no update and 1 will indicate the update of the @SalonID and any higher value will tell you your SalonID values are not unique.

Bye, Olaf.