Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to pass multiple values in the parameters in stored proc to design reports

Posted on 2011-02-18
25
Medium Priority
?
472 Views
Last Modified: 2012-05-11
exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,dIVISION dESCRIPTION 3'


NOT GETTING RESULTS FOR THE ATTACHED PROC WHEN I PASS TWO VALUES FOR THE PARAMETER @DIVISIONNAME

URGENT
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
25 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34928947
In your releated question also, you have same problem and everyone suggested to split the comma separated values to a tabular format. Are you still facing any issues?
0
 

Author Comment

by:sqlcurious
ID: 34928975
YES I POSTED WHAT ERROR I GOT
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34929044
try using any of these SET statements.

SET @SQL= @Sql + ' AND (exists(SELECT D.DivisionName FROM dbo.fneBid_SplitValue(@DivisionName,",")))'

set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'','','')) '

I just corrected the syntactical errors in these two statements.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:sqlcurious
ID: 34929144
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'','','')) '

THIS DOESNOT WORK PROPERLY.IT ACCEPTS MULTIPLE VALUES BUT SHOWS EVERYTHING IN THE dIVISION NAME COLUMN

exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,DIVISION DESCRIPTION 4'

FOR THE ABOVE COMMAND IT SHOULD SHOW RECORDS THAT CONTAINS ONLY THOSE DIVISIONS BUT NOW IT SHOWS ALL THE DIVISION NAMES LIKE DIVISION DESCRIPTION 3......WHICH I AM NOT LOOKING FOR
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34931381
Is there any chance you can layoff using ALL CAPS as it looks like you are yelling.
0
 

Author Comment

by:sqlcurious
ID: 34931524
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'+@DivisionName+'%'','','')) '

This doesnot work properly.I wanted to display the names of divisions that has been passed in the @DivisionName parameter.It gives me all the records in the 'DivisionName' column

exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,DIVISION DESCRIPTION 4'

For the above procedure ,Under the 'DivisionName' column ,I should get the result  'Division Description 2 and Division Description 4.But It gives me all the records in the ' DivisionName ' column, which i dont want
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34932645
Can you try this?
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@DivisionName+','','')) '
0
 

Author Comment

by:sqlcurious
ID: 34933524
For This
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@DivisionName+','',''))'  

I got following error message

SELECT
--P.ProjectID,
distinct P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
 convert(varchar(10),p.ProjectStartDate,110)as ProjectStartDate,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight

FROM TBL_EBID_Project P

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner  join TBL_Division D ON D.CreatedBy = Q.QuoteCreatedBy
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID And p.projectstartdate >='Jan  1 2006 12:00AM' And p.projectstartdate <='Jan  1 2011 12:00AM' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(Division description 2,DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11,','))

Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'description'.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34934884
check this
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('''+@DivisionName+''','','')) '
0
 

Author Comment

by:sqlcurious
ID: 34938107

It is giving me all the  results  when i execute this

exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11'

When I execute the above command ,It should give me particularly  only results that has DivisionName
Division Description 2,
Division Description 4  and
Division Description 11

Instead of all



0
 
LVL 41

Expert Comment

by:Sharath
ID: 34938386
try this.

SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','','')'

Open in new window

