Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create procedure and filter the result


Create procedure and filter the result

Author
Message
enrique.prados
enrique.prados
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
SoCal_DBD
SoCal_DBD
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1413
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search