August 29, 2012 at 4:23 am
Hi,
i have a report developed in Report Builder 3.0, having 3 parameters, 2 are drop down and 1 is text box,
user need to select 3 parameters, for text box parameter, user may enter like "Bike" or "20*Bike"
if Bike : output should be "Bike 1, bike 444, 2012 bike,.....
if 20*Bike : output should be "2012 Bike, 2011 Bike, 20 Bike123, 20 Bike 983,etc...
to get the above logic am using below stored procedure
create procedure dbo.STORED_PROC_NAME
(
@sstrg varchar(100)
)
as
begin
set nocount on;
--declare @strg varchar(100) = 'Catalog',
set @sstrg = '%' + REPLACE( @sstrg, '*', '%') + '%'
SELECT project_name
FROM groups
WHERE project_name like @sstrg
end
Am using stored procedure in report builder,
stored procedure query:
------------------------------------------
SELECT a,b,c
FROM groups
WHERE
a IN (SELECT value from dbo.Fn_tsp_orderhistory('''+@a+''','',''))
and
b IN ((SELECT value from dbo.Fn_tsp_orderhistory('''+@b+''','',''))
and
c LIKE (SELECT value from dbo.Fn_tsp_orderhistory(EXEC STORED_PROC_NAME
('''+@c+'''))'
-----------------------------
FUNCTION :"Fn_tsp_orderhistory is using for multi-value parameter.
My requirement is : how to use above stored procedure in below where condition ::
and c LIKE (SELECT value from dbo.Fn_tsp_orderhistory(EXEC STORED_PROC_NAME
('''+@c+'''))'
Please help,
August 29, 2012 at 6:45 am
You don't have to use a SP. you can just replace following code:
SELECT value from dbo.Fn_tsp_orderhistory(EXEC STORED_PROC_NAME
('''+@c+''')
WITH
SELECT value from dbo.Fn_tsp_orderhistory( '%' + REPLACE( @C, '*', '%') + '%')
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
August 29, 2012 at 6:49 am
Are you sure about that?
copy and paste it to SSMS...that stops right away with a syntax check, since the EXEC command cannot exist inside the parameter list.
H4K (8/29/2012)
You don't have to use a SP. you can just replace following code:SELECT value from dbo.Fn_tsp_orderhistory(EXEC STORED_PROC_NAME
('''+@c+''')
WITH
SELECT value from dbo.Fn_tsp_orderhistory( '%' + REPLACE( @C, '*', '%') + '%')
Lowell
August 29, 2012 at 7:31 am
Please, don't go step by step.
You have 3 posts very similar and it seems to me that you're not giving us the entire problem.
If we're aware of the whole context, we can give you better ideas.
PS. I'm glad you liked my solution, a simple thanks would have been appreciated.
August 31, 2012 at 4:54 am
Lowell (8/29/2012)
Are you sure about that?copy and paste it to SSMS...that stops right away with a syntax check, since the EXEC command cannot exist inside the parameter list.
To answer @lowell, I didn't check the original query with EXEC command. I just suggest an alternate solution. Thanks for pointing that out 🙂
I received and replied to the private message from the creator of this post:
I replied:
Actually there is a problem with the apostrophes you have used
try this:
Set @project_name_cond=' and (project_name LIKE
(SELECT value from dbo.Fn_tsp_orderhistory(''' + '%' + REPLACE( @original_project_name, '*', '%') + '%' + ''')'
I know the above pasted is private message, but wanted to share that maybe this issue is resolved now :unsure:. i think as no reply came.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply