Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure in a function Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 4:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:09 AM
Points: 227, Visits: 715
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
Posted Wednesday, August 29, 2012 6:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
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
Posted Wednesday, August 29, 2012 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--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
Posted Wednesday, August 29, 2012 7:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:30 PM
Points: 3,541, Visits: 7,636
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1351608
Posted Friday, August 31, 2012 4:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
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 . 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 »

Add to briefcase

Permissions Expand / Collapse