1

I am writing an SQL Script using a Stored Procedure, that somebody else wrote and I cannot change, which identifies whether or not a record can be safely deleted, based on its dependents.

My problem is that this stored procedure returns an anonymous resultset in the form of

IF <Condition>
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END

Now, I think this is poor design on the part of whoever wrote this because the messages are redundant, but that is besides the point because I cannot change it and so must work with what I have got.

The problem I have is that when I go to execute this proc

IF (EXEC CanDeleteRecord @RecordID = @Record_ID)

I don't really know how I am supposed to select the first column of the result set to compare with 'Y' or 'N' because the columns are anonymous. Is there some way of enumerating the results? This is a problem I have never encountered before with SQL and Google turns up squat.

  • Can you show the code you would use if the columns were named? There might be a way to name them using EXECUTE WITH RESULT SETS depending on the version of SQL Server you are on but IF (EXEC is not valid syntax so I don't see how that would help. – Martin Smith Aug 17 '16 at 18:24
4

Store the results in a temporary table or table variable and work with the stored set.

Here's a quick prototype you can work with.

create PROCEDURE [dbo].[TestMe]
AS
BEGIN
IF 1=1
BEGIN
  SELECT 'N', 'Cannot be deleted'
END
ELSE
BEGIN
  SELECT 'Y', ''
END
END


Declare @Results Table (CanDeleteIndicator char(1),Description varchar(30))
insert @Results exec dbo.testme
select * from @Results
  • Why CHAR instead of VARCHAR? The default datatype for literals not prefixed with N is VARCHAR, and with N is NVARCHAR. But otherwise, yes. – Solomon Rutzky Aug 17 '16 at 17:01
  • Does it really matter in this instance? – Scott Hodgin Aug 17 '16 at 17:02
  • In the sense that many people will simply copy and paste this stuff and hence it won't be applied in just this instance, yes, since it might matter in other instances. Besides, VARCHAR is vastly more commonly used than CHAR. – Solomon Rutzky Aug 17 '16 at 17:05
  • To be clear: I am not nit-picking to be difficult. I have been on the receiving end of plenty of copy-and-pasted code, so I try to help others avoid it :) – Solomon Rutzky Aug 17 '16 at 17:06
  • Understood and agree 100% on the copy paste thing - as I indicated, it was a prototype that I quickly typed up. I'm expecting people to actually 'test' this stuff :) – Scott Hodgin Aug 17 '16 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.