Post reply

Mystery Value in Variable

  • October 19, 2016 at 2:07 pm

    #1907538

    I tried that, sure it would work, but for whatever reason, no records were added to the table. Here's the code I used:

    ALTER PROCEDURE [dbo].[ImportJobRevenue]

    @DataSourceJobRevenue AS varchar(100)

    AS

    SET NOCOUNT ON

    DELETE FROM JobRevenueStage

    DECLARE @cmd varchar(100)

    SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '

    --

    INSERT INTO DEEKTEMP(DataSourceJobRevenue)

    VALUES(@cmd)

    --

    --PRINT @cmd

    EXECUTE (@cmd)

    I chose to insert the value of @cmd into the table instead of @DataSourceJobRevenue because I knew for a fact that @cmd would have a string value, seeing as how we see it initialized in the line SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '

    But when I then go and do a SELECT ALL * FROM DEEKTEMP;, I get an empty set. Shouldn't the variable @cmd at least have added "'BULK INSERT JobRevenueStage FROM '" if nothing else? Incidentally, I did also try this with a static value in place of the variable (SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + 'STATIC' + ''' '), and got the same result. Which seems really odd to me.

    Why is @cmd not showing me a value?

  • October 19, 2016 at 2:10 pm

    #1907539

    Incommensurables this:

    --PRINT @cmd

    _____________
    Code for TallyGenerator

  • October 19, 2016 at 2:17 pm

    #1907544

    Shouldn't the statement --PRINT @cmd be irrelevant, seeing as how the code is commented out?

  • October 19, 2016 at 2:27 pm

    #1907545

    I chose to insert the value of @cmd into the table instead of @DataSourceJobRevenue because I knew for a fact that @cmd would have a string value, seeing as how we see it

    initialized in the line SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '

    Fact? If @DataSourceJobRevenue is NULL, then @cmd will be null.

    Are you sure you are getting no records, or NULLS?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • October 19, 2016 at 2:29 pm

    #1907546

    This has gotten a bit meandering, so let me see if I can summarize what's happened so far before I suggest anything.

    You have a stored procedure for which you want to see the parameter values being passed.

    You have altered the stored procedure so that when it runs it inserts the value of the @cmd variable into a table.

    You do not see anything in that table yet.

    Trying the simplest explanation first, has the stored procedure actually been executed since you altered it to log the value of @cmd?

    So far I see no indication of anything having been run aside from the ALTER scripts.

    Cheers!

  • October 19, 2016 at 2:47 pm

    #1907550

    This: EXECUTE (@cmd)

    executes the sql command contained in the string. That string is a BULK INSERT sql command that will insert the data from the file whose name is passed in to the stored procedure in the parameter @DataSourceJobRevenue. For example, if this is how the procedure is invoked:

    exec [dbo].[ImportJobRevenue] 'd:\ImportFiles\JobRevenue_20160630'

    The following SQL command will be executed:

    BULK INSERT JobRevenueStage FROM 'd:\ImportFiles\JobRevenue_20160630'

  • October 19, 2016 at 2:51 pm

    #1907551

    Just do it.

    And see what it prints

    _____________
    Code for TallyGenerator

  • October 21, 2016 at 2:45 pm

    #1908117

    That was the problem- you nailed, Jacob Wilkins. I was updating the SP, but I wasn't actually executing it! What an amateur move.

    It's been a few years since I programmed in SQL; and I'm still wiping off the dust.

    Thank you

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply