Dynamic Stored Procedure

Question

Dynamic Stored Procedure

imfairozkhan

SSC-Addicted

Points: 422
More actions
March 6, 2011 at 10:47 pm

#234765

Hi All,
I have create a dynamic stored procedure, looks everything is fine. When i pass all the blank parameters it should return me all the records from the table, if i pass any of the parameter then it should filter for that particular column n return me the records. This is not happening. Please let me know where i am going wrong. I am using sql 2005
ALTER procedure [dbo].[GetVehicleDetails]
@OwnerName varchar(50),
@CPRNo varchar(9),
@MobileNo varchar(20),
@PlateNo varchar(7)
as
begin
declare @Query as varchar(1000)
set @Query=@Query + ' select * from vehiclereg where 1=1 '
if @OwnerName<>''
set @Query=@Query + ' and Ownername=' + @OwnerName
if @CPRNo<>''
set @Query=@Query + ' and CPRNo=' +@CPRNo
if @MobileNo<>''
set @Query=@Query + ' and MobileNo=' +@MobileNo
if @PlateNo<>''
set @Query=@Query+ 'and PlateNo=' + @PlateNo
exec(@Query)
end

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

tommyh SSCertifiable Points: 6302 More actions · Answer 1

imfairozkhan (3/6/2011)
Hi All,
I have create a dynamic stored procedure, looks everything is fine. When i pass all the blank parameters it should return me all the records from the table, if i pass any of the parameter then it should filter for that particular column n return me the records. This is not happening. Please let me know where i am going wrong. I am using sql 2005
ALTER procedure [dbo].[GetVehicleDetails]
@OwnerName varchar(50),
@CPRNo varchar(9),
@MobileNo varchar(20),
@PlateNo varchar(7)
as
begin
declare @Query as varchar(1000)
set @Query=@Query + ' select * from vehiclereg where 1=1 '
if @OwnerName<>''
set @Query=@Query + ' and Ownername=' + @OwnerName
if @CPRNo<>''
set @Query=@Query + ' and CPRNo=' +@CPRNo
if @MobileNo<>''
set @Query=@Query + ' and MobileNo=' +@MobileNo
if @PlateNo<>''
set @Query=@Query+ 'and PlateNo=' + @PlateNo
exec(@Query)
end

Well one thing is that the bold part should be

set @Query=' select * from vehiclereg where 1=1 '

Otherwise you would get

set @Query = NULL + ' select * from vehiclereg where 1=1 ' and that would give you NULL 🙂

/T

imfairozkhan SSC-Addicted Points: 422 More actions · Answer 2

Hi Ten,

thanks that one solved my problem

thanks

fairozkhan