0

Can someone help me with the following stored procedure?

When I execute this stored procedure:

DECLARE @retVal varchar(500)

EXEC utility_getDatabaseLocation 
            @program = "RH17009", @locationOutput = @retVal OUTPUT

SELECT @retVal

It returns the following @sql statement:

SELECT Location 
FROM Intranet.dbo.tbl_Campaigns 
WHERE JobCode = @paramProgram

So the dynamic SQL statement parameter is not being passed.

ALTER PROCEDURE [dbo].[utility_getDatabaseLocation]
    @program AS NVARCHAR(75),
    @locationOutput VARCHAR(500) OUTPUT
AS
   DECLARE @ParmDefinition nvarchar(500);

   SET @ParmDefinition = N'@paramProgram varchar(75),@paramOUTPUT varchar(500) OUTPUT';

   DECLARE @sql AS nvarchar(500)
   BEGIN
       SET @sql = N'SELECT Location FROM Intranet.dbo.tbl_Campaigns WHERE JobCode = @paramProgram'

       EXEC sp_executesql @sql, @ParmDefinition,  
            N'@paramProgram=@program', N'@paramOUTPUT=@locationOutput OUTPUT'

       PRINT @sql
       PRINT @locationOutput 
   END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Charles
  • 1
  • 5
  • 1
    It does appear to be passed correctly in the actual `EXEC sp_executesql` - that's how it works. What makes you think it isn't passed? – Nick.Mc Nov 05 '17 at 04:08
  • At the bottom of the sp I print @sql. When I try to execute the stored procedure I use the value RH17009 as the @program parameter. What I expect to see when i print the @sql varaible is: `SELECT Location FROM Intranet.dbo.tbl_Campaigns WHERE JobCode=RH17009` But what I get is: `SELECT Location FROM Intranet.dbo.tbl_Campaigns WHERE JobCode= @paramProgram` – Charles Nov 05 '17 at 04:24
  • 1
    You need to take some time to understand how `sp_executesql` works. Then you'll get the full picture. Are you asking this question because the `sp_executesql` doesn't return what you expect it to? What is the _real_ problem here? Is the problem that `print @locationOutput` doesn't print what you expect? – Nick.Mc Nov 05 '17 at 04:47
  • 1
    I think the _real_ problem must be not what `print @sql` returns, it's what `print @locationOutput` returns, right? Here is a question that explains what it looks like you're trying to do. https://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable-for-sql-server – Nick.Mc Nov 05 '17 at 04:51
  • Yes, I am struggling to figure out how `SP_executesql` works. The main thing I am trying to accomplish is for` @locationOutput` to return the value from the executed sql statement, but that is not happening. – Charles Nov 05 '17 at 04:52
  • OK we might have missed each other comments but look at the link in my prior comment which has an example of what your'e trying to do - capture an output variable from SQL executed by `sp_executesql` – Nick.Mc Nov 05 '17 at 04:53
  • When I look at the example used in your link and compare it to my sp I do not see what I am missing. It looks to be setup in the same we way. I am obviously doing something wrong, but when comparing to examples like this one I am not seeing it. When I execute my sp it returns no results. But when I execute the following query it does return a value. Do you have any idea what is missing? `SELECT Location FROM Intranet.dbo.tbl_Campaigns WHERE JobCode= 'RH17009'` – Charles Nov 05 '17 at 05:14
  • They are definitely different. The linked example has four arguments to `sp_executesql`. The first two arguments are *strings*. The second two are *not strings*. In your code you call `sp_executesql` but _all four_ arguments are strings. Try removing the quotes from the last two arguments and see if it works. If you're going to use dynamic SQL it's important you understand what is and isn't a string. – Nick.Mc Nov 05 '17 at 05:26
  • Yes! That is the issue. Thank you so much – Charles Nov 05 '17 at 22:50
  • It would be awesome if you could post your result and accept it as an answer – Nick.Mc Nov 05 '17 at 23:16

1 Answers1

0

Below is the updated code that now works:

    ALTER PROCEDURE [dbo].[utility_getDatabaseLocation]
    @program as nvarchar(75),
    @locationOutput varchar(250) OUTPUT
AS

BEGIN
    DECLARE @ParmDefinition nvarchar(500);

    DECLARE @sql AS nvarchar(500)

    SET @sql = N'SELECT @paramOUTPUT = MAX(Location) FROM Intranet.dbo.tbl_Campaigns WHERE JobCode= @paramProgram';
    SET @ParmDefinition = N'@paramProgram varchar(75),@paramOUTPUT varchar(250) OUTPUT';

    EXECUTE sp_executesql   @sql,   @ParmDefinition,  @paramProgram=@program, @paramOUTPUT=@locationOutput OUTPUT

END
Charles
  • 1
  • 5