if you still get all the records, post the result of this query.
SELECT DivisionName FROM dbo.fneBid_SplitValue('Division description 2,division description 3',','

Open in new window

0
 

Author Comment

by:sqlcurious
ID: 34938445
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','','')'

This statement gave me the following error

Incorrect syntax near ')'.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34938457
My bad.

SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
0
 

Author Comment

by:sqlcurious
ID: 34939205
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'

Still It gives me the all results when i execute proc after using above statement instead of giving just
division description 2
division description 4
division description 11

when i execute following

exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2',DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11'



Isn't there any way to modify the proc and give the desired result
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34939349
As I asked, can you run this and post the result?


SELECT DivisionName FROM dbo.fneBid_SplitValue('Division description 2,division description 3',','
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34939352
I mean this,


SELECT DivisionName FROM dbo.fneBid_SplitValue('Division description 2,division description 3',',')
0
 

Author Comment

by:sqlcurious
ID: 34939504


select divisionName from TBL_Division
-- exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11'
-- exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='',@ProjectLettingDateTo=''
-- =============================================
-- Create date: <01/21/2011>
-- Description:      <stored procedure to find total Project Costs>
-- =============================================
ALTER    PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost1]
(      
      --@projectID int =null,
      @ProjectNumber VARCHAR(50)=Null,
      @ProjectControlNumber VARCHAR(50)= Null,
      @ProjectLettingDateFrom DATETIME,
      @ProjectLettingDateTo DATETIME,
      @ProjectName varchar(50) = Null,
      @DivisionName VARCHAR(250) = Null
)      
AS      
begin
DECLARE @SQL VARCHAR(8000)
--declare @param  varchar(50)
 
 

  set @SQL= 'SELECT
--P.ProjectID,
distinct P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
 convert(varchar(10),p.ProjectStartDate,110)as ProjectStartDate,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight

FROM TBL_EBID_Project P

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner  join TBL_Division D ON D.CreatedBy = Q.QuoteCreatedBy
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID'

if @projectname  <> ''
begin
 set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%'''
end

If @ProjectLettingDateFrom Is Not Null
      Begin
            Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLettingDateFrom As Varchar(30))+''''
      End

If @ProjectLettingDateTo Is Not Null
      Begin
            Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLettingDateTo As Varchar(30))+''''
      End

if @DivisionName <> ''


 
 begin
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
end

print @sql
exec (@sql)

END


following is the results for division column only.You can see only three values have been passed but why still showing all the reslults?



Division Description11
Division Description11
Division Description 2
Division Description 2
Division Description 4
Division Description 4
Division Description 5
Division Description 5
Division Description 12345
Division Description 12345
 Description
 Description
aaaa Division description division description div
aaaa Division description division description div
description1211
description1211
Division New
Division New
ffgdf
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
Division Description11
Division Description 2
Division Description 4
Division Description 5
Division Description 12345
 Description
aaaa Division description division description div
description1211
Division New
ffgdf
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34940844
I want to see the code of the function. Can you read my comments? Run this statement and post the result.

SELECT DivisionName FROM dbo.fneBid_SplitValue('Division description 2,division description 3',',')
0
 

Author Comment

by:sqlcurious
ID: 34943591
The above statement says
Invalid column name 'DivisionName'.


FOLLOWING IS THE SPLIT FUNCTION CODE



-- select * from dbo.fneBid_SplitValue('123123,123,123,123,123,123',',')  
CREATE FUNCTION [dbo].[fneBid_SplitValue](@Text varchar(Max), @Delimeter varchar(1) = ' ')        
RETURNS @ReturnValue TABLE (ID int, Item varchar(Max))        
AS        
BEGIN        
 DECLARE @ID int,        
  @Item varchar(Max),        
  @cont bit,        
  @strik int,        
  @Delimlength int        
       
 IF @Delimeter = null        
 BEGIN        
  SET @Delimeter = ' '        
 END        
 --initialize ID with 0        
 SET @ID = 0        
 SET @Text = LTrim(RTrim(@Text))        
 SET @Delimlength = DATALENGTH(@Delimeter)        
 SET @cont = 1        
       
 IF NOT ((@Delimlength = 0) or (@Delimeter = 'Empty'))        
  BEGIN        
   WHILE @cont = 1        
   BEGIN        
   --If you can find the delimiter in the text, retrieve the first element and        
   --insert it with its index into the return table.        
   IF CHARINDEX(@Delimeter, @Text)>0        
    BEGIN        
     SET @Item = SUBSTRING(@Text,1, CHARINDEX(@Delimeter,@Text)-1)        
     BEGIN        
      INSERT @ReturnValue (ID, Item)        
      VALUES (@ID, @Item)        
     END        
     --Increment the index and loop.        
     SET @strik = DATALENGTH(@Item) + @Delimlength        
     SET @ID = @ID + 1        
     SET @Text = LTrim(Right(@Text,DATALENGTH(@Text) - @strik))        
    END        
   ELSE        
   BEGIN        
   --If you can’t find the delimiter in the text, @Text is the last value in        
   --@ReturnValue.        
    SET @Item = @Text        
    BEGIN        
     INSERT @ReturnValue (ID, Item)        
     VALUES (@ID, @Item)        
    END        
   --Exit the WHILE loop.        
    SET @cont = 0        
   END        
  END        
 END        
 ELSE        
 BEGIN        
  WHILE @cont=1        
  BEGIN        
  --If the delimiter is an empty string, check for remaining text        
  --instead of a delimiter. Insert the first character into the        
  --retArray table. Trim the character from the front of the string.        
  --Increment the index and loop.        
   IF DATALENGTH(@Text)>1        
    BEGIN        
     SET @Item = SUBSTRING(@Text,1,1)        
     BEGIN        
     INSERT @ReturnValue (ID, Item)        
     VALUES (@ID, @Item)        
     END        
     SET @ID = @ID+1        
     SET @Text = SUBSTRING(@Text,2,DATALENGTH(@Text)-1)        
    END        
   ELSE        
    BEGIN        
    --One character remains.        
    --Insert the character, and exit the WHILE loop.        
    INSERT @ReturnValue (ID, Item)        
    VALUES (@ID, @Text)        
    SET @cont = 0        
    END        
  END        
 END        
       
 RETURN        
END  
GO
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34943776
Thats the problem. Thats why I asked function code. try this.

SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
0
 

Author Comment

by:sqlcurious
ID: 34944266
exec DBSP_Report_GetProjectDetailCost1 @ProjectLettingDateFrom='01/01/2006',@ProjectLettingDateTo='01/01/2011',@DivisionName='Division description 2,DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11'

it executed the above statement.I have passed 3 values in the @DivisionName parameter.It showed only 2
ie

Division Description 2 and Division Description 3

My Question is:  Suppose i have to pass 20 values in the @DivisionName then ,the following statement is not giving the exact results though it works partially


SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
0
 

Author Comment

by:sqlcurious
ID: 34944345
my bad
its showing the results
I didnot assign the value properly

thank u very much
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34945649
Are you still facing any issues?
0
 

Author Comment

by:sqlcurious
ID: 34953467
not really
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34962170
Then could you close this question?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question