1

I am trying to call a stored procedure (with output variable) using sp_executesql but within another stored procedure. I wrote the following, but still not able to get trhough what that error means

This would be called from webservice code:

exec sp1 'obj1',@params

Here obj and params are of nvarchar(max)

Definition of sp1 is :

Alter procedure [dbo].[sp1 ]  
    @procname nvarchar(max),  
    @params nvarchar(max)  
as   
    declare @temp varchar(15)  

    if @procname = 'obj1'  
    begin  
        set @params = @params +  ',@Newval  varchar(15) output'  
        EXEC sp_executesql @sp2,@params,@Newval=@temp OUTPUT  
    end

Definition of sp2:

Here I am returning @Newval

Error I am getting :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Also in 2 in place of exec statement , I have tried following:

 EXEC sp_executesql @sp2, @params, @temp OUTPUT;

Results in the same error.

set @sql='sp2,' + ' @params ' + ',@params,@temp OUTPUT'
EXEC sp_executesql (@sql)

Also results in the same error.

I need this dynamic selection of stored procedures in sp1 and params is a nvarchar(max) string of parameters and their values, some of them are varchar and are embedded in ''value'' format but this string is fine as I am able to call the underlying sp2 with this.

Additional info, it it helps.

EXEC sp_executesql @sp2,@params,@Newval=@temp OUTPUT

in this @params is combination of keys and vlaue pairs for the final sp. something like :
'@key1="a",@key2="b"'
and so on, I can not predefined the @params but it is dynamic and it is working fine when I run it with 

exec (@sql)

Format while whole of the name, params are embedded in the @sql

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3792812
  • 155
  • 1
  • 4
  • 13

1 Answers1

0

If @params='' or NULL then your , before @Newval is irrelevant. I suggest you to check:

IF NULLIF(@params,'') IS NULL or @params IS NULL
SET @params = '@Newval  varchar(15) output'
ELSE 
SET @params = @params + ',@Newval  varchar(15) output'

You are passing @sp2 maybe you need this:

ALTER PROCEDURE [dbo].[sp1]  
    @procname nvarchar(max),  
    @params nvarchar(max)  
AS   
    DECLARE @temp varchar(15)

    IF @procname = 'obj1'  
    BEGIN  
        SET @params = @params +  ',@Newval  varchar(15) output'  
        EXEC sp_executesql N'EXEC sp2 @someparam1, @someparam2, @Newval varchar(15) OUTPUT', @params, @someparam1 = 1, @someparam2 = 2, @Newval=@temp OUTPUT  
    END

EDIT

Working example:

USE [AdventureWorks]
GO

DECLARE @procname nvarchar(max) = 'EXEC [dbo].[uspGetWhereUsedProductID] @StartProductID, @CheckDate',  
        @params nvarchar(max) = '@StartProductID int, @CheckDate date'

EXEC sp_executesql @procname, @params, @StartProductID = 1, @CheckDate = '2015-10-17'
GO
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • Thanks but @Params is neither null nor ' ' :( – user3792812 Jun 07 '16 at 05:19
  • If I remove the output variable requirement I can still call sp2 with following declare @sql nvarchar(max); set @sql='sp2' + ' ' + @params; exec (@sql) but that is just defeating the purpose of getting output variable and also sp_executesql is not being used in this case. Please consider I am using sql server 2005 if that helps – user3792812 Jun 07 '16 at 05:21
  • What you send in '@params' from php? – gofr1 Jun 07 '16 at 05:27
  • I am not sending from php but a webservice call from java, string is '@a=1,@b=''PL_30338'',@c=1,@d=''P10563'',@e=''K_3749'' , this very same string works in my comment section mentioned way and also if just pass it along with sp1 name with exec 'sp2' + ' ' + @params – user3792812 Jun 07 '16 at 06:31
  • I understand. I add some info to my answer. Please, try. – gofr1 Jun 07 '16 at 06:47
  • Thanks @gofr1 , I tried that just now and found out I tried earlier too. Error is Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='. – user3792812 Jun 12 '16 at 16:25
  • does it seem some problem with output variable assignment ? @gofr1 – user3792812 Jun 12 '16 at 16:29
  • I add change second query in my answer please check it, and add working example with `[AdventureWorks]` DB. – gofr1 Jun 12 '16 at 17:51
  • Yes it is a problem with getting parameters to SP. – gofr1 Jun 12 '16 at 17:53
  • Thanks @gofr1 for helping me but I tried this solution, in the edit part, it is still showing the same error with '=', Actually I can not pass any additional parameter along with sp name and only possibility is in a combined string params which contains both parameters and their values for the final sp. I tried performing your suggestion which is adding the keyword exec along with sp name and got the same error. :( Also I found you added the newval varchar(15) output twice while putting in text, This way I have this in first param which is the name of the sp and also again in params, – user3792812 Jun 12 '16 at 18:35
  • Working example from edit actually works (I checked) your sp got OUTPUT parameter? Yes, newval must be declared as output from sp then determine where to put it (another variable) – gofr1 Jun 12 '16 at 18:42
  • Thanks @gofr1 , I agree with the point that it should ideally works as it does in the standard case, i.e. working example but in my case it is even simpler and does not work. I actually put the newval varchar(15) output along with sp name block and again added it to params still the same result. :( – user3792812 Jun 12 '16 at 18:48