0

I have some list of Tasks in the table and each tasks has 'Completed' column some task may completed or may not completed. In webpage, I have textbox in that we can pass multiple Task names, I want to display messages to the user task is completed or not. Here, how to check If user passes multiple Task names among that one might be completed another may not. How to do in SQL query?

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Tarun
  • 25
  • 1
  • 6
  • Please provide more details. What language is the web page created with? How have you connected to the database? What is the structure of your data table? – John Bell Apr 23 '15 at 10:30
  • asp.net and connected to Microsoft SQL server – Tarun Apr 23 '15 at 10:31
  • Table contains columns like Task ID, Task Name, Completed. – Tarun Apr 23 '15 at 10:32
  • are you looking at a possible plsql procedure , passing parameter to it and getting the desired result ? – anudeepks Apr 23 '15 at 10:44
  • @anudeepks exactly...but I am able to pass parameter to sql procedure and get desired message to user.... If task is completed or not I am sending message ... but when user inputs multiple task, among that one may completed other may not.. in dis condition I am failed. plz do help. thanks in advance – Tarun Apr 23 '15 at 10:52

1 Answers1

0

I think you're looking for some dynamic SQL based on your question.

For the purpose of this answer I assume that the the string values are comma separated, potentially with a space after the comma:

-- search criteria
DECLARE @taskNames NVARCHAR(30) = 'task1, task2, task3'
--set up some dummy data
SELECT  *
INTO    #TasksTemp
FROM    ( SELECT    1 AS ID ,
                    'task1' AS TaskName ,
                    1 AS Completed
          UNION
          SELECT    2 AS ID ,
                    'task2' AS TaskName ,
                    0 AS Completed
          UNION
          SELECT    3 AS ID ,
                    'task3' AS TaskName ,
                    1 AS Completed
          UNION
          SELECT    4 AS ID ,
                    'task4' AS TaskName ,
                    0 AS Completed
        ) t

-- dynamic sql variable
DECLARE @sql NVARCHAR(200)

-- SANITIZE input
-- cover case where comma may have space after it
SET @taskNames = REPLACE(@taskNames, ', ', ',')
-- add quote marks to seperate string values
SET @taskNames = '''' + REPLACE(@taskNames, ',', ''',''') + ''''

SET @sql = 'SELECT * FROM #TasksTemp WHERE TaskName IN (' + @taskNames + ')'
PRINT @sql
-- will rpoduce:
-- SELECT * FROM #TasksTemp WHERE TaskName IN ('task1','task2','task3')

-- run the dynamic sql
EXEC sp_executesql @sql

DROP TABLE #TasksTemp

You may just have to tweak the code to account for different separators.

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • thanks for your help.....your all assumptions made is correct... u consider only three values as inputs....but we don't know right how many values user will enter into the text box.... – Tarun Apr 23 '15 at 11:22
  • @Tarun this will work for any number of comma separated values, it's just a short demo, you can edit the sample code and see – Tanner Apr 23 '15 at 13:11