CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Sameh Ali
Sameh Ali

Reputation: 21

How to make stored procedure dynamic search with SQL Server

How to make stored procedure dynamic search with SQL Server with name table and name filed and value filed

select * from @nametable where @filedname = @valuename

Upvotes: 1

Views: 3827

Answers (4)

Sameh Ali
Sameh Ali

Reputation: 21

1. DECLARE @value AS SMALLINT
2. DECLARE @SQLQuery AS NVARCHAR(500)
3. DECLARE @NameTable AS NVARCHAR(500)
4. DECLARE @field AS NVARCHAR(500)
5. SET @value = 2
6. SET @NameTable = 'Suppliers'
7. SET @field = 'Id'
8. SET @SQLQuery = 'SELECT * FROM '+@NameTable+' WHERE '+@field+' = ' + 
   CAST(@value AS NVARCHAR(10))
9. EXECUTE(@SQLQuery)

Upvotes: 0

user6341745
user6341745

Reputation: 75

You can Create dynamic search SQL server stored procedure 

Create PROCEDURE CUSTOMER.GET_DynamicSearch
(
    -- Optional Filters for Dynamic Search
    @CustomerID            INT = NULL,
    @CustomerName          NVARCHAR(50) = NULL,
    @CustomerTitle         NVARCHAR(50) = NULL,
    @locationId            int = NULL,
    @ControlCardNumber     bigint = NULL,
    @Formnumber            int = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE
    @lCustomerID            INT = NULL,
    @lCustomerName          NVARCHAR(50) = NULL,
    @lCustomerTitle         NVARCHAR(50) = NULL,
    @llocationId            int = NULL,
    @lControlCardNumber     bigint = NULL,
    @lFormnumber            int = NULL

    SET @lCustomerID          = @CustomerID
    SET @lCustomerName         = LTRIM(RTRIM(@CustomerName))
    SET @lCustomerTitle          = LTRIM(RTRIM(@CustomerTitle))
    SET @llocationId           = LTRIM(RTRIM(@locationId))
    SET @lControlCardNumber    = @ControlCardNumber
    SET @lFormnumber             = LTRIM(RTRIM(@Formnumber))

    SELECT
        c.CustomerId,
        c.CustomerName,
        c.CustomerTitle,
        c.LocationId,
        c.ControlCardNumber,
        c.FormNumber
    FROM CUSTOMER.Customer c
    WHERE
        (@lCustomerID IS NULL OR CustomerId = @lCustomerID)
    AND (@lCustomerName IS NULL OR CustomerName LIKE  '%' +@lCustomerName + '%')
    AND (@lCustomerTitle IS NULL OR CustomerTitle LIKE '%' + @lCustomerTitle + '%')
    AND (@llocationId IS NULL OR locationId = @llocationId )
    AND (@lControlCardNumber IS NULL OR ControlCardNumber = @lControlCardNumber)
    AND (@lFormnumber IS NULL OR Formnumber = @lFormnumber)
    ORDER BY c.CustomerID asc

END
GO

Execution Details


EXEC CUSTOMER.GET_DynamicSearch -- 2000  records

-- On providing only CustomerName parameter:


EXEC CUSTOMER.GET_DynamicSearch @CustomerName= 'Uttam' -- 1040 records

Upvotes: 0

Boody
Boody

Reputation: 144

i think in addition to dynamic SQL, using the database information schema is useful in search scenarios which you can in a way or another to employ for filtering or validating the tables and fields names you want to search in. For example: suppose your database name is "dbname" that contains some inventory tables that share the prefix "invent" in their names, and you want to search in these inventory tables only ... so you can loop through the result set of the following simple query:

    select Table_Name      ,Column_Name      ,Data_Type      ,Character_Maximum_Length, *
from dbname.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like 'invent%'

Upvotes: 0

Shukri Gashi
Shukri Gashi

Reputation: 535

You can use dynamic SQL

Example:

DECLARE @nametable VARCHAR(150)='MyTable' --You can change the name here
DECLARE @fieldname VARCHAR(150)='MyField'
DECLARE @valuename INT=4                  --You must be careful here because you have to know the datatype of field to determine data type of parameter

DECLARE @sqlToExecute  AS NVARCHAR(MAX) =N'select * from ['+ @nametable +'] where ['+ @fieldname +'] = ' + @valuename

EXEC (@sqlToExecute)

Upvotes: 1

Related Questions