Storing variable values, etc.

Question

Post reply

Storing variable values, etc.

Slick84

SSCertifiable

Points: 5602
More actions
July 22, 2009 at 8:05 am

#223869

I need some help in trying to figure this out. I am unsure of how to store the counts of all the items I have updated and inserted into my tables. We currently have a stored procedure called AddItemCount where I need to pass these values once I have them stored.
Below is one of my sample update statements and the AddItemCount stored procedure. Can some one help me write this so I'm storing the counts that I updated in my UPDATE statement and then passing them onto the AddItemCount stored procedure.
--My update statement
UPDATE dbo.tblProductSizeWidthColor
SETfbitDeleted = 0
WHEREfintProductSizeWidthColorId IN
(
SELECTpswc.fintProductSizeWidthColorId
FROM dbo.tblProductSizeWidthColor pswc
INNER JOIN dbo.tblRegularItems ri
ONri.ItemNo=pswc.fstrLongSku and ri.EdpNo=pswc.fintEcometryId
WHEREpswc.fbitDeleted = 1 AND ri.Price > 0 AND ri.ItemNo LIKE '%FB%' AND ri.ItemStatus = 'A1'
)
--Below is the addItemCount stored procedure
SET @value = ISNULL(@value,0)
INSERT INTO [ItemCounts]
([ApplicationId]
,[Name]
,[Value]
,[CreatedOn])
VALUES
(@applicationId
,@name
,@value
,getdate())
A collegue of mine told me I could call the above sproc like below:
dbo.AddItemCount 26, 'Total Products Activated', 1700
Above the first is the sproc name, application id, description and then the number of activated products.
If you require more information, please let me know.
Thanks,
S
--
:hehe:

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

General frame work:

update tablename set

somecolumn = @somevalue;

set @value = @@rowcount -- capture how many rows were updated;

exec dbo.insertcounts 26,'description',@value;

Does this hint help?

Slick84 SSCertifiable Points: 5602 More actions · Answer 2

Lynn,

Thanks for the hint it does help. Would it be too much to ask for if you could do this within my update statement ? Or would you need more information? I'm not very comfortable with variables...they almost even intimidate me for some reason.

Anyhow, if you dont have enough information to do the above, can you tell me what the @value <--- is? Where am I getting it from? You can probably see from my questions that my basic knowledge about variables is limited.

Thanks,

S

--
:hehe:

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

okay,

declare @value int;

update sometable set

somecolume = somevalue;

set @value = @@rowcont;

exec dbo.InsertRowCount 26,'Description',@value;

Help a little more?

As to writing your code for you, no. You give it a go and show us what you come up with. If there is a problem with your code, we'll let you know so you can fix it before you actually use it in a production environment.

Slick84 SSCertifiable Points: 5602 More actions · Answer 4

lol...

Thanks! I will try to implement that and provide an update here.

Btw, I know a lot of people come here to get their code written, and I probably sounded like one.. that sproc up there is alot more complicated than I posted. Of course just putting this out there so I'm not listed as one of 'those'.

Anyhow, appreciate your help.

--
:hehe:

Slick84 SSCertifiable Points: 5602 More actions · Answer 5

How about the below:

DECLARE @value int

UPDATECatalogFeedTestDB.dbo.tblProductSizeWidthColor

SETfbitDeleted = 0

WHEREfintProductSizeWidthColorId IN

(

SELECTpswc.fintProductSizeWidthColorId

FROMCatalogFeedTestDB.DBO.tblProductSizeWidthColor pswc

INNER JOINBMBSTAGING.dbo.tblRegularItems ri

ONri.ItemNo=pswc.fstrLongSku and ri.EdpNo=pswc.fintEcometryId

WHEREpswc.fbitDeleted = 1 AND ri.Price > 0 --AND pswc.fstrEcometryStatus = 'A1' AND pswc.fstrLongsku LIKE '%FB%'

)

SET @value = @@rowcount

EXEC SystemsDashboard.dbo.AddItemCount 26, 'Total Products Activated', @value

Is that the right way of doing it?

--
:hehe:

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

Looks good to me. Test it in a test environment first just to be sure.

Slick84 SSCertifiable Points: 5602 More actions · Answer 7

can I do a select against the @value to see what value its giving me after the update has been executed? in my example the @value should be 222 rows.. how can I check?

--
:hehe:

Slick84 SSCertifiable Points: 5602 More actions · Answer 8

Slick84 (7/22/2009)
can I do a select against the @value to see what value its giving me after the update has been executed? in my example the @value should be 222 rows.. how can I check?

Okay I just used:

PRINT @value and it told me the number stored within that variable.

Moving onto a more advanced question regarding the same issue.

So now I have multiple updates/inserts. Now that I know how to store values in the variable for that update statement, how would I keep a count of all my updates/inserts and then when I run my :

exec dbo.AddItemCount 26, 'Total Products Activated', @value

I want it to be a total of all the inserts/updates. Any more hints ? Please...

--
:hehe:

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

Hint:

declare @value int;

update sometable set

somecolumn = somevalue;

set @value = @@rowcount;

insert into sometable

select * from someothertable;

set @value = @value + @@rowcount;

exec dbo.InsertRowCounts 26,'Description',@value;

How's that for a hint?

Slick84 SSCertifiable Points: 5602 More actions · Answer 10

Slick84

SSCertifiable

Points: 5602

July 22, 2009 at 9:09 am

#1028394

Thank you so much!

--
:hehe: