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

Create procedure and filter the result Expand / Collapse
Author
Message
Posted Thursday, May 8, 2008 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 12:22 AM
Points: 4, Visits: 12
Hi all,
I have this sql script:

-- Crear tabla
create table TABLA_MAESTRA (
CD_NOMBRE_TABLA VARCHAR(100) NOT NULL
, DS_CAMPO_DESCRIPCION VARCHAR(100) NOT NULL
, DS_CAMPO_CIF VARCHAR(100) NULL
)
go

CREATE TABLE tabla1(
ID INT NOT NULL,
Descripcion VARCHAR(20) NOT NULL,
CIF VARCHAR(8) NULL
)
GO
CREATE TABLE tabla2(
ID INT NOT NULL,
Descripcion2 VARCHAR(20) NOT NULL
)
GO

INSERT INTO [testdb].[dbo].[TABLA_MAESTRA] ([CD_NOMBRE_TABLA],[DS_CAMPO_DESCRIPCION],[DS_CAMPO_CIF])
VALUES
('tabla1', 'Descripcion', 'CIF')

INSERT INTO [testdb].[dbo].[TABLA_MAESTRA] ([CD_NOMBRE_TABLA],[DS_CAMPO_DESCRIPCION],[DS_CAMPO_CIF])
VALUES
('tabla2', 'Descripcion2', null)
GO

INSERT INTO tabla1 (ID, Descripcion, CIF) VALUES (1, 'des1 a', '12345678');
INSERT INTO tabla1 (ID, Descripcion, CIF) VALUES (2, 'des1 b', '20939483');

INSERT INTO [tabla2] ([ID],[Descripcion2]) VALUES ( 1, 'des2 a' )
INSERT INTO [tabla2] ([ID],[Descripcion2]) VALUES ( 2, 'des2 b' )
INSERT INTO [tabla2] ([ID],[Descripcion2]) VALUES ( 3, 'des2 c' )
GO

TABLA_MAESTRA
CD_NOMBRE_TABLA DS_CAMPO_DESCRIPCION DS_CAMPO_CIF
tabla1 Descripcion CIF
tabla2 Descripcion2 NULL

dynamically, I want to get all rows of TABLA_MAESTRA table, and get sql string like this:

select Descripcion as Des, CIF as CIF from tabla1
union
select Descripcion2 as Des, NULL as CIF from tabla2

I get a table with two fields: Des, CIF,

and then I want to filter, for example

select Des, CIF from THE_RESULT_TABLE where Des like '%a%'

Using function not possible (dynamic sql using EXEC or sp_executesql not possiblle in function), and I want use store procedure.

Any suggestions ? any help will be appreciated.

Thanks in advance, regards
Post #496980
Posted Friday, May 9, 2008 10:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:01 AM
Points: 170, Visits: 896
Let me give this a shot. If I understand your post correctly, you are saying you want to use a stored procedure. You're practically there with what you have supplied. I would do the following:

if object_id('GetResults') is not null
drop procedure GetResults
go

create procedure GetResults
@PassedCriteria varchar(5) -- i'm just guessing at this length

as

declare @sql nvarchar(max)
declare @params nvarchar(max)
declare @criteria nvarchar(max)

set @PassedCriteria = '%' + @PassedCriteria + '%'

set @sql = 'select Descripcion as Des, CIF as CIF from tabla1 '
set @sql = @sql + ' where Descripcion like @Criteria '
set @sql = @sql + ' union '
set @sql = @sql + ' select Descripcion2 as Des, NULL as CIF from tabla2 '
set @sql = @sql + ' where Descripcion2 like @Criteria '

set @params = '@Criteria varchar(5)'

exec sp_executesql @sql, @params, @Criteria = @PassedCriteria

go

-- now test:
GetResults @PassedCriteria = 'a'


I hope this is what you're looking for and I haven't missed the mark.

Lisa
Post #498071
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse