dynamic stored procedure

[Troopers]

Hello!!

I would like build a SQL request dynamically in a stored procedure
So I do:


create procedure sp_RecordsetArticle


(
@Famille varchar (70),
@SousFamille varchar (70),
@Genre varchar(70),
@N int OUTPUT
)


AS
declare @Fam varchar(70), @SSFam varchar(70) , @Gre varchar (70)


if @Famille = ''
set @Fam = 'BaseArticle.ba_Famille like %'
else
set @Fam = 'BaseArticle.ba_Famille = ' + @Famille
if @SousFamille = ''
set @SSFam = 'BaseArticle.ba_SousFamille like %'
else
set @SSFam = 'BaseArticle.ba_SousFamille = ' + @SousFamille
if @Genre = ''
set @Gre = 'BaseArticle.ba_Genre like %'
else
set @Gre = 'BaseArticle.ba_Genre = '+ @Genre

SELECT @N = [ba_N°]
From BaseArticle
WHERE @Fam + ' and ' + @SSFam + ' and ' + @Gre



GO



so i create my WHERE condition in dynamic
but i don't know the exact syntax to WHERE line


I try several things but no result

someone could help me?

thanks

Troopers

 

[tlbroadbent]

Change the procedure as follows.

declare @Fam varchar(70), @SSFam varchar(70) , @Gre varchar (70), @sql varchar(1024)

if @Famille = ""
set @Fam = "ba_Famille like '%'"
else
set @Fam = "ba_Famille = '" + @Famille + "'"
if @SousFamille = ""
set @SSFam = "ba_SousFamille like '%'"
else
set @SSFam = "ba_SousFamille = '" + @SousFamille + "'"
if @Genre = ""
set @Gre = "ba_Genre like '%'"
else
set @Gre = "ba_Genre = '" + @Genre + "'"

SELECT @sql="SELECT [ba_N°] From BaseArticle WHERE " + @Fam + " and " + @SSFam + " and " + @Gre

Exec(@sql) Terry

;-) He has the right to criticize who has the heart to help. -Abraham Lincoln

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm