Mystery Value in Variable

Question

Post reply

Mystery Value in Variable

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

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

deekadelic Ten Centuries Points: 1123 More actions · Answer 1

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Incommensurables this:

--PRINT @cmd

_____________
Code for TallyGenerator

deekadelic Ten Centuries Points: 1123 More actions · Answer 3

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

Michael L John One Orange Chip Points: 27056 More actions · Answer 4

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/

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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'

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Just do it.

And see what it prints

_____________
Code for TallyGenerator

deekadelic Ten Centuries Points: 1123 More actions · Answer 8

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