May 8, 2008 at 3:57 am
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
May 9, 2008 at 10:30 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply