http://www.sommarskog.se/dyn-search-2005.html#sp_executesql
I create a lot of search forms and I’ve always just used in-line SQL by examining each form element value and then building a SQL string to run against a view or table. This article discusses how to build a stored procedure in SQL Server 2005 or 2008 using the system stored procedure, sp_executesql.
Here is my example
CREATE PROCEDURE [dbo].[p_Search]
@id_field int = NULL,
@FieldName varchar(300) = NULL,
@TableName varchar(250) = NULL,
@FieldType varchar(3000) = NULL,
@DataEntry varchar(250) = NULL
AS
DECLARE @sql nvarchar(max),
@paramlist nvarchar(4000)
SELECT @sql =
'SELECT * FROM v_FieldDetail
WHERE 1 = 1'
IF @id_field IS NOT NULL
SELECT @sql = @sql + ' AND id_field = @xid_field'
IF @FieldName IS NOT NULL
SELECT @sql = @sql + ' AND FieldName LIKE + ''%'' + @xFieldName + ''%'''
IF @TableName IS NOT NULL
SELECT @sql = @sql + ' AND TableName LIKE + ''%'' + @xTableName + ''%'''
--This one utilizes a split function (below) to accept a comma-delimited string for use with an IN clause
IF @FieldType IS NOT NULL
SELECT @sql = @sql + ' AND FieldType IN ( SELECT * from dbo.Split( @xFieldType, '',''))'
IF @DataEntry IS NOT NULL
SELECT @sql = @sql + ' AND DataEntry LIKE + ''%'' + @xDataEntry + ''%'''
SELECT @sql = @sql + ' ORDER BY FieldName'
--PRINT @sql
SELECT @paramlist =
'@xid_field int,
@xFieldName varchar(300),
@xTableName varchar(250),
@xFieldType varchar(3000),
@xDataEntry varchar(250)
EXEC sp_executesql @sql, @paramlist,@id_field,
@FieldName,
@TableName,
@FieldType,
@DataEntry
To call this stored procedure and bind the results to a Gridview, do something like this:
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ds As DataSet
Dim strSql As String = "p_Search"
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString)
Dim cmd As New SqlCommand(strSql, con)
cmd.CommandType = CommandType.StoredProcedure
'p_Search uses dynamic SQL so we pass a value to it only when there is one
If lblid_field2Text <> "" Then
cmd.Parameters.Add("@id_field", SqlDbType.Int).Value = Convert.ToInt32(lblid_field.Text)
End If
If txtFieldName.Text <> "" Then
cmd.Parameters.Add("@FieldName", SqlDbType.VarChar).Value = txtFieldName.Text
End If
If txtTableName.Text <> "" Then
cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTableName.Text
End If
‘This one passes a comma-delimited string for @FieldType which is used in the split function
If ddlFieldType.SelectedValue <> "" Then
Dim strSearchValue As String = ""
Dim li As ListItem
For Each li In ddlFieldType.Items
If li.Selected = True Then
strSearchValue = strSearchValue & li.Value & "#"
End If
Next
strSearchValue = Left(strSearchValue, (Len(strSearchValue) - 1))
strSearchValue = Replace(strSearchValue, "#", ",")
cmd.Parameters.AddWithValue("@FieldType", SqlDbType.VarChar).Value = strSearchValue
End If
If txtDataEntry.Text <> "" Then
cmd.Parameters.Add("@DataEntry", SqlDbType.VarChar).Value = txtDataEntry.Text
End If
Try
con.Open()
Dim MyAdapter As New SqlDataAdapter(cmd)
ds = New DataSet()
MyAdapter.Fill(ds, "Requests")
Dim intRecordCount As Integer = ds.Tables(0).Rows.Count()
lblRowCount.Text = "Your search returned " & intRecordCount & " records"
GridView1.DataSource = ds.Tables("Requests").DefaultView
GridView1.DataBind()
Finally
con.Close()
End Try
End Sub
--Split function
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end