June 25, 2009 at 7:33 am
I have this stored procedure below that accepts two parameters - districtId and year. In the webform, the user can select more than 1 district. So, how would I change this procedure so it returns the data for multiple districts that was selected.
@DistrictId int,
@year int
AS
BEGIN
CREATE TABLE #t
(
dealerid varchar(50),
dealername varchar(255),
district varchar(255),
tmname varchar(255),
packagename varchar(100),
totalinvestment varchar(100),
coopclaim varchar(10),
submissiondate datetime
)
INSERT INTO #t
SELECTcss.dealerid,
c.dealer_name,
d.DISTRICT_NAME,
t.TMName,
NULL,
NULL,
c.coop_claim,
css.submission_date
FROMcurrent_signup_submission css
INNER JOIN current_dealers c ON c.dealerid = css.dealerid AND css.year = c.year
INNER JOIN LennoxCAPDealers l ON l.DealerNo = c.dealerid
INNER JOIN district d ON d.DISTRICT_NO = l.DistNameID
LEFT JOIN TM t ON t.ID = c.TMId
WHEREl.DistNameID = @DistrictId
ANDc.Year = @year
UPDATE#t
SETpackagename = (SELECT DISTINCT package_name
FROM current_package_details cpd
INNER JOIN current_signup_submission css ON css.dealerid = cpd.dealerid AND css.year = cpd.year
WHEREcpd.year = @year
ANDcpd.dealerid = #t.dealerid)
UPDATE#t
SETtotalinvestment = (SELECT DISTINCT total_investment
FROM current_package_details cpd
INNER JOIN current_signup_submission css ON css.dealerid = cpd.dealerid AND css.year = cpd.year
WHEREcpd.year = @year
ANDcpd.dealerid = #t.dealerid)
SELECT * FROM #t
DROP TABLE #t
END
June 25, 2009 at 8:07 am
Assuming your form was sending through multiple values in the correct format '1, 2, 3' etc. Change your variable from an int to string. In your where clause, update to "WHERE l.DistNameID in (@DistrictId)"
Should work.
June 25, 2009 at 11:52 am
ALTER PROCEDURE SA_STPR_Report_CAPSignup_ByDistrict
@DistrictId varchar(255),
@year int
AS
BEGIN
CREATE TABLE #t
(
dealerid varchar(50),
dealername varchar(255),
district varchar(255),
tmname varchar(255),
packagename varchar(100),
totalinvestment varchar(100),
coopclaim varchar(10),
submissiondate datetime
)
INSERT INTO #t
SELECTcss.dealerid,
c.dealer_name,
d.DISTRICT_NAME,
t.TMName,
NULL,
NULL,
c.coop_claim,
css.submission_date
FROMcurrent_signup_submission css
INNER JOIN current_dealers c ON c.dealerid = css.dealerid AND css.year = c.year
INNER JOIN LennoxCAPDealers l ON l.DealerNo = c.dealerid
INNER JOIN district d ON d.DISTRICT_NO = l.DistNameID
LEFT JOIN TM t ON t.ID = c.TMId
WHERECAST(l.DistNameID AS varchar(50)) IN (@DistrictId)
ANDc.Year = @year
UPDATE#t
SETpackagename = (SELECT DISTINCT package_name
FROM current_package_details cpd
INNER JOIN current_signup_submission css ON css.dealerid = cpd.dealerid AND css.year = cpd.year
WHEREcpd.year = @year
ANDcpd.dealerid = #t.dealerid)
UPDATE#t
SETtotalinvestment = (SELECT DISTINCT total_investment
FROM current_package_details cpd
INNER JOIN current_signup_submission css ON css.dealerid = cpd.dealerid AND css.year = cpd.year
WHEREcpd.year = @year
ANDcpd.dealerid = #t.dealerid)
SELECT * FROM #t
DROP TABLE #t
END
When I run this procedure, I don't get any data. Am I doing something wrong here?
SA_STPR_Report_CAPSignup_ByDistrict '11, 12, 15, 16, 18', 2008
June 25, 2009 at 1:26 pm
Hmm, question is how do you take a string and put it in the where clause against 'int' not too sure.
June 25, 2009 at 8:36 pm
I got a workaround to this but its not a good way but it works. But I would still like to work on this.
I stored all the selected districts that the user selects in the run time to a temp table, called the above procedure by storing the district values from this temp table to a cursor which is then used in the procedure to loop through the above stored procedure. Once the result was obtained, I deleted the data from this temp table which stores the districts. This works.
June 26, 2009 at 12:29 pm
Not sure what you meant by "Hmm, question is how do you take a string and put it in the where clause against 'int' not too sure. "
You can compare integers and strings:
create table #temp1 (String varchar(1))
create table #temp2 (Integer INT)
Insert into #temp1 values('1')
Insert into #temp2 values(1)
select * from #temp1 a
inner join #temp2 b on a.string = b.integer
select * from #temp1 a, #temp2 b
where a.string = b.integer
Both the select statements return the join.
June 26, 2009 at 3:39 pm
Using multiple values requires xyz in (1, 2, 3) etc. If you pass 1, 2, 3 as a variable it is a string but you can't then say where xyz in (@variable). xyz is an int and it can't convert it.
June 29, 2009 at 12:46 am
Basically from what I gather from your SP is that you are wanting to pass an array of intergers, and to do it, you are placing them into a string. If I gather this correctly, in this method, you will need to parse them back out into individual INT values as a table variable or temp table before you can use them again as INT's.
Depending on the flexibility of your calling procedure, an alternative is to put the list of integers into a temp table before you call the stored procedure you have listed. For example, in the calling SP, you could specify:
Create Table #DistrictIDs (DistrictID INT)
INSERT INTO #DistrictIDs Select DistrictID from ...
EXEC SA_STPR_Report_CAPSignup_ByDistrict 2008
You won't have #DistrictIDs as a parameter. But since the temp table #DistrictIDs still exists in scope when SA_STPR_Report_CAPSignup_ByDistrict is called, the table #DistrictIDs will be available for use by your SA_STPR_Report_CAPSignup_ByDistrict SP. This is my prefered method of passing arrays of data to SP's.
June 29, 2009 at 7:50 am
Hi Carleton,
I have done the same way as you have suggested but the only difference is I have created a temp table which first stores all the districtid that the user selects in the webform, then it calls the SA_STPR_Report_CAPSignup_ByDistrict to display the data for the districts selected.
I think creating a table in memory is better than creating a physical table but I have a question. The way I am storing the districtId into a temp table is using a while loop in my webform codebehind that gets the districtId from a listbox one by one and inserts them to this temp table. But if we execute the second procedure in the same stored procedure, would it display data for all the districts that have been passed as parameters? What I mean to say is wouldn't this procedure work like first it inserts the selected districtId into temp table, execute the second stored procedure and then stores the next one, executes the procedure and so on. It wouldn't inserts all the districtId into temp table and then execute the second procedure right? I think thats the reason why I created a temp table on the database, called one sp just to insert the selected districts into this table, then the next stored procedure would display the result based on the districtId from this temp table..once its done displaying using the districtId from this temp table, then it would empty the temp table so we can use that again.
Pardon me if I have confused you here.
June 29, 2009 at 9:12 am
Hi,
Assuming i have understood what you are trying to do correcty. I would suggest passing the Sproc a comma delimetered list of integers which you can seperate out and dump into a tempory table.
Once in a temp table you can query the values how you like. If you use the code below to seperate the values from a parameter and place them into a temp table ( you will need to edit where appropriate ) then hopefully this will point you in the right direction.
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'
SET NOCOUNT ON
--===== Create a table to store the results in
DECLARE @Elements TABLE ( Number INT IDENTITY(1,1),
--Order it appears in original string
Value VARCHAR(8000)
--The string value of the element
)
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = ','+@Parameter +','
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements (Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
SELECT * FROM @Elements
This will give you the table @Elements with a column called number which will be a count, and a column called value which will be the value in between the columns.
This code was taken from an article by jeff moden on tally tables. if you haven't read it its worth a look anyway.
Let me know if this is any help.
Thanks
Matt
June 29, 2009 at 9:17 am
sorry...value between the commas i meant...
June 29, 2009 at 10:27 am
Hi Matt,
Thanks a bunch for the script. That worked like a charm. I tried the script and it worked great. Now, I don't have to worry about creating a temp table (a physical table) in the database. Awesome!!
Sid
June 30, 2009 at 2:06 am
No problems. I have done several things like this recently so it was code i have used a lot. You will probably find it the same.
If you haven't already read the article on tally tables have a look for it. There is some useful ways of getting around while loops in there. Clever stuff!
Enjoy 😀
Matt
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply