[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

Multi comma seprated Values in a SP parameter.

I have a program that stores values as comma sperated for SToreID. What I need to get is the SP to run for each value in the string.  So if the StoreID was 10,21,31 it would run for store 10 store 21 and store 31.  Below is the stored procedure I have now can you please help me to modify it to to the above request.

CREATE PROCEDURE [dbo].[Tender_Net_Sales_stone]
@begin_sale_date datetime,
@end_sale_date datetime,
@begin_store_id int


AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE @inclusive_tax float
DECLARE @exclusive_tax float
DECLARE @total_gross_sales float
DECLARE @total_tax float
DECLARE @net_to_gross_ratio float
DECLARE @Store_NAME varchar(24)
--DECLARE @overcharge float

SET @Store_name=
(
SELECT t1.NAME as StoreNAME
from ibersql..store t1
where storeid=@begin_store_id)

SET @total_gross_sales =
(
SELECT SUM(Amount) AS tgs
FROM         ibersql..HstGndTender
WHERE    
(FKStoreId = @begin_store_id )
AND
(DateOfBusiness BETWEEN @begin_sale_date AND @end_sale_date)
AND (Type = 1)
GROUP BY Type
)
IF @total_gross_sales IS NULL
SET @total_gross_sales = 0

/*
SET @overcharge =
(
SELECT SUM(Amount) AS tgs
FROM         ibersql..HstGndSale
WHERE    
(FKStoreId BETWEEN @begin_store_id AND @end_store_id)
AND
(DateOfBusiness BETWEEN @begin_sale_date AND @end_sale_date)
AND (Type = 26)
GROUP BY Type
)
IF @overcharge IS NULL
SET @overcharge = 0
*/

SET @inclusive_tax =
(
SELECT SUM(Amount) AS tgs
FROM         ibersql..HstGndSale
WHERE
(FKStoreId = @begin_store_id )
AND
(DateOfBusiness BETWEEN @begin_sale_date AND @end_sale_date)
AND (Type = 33)
GROUP BY Type
)
IF @inclusive_tax IS NULL
SET @inclusive_tax = 0

SET @exclusive_tax =
(
SELECT     SUM(Amount) AS total_gross_sales
FROM         ibersql..HstGndSale
WHERE
(FKStoreId = @begin_store_id )
AND
(DateOfBusiness BETWEEN @begin_sale_date AND @end_sale_date)
AND (ibersql..HstGndSale.Type = 2)
GROUP BY ibersql..HstGndSale.Type
)
IF @exclusive_tax IS NULL
SET @exclusive_tax = 0

SET @total_tax = @exclusive_tax + @inclusive_tax

SET @net_to_gross_ratio = (@total_gross_sales - @total_tax)/@total_gross_sales;
IF @total_gross_sales = 0
SET @total_gross_sales = 1

SELECT
@Store_name as 'Store_Name',
 @begin_store_id as 'Store',
@begin_sale_date as 'Start Date',
 @end_sale_date       as 'End Date',  
 ibersql..Tender.Name AS 'Tender Type',
ROUND(SUM([Amount]),3) AS 'Gross Sales',
ROUND(SUM([Amount]) * @net_to_gross_ratio, 3) AS 'Net Sales',
COUNT(*) AS 'Number of Transactions',
CASE
  WHEN COUNT(*) = 0 THEN ROUND((SUM([Amount]) * @net_to_gross_ratio)/(1), 3)
  ELSE ROUND((SUM([Amount]) * @net_to_gross_ratio)/(COUNT(*)), 3)
END AS 'Average Amt Per Transaction',
@exclusive_tax AS 'Exclusive Tax',
@inclusive_tax AS 'Inclusive Tax'
,Type,TypeId
FROM         ibersql..HstGndTender LEFT OUTER JOIN
                      ibersql..Tender ON TypeId = ibersql..Tender.TenderId
WHERE
(FKStoreId = @begin_store_id )
AND
(DateOfBusiness BETWEEN @begin_sale_date AND @end_sale_date)
AND Type = 1
GROUP BY Type, TypeId, ibersql..Tender.Name
ORDER BY typeid

END
GO
0
stone99
Asked:
stone99
  • 4
  • 4
  • 3
  • +1
1 Solution
 
leonstrykerCommented:
>I have a program that stores values as comma sperated for SToreID.

This is usually handled better on the front end, IMO. By having an application pass these values to the store procedure one at a time. The other way would be to setup 2 procedures. One that would actually process the data and outputs the result to a temp table, and the send which feeds the first each value as a parameter and outputs the final result from the temp table.
0
 
InteqamCommented:
use dynamic SQL with Storeid IN (10,21,31)
0
 
InteqamCommented:
oh, it is even simpler, you need a new SP that does some string manupulation.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
InteqamCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
stone99Author Commented:
Ok I see how to split the CSV but how do I apply this to where each ID will be ran against the SP.
0
 
InteqamCommented:
you want the same SP to run for all, or a new SP to call the old SP with one of the IDs each time?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can use a dynamic sql to loop thru the arguments

declare @rows int
declare @StoreID int
declare @nSQL nvarchar(1000)
declare @tab table ( i int identity, StoreID int )
insert into @tab
SELECT dbo.fnCSVString (...)

select @rows = @@ROWCOUNT
SET @nSQL = 'dbo.Tender_Net_Sales_stone  '''+cast(@begin_sale_date as varchar) +''','''+cast(@begin_store_id as varchar)+''''
WHILE @Rows > 0
BEGIN
     SELECT @StoreID = StoreID
     FROM @Tab WHERE i = @rows
 
    SET @nsql =@nsql +','+ cast(@storeid as varchar)
    exec sp_executesql @nsql
     
     SET @Rows = @Rows -1
END
0
 
stone99Author Commented:
Ok here is the error I get to you dynamic SQL

Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
Msg 137, Level 15, State 2, Line 9
Must declare the variable '@begin_sale_date'.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @begin_sale_date datetime,
        @end_sale_date datetime

declare @rows int
declare @StoreID int
declare @nSQL nvarchar(1000)
declare @tab table ( i int identity, StoreID int )
insert into @tab
SELECT dbo.fnCSVString () --------- you need to put the arguments here also check the function name, is it already created

set @begin_sale_date  = '12/12/2005'
set @end_sale_date    = '12/12/2006'

select @rows = @@ROWCOUNT
SET @nSQL = 'dbo.Tender_Net_Sales_stone  '''+cast(@begin_sale_date as varchar) +''','''+cast(@end_sale_date as varchar)+''''
WHILE @Rows > 0
BEGIN
     SELECT @StoreID = StoreID
     FROM @Tab WHERE i = @rows
 
    SET @nsql =@nsql +','+ cast(@storeid as varchar)
    exec sp_executesql @nsql
     
     SET @Rows = @Rows -1
END
0
 
stone99Author Commented:
Not sure why I get this but I put the arguments this is what I get.  Also is the function call suppose to be select * from dbo.fncsvstring('10,12,13,14')  because I cannot get it to run with your info.  If yours is correct please shot me a example.

Msg 313, Level 16, State 3, Line 8
An insufficient number of arguments were supplied for the procedure or function dbo.fnCSVString.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if you are using the same function , then u missed one argument


select * from dbo.fncsvstring('10,12,13,14',',')
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now