Stored procedure in a function

Question

Post reply

Stored procedure in a function

Minnu

SSCertifiable

Points: 6451
More actions
August 29, 2012 at 4:23 am

#393043

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,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

H4K Hall of Fame Points: 3374 More actions · Answer 1

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

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

H4K Hall of Fame Points: 3374 More actions · Answer 4

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