Stored procedure in a function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Stored procedure in a function

Stored procedure in a function

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Wednesday, August 29, 2012 4:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 116, Visits: 356

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,

Post #1351505

H4K

Posted Wednesday, August 29, 2012 6:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1351574

Lowell

Posted Wednesday, August 29, 2012 6:49 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 11,607, Visits: 27,653

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)

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1351577

Luis Cazares

Posted Wednesday, August 29, 2012 7:31 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:26 PM
Points: 958, Visits: 1,917

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1351608

H4K

Posted Friday, August 31, 2012 4:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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 Smile

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

Post #1352701

« Prev Topic | Next Topic »

Permissions