March 14, 2016 at 3:53 pm
Hi,
The stored procedure below wanted to spend the parameters of the select. At this time only me is the stored procedure is to return only the first line of select, but I wanted the other as well.
DECLARE @sku varchar(30), @Price Money,@Discount real
SELECT @sku=sku,@Price=Price, @discount=Discount
FROM tbProducts
WHERE sku in ('XPTO','XPTO1')
exec spPriceHistoric @sku,@Price,@Discount
Thanks
March 15, 2016 at 2:58 am
I don't understand the request, can you be clearer please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2016 at 4:59 am
Hi,
I want the stored procedure receives a loop in the query
Thanks
March 15, 2016 at 5:13 am
A stored procedure can't "receive" a loop. It can loop, but it many cases it isn't necessary. Perhaps you could explain what it is that you're trying to do.
March 15, 2016 at 5:57 am
I believe the request is to split the @sku into a table , via dbo.DelimitedSplit8K for example:
right now he's passing a string that happens to have a comma in it.
WHERE sku in ('XPTO','XPTO1')
your procedure spPriceHistoric must use the splitter function to convert your string into a table of strings.
SELECT sku,Price, Discount
FROM tbProducts
WHERE sku in (SELECT Item FROM dbo.DelimitedSplit8K(@sku,',') )
the example i mentionsed here is found in this article:
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
Lowell
March 15, 2016 at 6:17 am
Lowell (3/15/2016)
I believe the request is to split the @sku into a table , via dbo.DelimitedSplit8K for example:right now he's passing a string that happens to have a comma in it.
WHERE sku in ('XPTO','XPTO1')
your procedure spPriceHistoric must use the splitter function to convert your string into a table of strings.
SELECT sku,Price, Discount
FROM tbProducts
WHERE sku in (SELECT Item FROM dbo.DelimitedSplit8K(@sku,',') )
the example i mentionsed here is found in this article:
That might very well be it, but I'll admit I wouldn't have gotten that from the OP. I think it's time for amadeu to weigh in and clarify the question.
March 15, 2016 at 9:38 am
I have a table with the following product sku fields, price and discount, and wanted to create a historical table of the products that whenever alter prices or discount reflects the history table.
The stored procedure analyze the historical table and if there is a change in price or discount, updates the table.
They were able to see what I want?
April 1, 2016 at 11:00 am
Hi,
The stored procedure below will select price changes due for example to a promotion, through the function.
The stored procedure works only if only for a record, whenever I run spPriceH runs only once, when you should take the number of rows that the function has!
How do I solve?
Alter PROCEDURE [dbo].[spPriceH]
AS
declare @brandID smallint @sku nvarchar(18), @Price Money,@Discount real
select @sku=sku,@Price=Price,@Discount=Discount,@brandID=brandID
from [fnExpsku1](0)
begin TRY
DECLARE
@brandIDT smallint, @skuT nvarchar(18), @PriceT Money,@DiscountT real, @statusT CHAR(1), @DateUpdateT SMALLDATETIME
SELECT @brandT=@brandID, @skuT=sku, @PriceT=Price, @DiscountT=Discount, @StatusT=Status, @DateUpdateT=DateUpdate
FROM PriceUpdateSku
WHERE brandID=@brandID and sku=@sku
IF @skuT IS NULL
INSERT INTO PriceUpdateSku (BrandID,sku,Price,Deiscount,Status,DateUpdate)
VALUES (@BrandID, @Sku, @Price,@Discount, 'S', GETDATE())
BEGIN
IF @BrandIDT<>@BrandID or @skuT<>@sku or @PriceT<>@Price or @DiscountT<>@Discount
UPDATE PriceUpdateSku set Price=@Price, Discount=@Discount, Status='S', DateUpdate=GETDATE()
WHERE brandID=@brandID and Sku=@Sku
ELSE
update PriceUpdateSku set Status='S' where brandID=@brandID and Sku=@Sku
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply