sqlcurious
asked on
how to pass multiple values in the parameters in stored proc to design reports
exec DBSP_Report_GetProjectDeta ilCost1 @ProjectLettingDateFrom='0 1/01/2006' ,@ProjectL ettingDate To='01/01/ 2011',@Div isionName= 'Division description 2,dIVISION dESCRIPTION 3'
NOT GETTING RESULTS FOR THE ATTACHED PROC WHEN I PASS TWO VALUES FOR THE PARAMETER @DIVISIONNAME
URGENT
NOT GETTING RESULTS FOR THE ATTACHED PROC WHEN I PASS TWO VALUES FOR THE PARAMETER @DIVISIONNAME
URGENT
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?
ASKER
YES I POSTED WHAT ERROR I GOT
try using any of these SET statements.
SET @SQL= @Sql + ' AND (exists(SELECT D.DivisionName FROM dbo.fneBid_SplitValue(@Div isionName, ",")))'
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%' +@Division Name+'%'', '','')) '
I just corrected the syntactical errors in these two statements.
SET @SQL= @Sql + ' AND (exists(SELECT D.DivisionName FROM dbo.fneBid_SplitValue(@Div
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%'
I just corrected the syntactical errors in these two statements.
ASKER
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%' +@Division Name+'%'', '','')) '
THIS DOESNOT WORK PROPERLY.IT ACCEPTS MULTIPLE VALUES BUT SHOWS EVERYTHING IN THE dIVISION NAME COLUMN
exec DBSP_Report_GetProjectDeta ilCost1 @ProjectLettingDateFrom='0 1/01/2006' ,@ProjectL ettingDate To='01/01/ 2011',@Div isionName= '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
THIS DOESNOT WORK PROPERLY.IT ACCEPTS MULTIPLE VALUES BUT SHOWS EVERYTHING IN THE dIVISION NAME COLUMN
exec DBSP_Report_GetProjectDeta
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
Is there any chance you can layoff using ALL CAPS as it looks like you are yelling.
ASKER
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue(''%' +@Division Name+'%'', '','')) '
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_GetProjectDeta ilCost1 @ProjectLettingDateFrom='0 1/01/2006' ,@ProjectL ettingDate To='01/01/ 2011',@Div isionName= '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
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_GetProjectDeta
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
Can you try this?
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@D ivisionNam e+','','') ) '
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@D
ASKER
For This
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@D ivisionNam e+','','') )'
I got following error message
SELECT
--P.ProjectID,
distinct P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
convert(varchar(10),p.Proj ectStartDa te,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_ItemTotalPric e As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItem_ItemTotalPri ce * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot eSurcharge 2+Q.QuoteS urcharge3) 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(Divi sion description 2,DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11,','))
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'description'.
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('+@D
I got following error message
SELECT
--P.ProjectID,
distinct P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
convert(varchar(10),p.Proj
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItem_ItemTotalPri
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot
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(Divi
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'description'.
check this
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('''+ @DivisionN ame+''','' ,'')) '
set @SQL= @Sql + ' And D.DivisionName in (select DivisionName from dbo.fneBid_SplitValue('''+
ASKER
It is giving me all the results when i execute this
exec DBSP_Report_GetProjectDeta
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
try this.
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','','')'
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',','
ASKER
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','','')'
This statement gave me the following error
Incorrect syntax near ')'.
This statement gave me the following error
Incorrect syntax near ')'.
My bad.
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT DivisionName FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
ASKER
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_GetProjectDeta ilCost1 @ProjectLettingDateFrom='0 1/01/2006' ,@ProjectL ettingDate To='01/01/ 2011',@Div isionName= 'Division description 2',DIVISION DESCRIPTION 4,DIVISION DESCRIPTION 11'
Isn't there any way to modify the proc and give the desired result
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_GetProjectDeta
Isn't there any way to modify the proc and give the desired result
As I asked, can you run this and post the result?
SELECT DivisionName FROM dbo.fneBid_SplitValue('Div ision description 2,division description 3',','
SELECT DivisionName FROM dbo.fneBid_SplitValue('Div
I mean this,
SELECT DivisionName FROM dbo.fneBid_SplitValue('Div ision description 2,division description 3',',')
SELECT DivisionName FROM dbo.fneBid_SplitValue('Div
ASKER
select divisionName from TBL_Division
-- exec DBSP_Report_GetProjectDeta
-- exec DBSP_Report_GetProjectDeta
-- ==========================
-- Create date: <01/21/2011>
-- Description: <stored procedure to find total Project Costs>
-- ==========================
ALTER PROCEDURE [dbo].[DBSP_Report_GetProj
(
--@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.Proj
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPric
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,
(QI.QuoteItem_ItemTotalPri
QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.Quot
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(@ProjectLetting
End
If @ProjectLettingDateTo Is Not Null
Begin
Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLetting
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
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('Div ision description 2,division description 3',',')
SELECT DivisionName FROM dbo.fneBid_SplitValue('Div
ASKER
The above statement says
Invalid column name 'DivisionName'.
FOLLOWING IS THE SPLIT FUNCTION CODE
-- select * from dbo.fneBid_SplitValue('123 123,123,12 3,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,DATALENG TH(@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,DATALENG TH(@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
Invalid column name 'DivisionName'.
FOLLOWING IS THE SPLIT FUNCTION CODE
-- select * from dbo.fneBid_SplitValue('123
CREATE FUNCTION [dbo].[fneBid_SplitValue](
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
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,DATALENG
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,DATALENG
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
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 + ''','',''))'
SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
ASKER
exec DBSP_Report_GetProjectDeta ilCost1 @ProjectLettingDateFrom='0 1/01/2006' ,@ProjectL ettingDate To='01/01/ 2011',@Div isionName= '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 + ''','',''))'
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 + ''','',''))'
ASKER
my bad
its showing the results
I didnot assign the value properly
thank u very much
its showing the results
I didnot assign the value properly
thank u very much
Are you still facing any issues?
ASKER
not really
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.