MS SQL stored procedure output error

REST API
1

Hi!

I’ve been trying to make an API call to a MS SQL Server DB stored procedure but I get an error (actually two, depending on the method I use). Let me break this down in methods I tried:

Method 1

The stored procedure looks like this:

 USE [dbname]
 GO
 /****** Object:  StoredProcedure [dbo].[Signup]    Script Date: 08/12/2016 13:56:06 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

 ALTER PROCEDURE [dbo].[Signup]
 	@name nvarchar(50),
 	@email varchar(100),
 	@password varchar(100) = null,
 	@passwordEncrypted varchar(100),
 	@screen varchar(20),
 	@device varchar(max),
 	@ip varchar(20),
 	@language char(2),
 	@currency char(3),
 	@city nvarchar(100),
 	@country char(2),
 	@timezone int,
 	@age int,
 	--out
 	@pkey varchar(100) output
 AS
 BEGIN

     SET NOCOUNT ON
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 	--DECLARE @pkey varchar(100)

 	--Check text
 	IF (SELECT [dbo].[isBannedText](@name)) = 1
 		OR (SELECT [dbo].[isBannedText](@email)) = 1
 		BEGIN RETURN END
 		
 	--Check email not blocked
 	IF [dbo].[isBlockedViaEmail](@email) = 1 BEGIN RETURN END	
 	
 	--Create or get person
 	DECLARE @personId bigint = 0,
 			@secret varchar(13),
 			@url varchar(100)
 	
 	--Exists?
 	SELECT @personId = personId
 	FROM [dbo].[Accounts]
 	INNER JOIN [dbo].[People] ON [People].id = [Accounts].personId
 	WHERE email = @email
 	
 	IF @personId = 0
 		BEGIN
 			
 			--Create name
 			IF @name = @email
 				BEGIN SET @name = [dbo].[removeSpecialChars]([dbo].[repairName](@email)) END
 			
 			--Create secret
 			EXECUTE [dbo].[CreateSecret] @secret = @secret OUTPUT
 			
 			--Create person
 			BEGIN TRY
 				INSERT INTO [dbo].[People] ([name],[email],[secret],[passwordEncrypted],[language])
 				VALUES (@name,@email,@secret,@passwordEncrypted,@language)
 				SET @personId = @@IDENTITY
 			END TRY
 			BEGIN CATCH
 				SELECT @personId = [id] FROM [dbo].[People] WHERE [email] = @email
 			END CATCH
 			
 			--Create key
 			SET @pkey = CAST(@personId as varchar) + '-' + @secret
 			
 			--Inserts
 			INSERT INTO [dbo].[Accounts] ([personId],[gems],[country],[city],[ip],[screen],[timezone],[currency],[device],[age])
 			VALUES (@personId,26,@country,@city,@ip,@screen,@timezone,@currency,[dbo].[getDevice](@device),@age)

 			--Create URL and set up
 			EXECUTE [dbo].[CreateURL] @personId = @personId, @url = @url OUTPUT
 			INSERT INTO [dbo].[History] ([personId]) VALUES (@personId)
 			INSERT INTO [dbo].[Settings] ([personId]) VALUES (@personId)
 			
 			--Email
 	----EMAIL
	--EXECUTE [dbo].[CreateEmail] 
 	--		@template = 'signup',
 	--		@pkey = 'admin',
 	--		@id = @personId,
 	--	@data1 = @password	
 			
 			--Log
 			EXECUTE [dbo].[CreateDailyLog] @pkey = @pkey, @action = 'signup'
 			
 			--SELECT @pkey as [pkey]
 		END	
 	
END

I pass the following params:

{
  "params": [
    {
      "name": "name",
      "value": "zzz"
    },
    {
      "name": "email",
      "value": "zzz@zzz.com"
    },
    {
      "name": "password",
      "value": "zzz"
    },
    {
      "name": "passwordEncrypted",
      "value": "a906zzz449d"
    },
    {
      "name": "screen",
      "value": "1920x1080"
    },
    {
      "name": "device",
      "value": "Mac"
    },
    {
      "name": "ip",
      "value": "127.0.0.1"
    },
    {
      "name": "language",
      "value": "en"
    },
    {
      "name": "currency",
      "value": "GBP"
    },
    {
      "name": "city",
      "value": "London"
    },
    {
      "name": "country",
      "value": "GB"
    },
    {
      "name": "timezone",
      "value": "1"
    },
    {
      "name": "age",
      "value": "99"
    },
    {
      "name": "pkey",
      "value": ""
    }
  ],
  "schema": {
    "pkey": "string"
  },
  "wrapper": "data"
}

But I get the following error:

{
  "error": {
    "context": null,
    "message": "Failed to call database stored procedure.\nSQLSTATE[IMSSP]: The active result for the query contains no fields.",
    "code": 500
  }
}

Method 2

The difference here is the SPROC, instead of using the OUT definition, it’s simply doing a SELECT. The stored procedure looks like this:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[Signup]    Script Date: 08/12/2016 13:19:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Signup]
     @name nvarchar(50),
	@email varchar(100),
	@password varchar(100) = null,
	@passwordEncrypted varchar(100),
	@screen varchar(20),
	@device varchar(max),
	@ip varchar(20),
	@language char(2),
	@currency char(3),
	@city nvarchar(100),
	@country char(2),
	@timezone int,
	@age int
	--out
	--@pkey varchar(100) output
AS
 BEGIN
    
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @pkey varchar(100)
>
	--Check text
	IF (SELECT [dbo].[isBannedText](@name)) = 1
		OR (SELECT [dbo].[isBannedText](@email)) = 1
		BEGIN RETURN END
		
	--Check email not blocked
	IF [dbo].[isBlockedViaEmail](@email) = 1 BEGIN RETURN END	
	
	--Create or get person
	DECLARE @personId bigint = 0,
			@secret varchar(13),
			@url varchar(100)
	
	--Exists?
	SELECT @personId = personId
	FROM [dbo].[Accounts]
	INNER JOIN [dbo].[People] ON [People].id = [Accounts].personId
	WHERE email = @email
	
	IF @personId = 0
		BEGIN
			
			--Create name
			IF @name = @email
				BEGIN SET @name = [dbo].[removeSpecialChars]([dbo].[repairName](@email)) END
			
			--Create secret
			EXECUTE [dbo].[CreateSecret] @secret = @secret OUTPUT
			
			--Create person
			BEGIN TRY
				INSERT INTO [dbo].[People] ([name],[email],[secret],[passwordEncrypted],[language])
				VALUES (@name,@email,@secret,@passwordEncrypted,@language)
				SET @personId = @@IDENTITY
			END TRY
			BEGIN CATCH
				SELECT @personId = [id] FROM [dbo].[People] WHERE [email] = @email
			END CATCH
			
			--Create key
			SET @pkey = CAST(@personId as varchar) + '-' + @secret
			
			--Inserts
			INSERT INTO [dbo].[Accounts] ([personId],[gems],[country],[city],[ip],[screen],[timezone],[currency],[device],[age])
			VALUES (@personId,26,@country,@city,@ip,@screen,@timezone,@currency,[dbo].[getDevice](@device),@age)
>
			--Create URL and set up
			EXECUTE [dbo].[CreateURL] @personId = @personId, @url = @url OUTPUT
			INSERT INTO [dbo].[History] ([personId]) VALUES (@personId)
			INSERT INTO [dbo].[Settings] ([personId]) VALUES (@personId)
			
			--Email
	----EMAIL
	--EXECUTE [dbo].[CreateEmail] 
	--		@template = 'signup',
	--		@pkey = 'admin',
	--		@id = @personId,
	--	@data1 = @password	
			
			--Log
			EXECUTE [dbo].[CreateDailyLog] @pkey = @pkey, @action = 'signup'
			
			SELECT @pkey as [pkey]

		END	
	
END

This returns the following error (using the same params):

{
  "error": {
    "context": null,
    "message": "Failed to call database stored procedure.\nSQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Procedure or function Signup has too many arguments specified.",
    "code": 500
  }
}

Note:

  • If I execute the stored procedure in the MS Server Studio it returns the “pkey” value correctly.
  • Although I’m using DF v2.2.1 I tried to use the “param_type” definition but still no luck
  • I tried every possible combination in the PARAMS for both versions/methods (with a null “pkey”, leaving the “pkey” field out altogether)

Any help or ideas would be extremely helpful at this point.

Many thanks!

1 Like
2

Hi @Adrian, thank you so much for putting the effort into documenting your issues and errors.

I am just checking in to see if you have resolved any of these, or what you may still be experiencing?

Thanks,
@AlexBowen

3

Hi @AlexBowen

Thanks for checking in, I’ve managed to sort it out meanwhile via a support ticket.

The problem was caused partially by the Stored Procedure. The take-away:

  • Don’t use OUTPUT Variables, instead use a SELECT
  • The SELECT must be outside of any conditional statement (examples below)

The working version for my case (see comments in the code):

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[Signup]    Script Date: 08/12/2016 13:19:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Signup]
    @name nvarchar(50),
	@email varchar(100),
	@password varchar(100) = null,
	@passwordEncrypted varchar(100),
	@screen varchar(20),
	@device varchar(max),
	@ip varchar(20),
	@language char(2),
	@currency char(3),
	@city nvarchar(100),
	@country char(2),
	@timezone int,
	@age int
	--@pkey varchar(100) output // dont use OUTPUT vars
AS
 BEGIN
    
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @pkey varchar(100)

	--Check text
	IF (SELECT [dbo].[isBannedText](@name)) = 1
		OR (SELECT [dbo].[isBannedText](@email)) = 1
		BEGIN RETURN END
		
	--Check email not blocked
	IF [dbo].[isBlockedViaEmail](@email) = 1 BEGIN RETURN END	
	
	--Create or get person
	DECLARE @personId bigint = 0,
			@secret varchar(13),
			@url varchar(100)
	
	--Exists?
	SELECT @personId = personId
	FROM [dbo].[Accounts]
	INNER JOIN [dbo].[People] ON [People].id = [Accounts].personId
	WHERE email = @email
	
	IF @personId = 0
		BEGIN
			
			--Create name
			IF @name = @email
				BEGIN SET @name = [dbo].[removeSpecialChars]([dbo].[repairName](@email)) END
			
			--Create secret
			EXECUTE [dbo].[CreateSecret] @secret = @secret OUTPUT
			
			--Create person
			BEGIN TRY
				INSERT INTO [dbo].[People] ([name],[email],[secret],[passwordEncrypted],[language])
				VALUES (@name,@email,@secret,@passwordEncrypted,@language)
				SET @personId = @@IDENTITY
			END TRY
			BEGIN CATCH
				SELECT @personId = [id] FROM [dbo].[People] WHERE [email] = @email
			END CATCH
			
			--Create key
			SET @pkey = CAST(@personId as varchar) + '-' + @secret
			
			--Inserts
			INSERT INTO [dbo].[Accounts] ([personId],[gems],[country],[city],[ip],[screen],[timezone],[currency],[device],[age])
			VALUES (@personId,26,@country,@city,@ip,@screen,@timezone,@currency,[dbo].[getDevice](@device),@age)

			--Create URL and set up
			EXECUTE [dbo].[CreateURL] @personId = @personId, @url = @url OUTPUT
			INSERT INTO [dbo].[History] ([personId]) VALUES (@personId)
			INSERT INTO [dbo].[Settings] ([personId]) VALUES (@personId)
			
			--Log
			EXECUTE [dbo].[CreateDailyLog] @pkey = @pkey, @action = 'signup'

			-- Previous SELECT was here, now moved (see below)

		END	

		-- New SELECT here
		SELECT @pkey as [pkey]
	
END

Or a basic example in case it helps someone:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[Signup]    Script Date: 08/31/2016 11:14:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Signup]
	@name nvarchar(50),
	@something nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE
		@pkey varchar(100),
		@secret varchar(100)
	
	SET @secret = '999'
	SET @pkey = @name + '-' + @secret + '-' + @something
		
	SELECT @pkey as [pkey]

END

For the params I had to specify the "param_type" value (IN or OUT). Trimmed example:

{
  	"name": "name",
  	"param_type": "IN",
  	"value": "John"
},
{
  	"name": "something",
  	"param_type": "IN",
  	"value": "123"
},
{
  	"name": "pkey",
  	"param_type": "OUT",
  	"type": "string"
}

With these adjustments this works fine for both POST (with -d data) and GET with the payload in the URL as per the API Docs.

A.

1 Like
4

Thanks so much @Adrian for sharing what works for you and following up.

Best,
@AlexBowen