Loading ...

Dynamic Search SQL Query | CodeAsp.Net

Dynamic Search SQL Query

 /5
0 (0votes)

  Few days back I have faced problem regarding sending multiple value in single parameter (Like. 1,2,3..) to the stored procedure and on the basis of that, I have to use the result of this stored procedure.

  I have spend couple of days for solving this issue.so I want to share this to you.

First of all I have taken a parameter of type string which contains the value like "1,2,3" .after that i have called a stored procedure which recieves this parameter and gives the filtered result. i.e. it gives the rows from table where column contain 1 or 2 or 3.

Eg.

Create Procedure Proce_name 

@param_name varchar(100)

AS
 
DECLARE @SQL nvarchar(4000)
DECLARE @paramlist  nvarchar(4000)

        SELECT @SQL= 'SELECT * from table_name '

        SELECT @SQL = @SQL + ' where Column_name IN (' + @param_name +'))'

        SELECT @paramlist ='@XRoleIDs varchar(100)'                    
           
        --execute query                                                                                               
        EXEC sp_executesql @SQL, @paramlist, @param_name

Note: if some one use more than one parameter in such type of query then take care of sequence of paramter at the              @paramlist i.e. sequence must be same as paramter declaration.

 

Vinay Kumar Gupta

Comments (4)

   
Shaitender
hello..
Are you using comma seperated values in the parameter
12/30/2008
 · 
by
   
vinay_jss
yes, I am using comma separated values for searching the values from database.
12/30/2008
 · 
by
   
Mohammed
thanx for shear ur issue ..
i have problem when i want pass parameter to storeProcedore ..
i want search on multi parameter plz check this code

CREATE PROCEDURE [dbo].[s_test]
@name nvarchar(50),
    @Last nvarchar(50)
AS
declare
@sql nvarchar(max)

BEGIN
set @sql='Select * from [user] where 1=1 '

if len(@name)=0
set @name=null
if @name is not null
set
@sql=@sql+' and First_name='+@name

if len(@last)=0
set
@last=null

if @last is not null
set
@sql=@sql+' and last_name=' @last


exec(@sql)


END
8/2/2009
 · 
by
   
vinay_jss
it will be better if you post your query on forum section of codeasp.
http://www.codeasp.net/forums/asp-net-topics/13/data-access
8/3/2009
 · 
by

Top Posts