Here I am explaining how to write dynamic Query in SQL!!!
I am giving you a basic scenario where you a table that contains some column.
You want to fetch data from that table. Suppose you have a user interface like below.
You want to fetch data on the click of "Search". If you select any item from combo box then it gives result only for that particular item and if you want to fetch all data then there is no need to select any item.
I am giving you a stored procedure below to fetch record using Dynamic Query. "Dynamic" means your query will make on
the execution time After that this query will execute using "EXEC" and gives appropriate result.
In the above code I declare a local variable named @SQL. It contain a query as a string. In the next line I check whether the Itemcode is empty that means I queried for all data. Then your query will be
SELECT ITEMCODE,BARCODE,SLOTCODE,STATUS,PrintedOn FROM TMOVEMENT WHERE 1=1
|
It returns all data.
Now, If itemcode is not empty your query will be
SELECT ITEMCODE,BARCODE,SLOTCODE,STATUS,PrintedOn FROM TMOVEMENT WHERE 1=1 AND ITEMCODE='ABC'
|
It returns data where Itemcode is 'ABC'.
ENJOY CODING!!!