none
How do I send three parameters in with a select command, search for records that contain paramater 1, then items found with parameter 1 that contain parameter 2, etc.

    Question

  • I am searching through a record with three different paramaters, where the second and third are dependent on the results of previous. 

    The first looks for last name in the contacts table, but now I want to look in the results of what I found for items that have a matching first name, then middle name. 

    I think I am close, but am at a loss. Where the ??? are in my code, I believe I need a table or dataset temporarily created.

    Thanks,


    DCSSR

    Saturday, September 28, 2013 2:35 PM

Answers

  • As SQL is set based, you need to think different. A procedural approach is under most circumstances not the best.

    Waht your are trying do to has Erland recaptured in his article Dynamic Search Conditions in T-SQL.

    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:29 AM
    Saturday, September 28, 2013 3:38 PM
  • I suggest a dynamic SQL approach to build the WHERE clause with only the criteria specified.  For example (using NULL to indicate omitted criteria):

    ALTER PROCEDURE [dbo].tblContactsSelectCommandWithNameToSearchFor	
    	  @FirstName varchar (25) = NULL
    	, @MiddleName varchar (25) = NULL
    	, @LastName varchar (25) = NULL
    AS
    
    DECLARE 
    	  @SQL nvarchar(MAX) = N'SELECT ContactID FROM dbo.tblContacts'
    	, @WhereClause nvarchar(MAX);
    
    IF @FirstName IS NULL AND @MiddleName IS NULL AND @LastName IS NULL
    BEGIN
    	RAISERROR ('At least one search value must be specified', 16, 1);
    	RETURN 1;
    END;
    
    IF @LastName IS NOT NULL
    BEGIN
    	SET @WhereClause = COALESCE(@WhereClause + N' AND', N' WHERE') + ' LastName = @LastName'
    END;
    
    IF @FirstName IS NOT NULL
    BEGIN
    	SET @WhereClause = COALESCE(@WhereClause + N' AND', N' WHERE') + ' FirstName = @FirstName'
    END;
    
    IF @MiddleName IS NOT NULL
    BEGIN
    	SET @WhereClause = COALESCE(@WhereClause + N' AND', N' WHERE') + ' MiddleName = @MiddleName'
    END;
    
    SET @WhereClause = @WhereClause + ';';
    
    SET @SQL = @SQL + @WhereClause;
    
    EXEC sp_executesql 
    	@SQL
    	, N'@FirstName varchar (25),
            @MiddleName varchar (25),
            @LastName varchar (25)'
    	, @FirstName = @FirstName
    	, @MiddleName = @MiddleName
    	, @LastName = @LastName;
    
    RETURN @@ERROR;
    GO

    See http://www.sommarskog.se/dyn-search-2008.html for more details on this approach.  If you don't want to grant permissions directly to the underlying table, see http://www.sommarskog.se/grantperm.html.  I suggest the certificate method.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, September 28, 2013 3:53 PM
  • Hi

    PFB code

    	SELECT ContactID FROM tblContacts
    	WHERE (isnull(@LastName,'') = '' or Lastname = @LastName)
    	AND (isnull(@FirstName,'') = '' or FirstName = @FirstName)
    	AND (isnull(@MiddleName,'') = '' or MiddleName = @MiddleName)

    OR

    	If(IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')='' and IsNull(@MiddleName,'')='' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName
    	ELSE if (IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')<>'' and IsNull(@MiddleName,'')='' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName and FirstName = @FirstName
    	ELSE if (IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')<>'' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName and FirstName = @FirstName and MiddleName = @MiddleName
    	ELSE if (IsNull(@LastName,'')='' and IsNull(@FirstName,'')<>'' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE FirstName = @FirstName and MiddleName = @MiddleName
    	ELSE if (IsNull(@LastName,'')='' and IsNull(@FirstName,'')='' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE MiddleName = @MiddleName
    	ELSE if (IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')='' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName and MiddleName = @MiddleName

    Thanks

    Sarvana Kumar C

    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:30 AM
    Saturday, September 28, 2013 4:45 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 
    This is minimal polite behavior on SQL forums. 

    Putting “tbl-” in table names is called “tibbling”; it is a violation of ISO-11179 Standards and looks silly enough to get derogatory name. A procedure name is “<verb>_<object>” and since a properly designed module of code in any language will do one clear, simple task that verb is also direct and simple. 

    SQL uses <> and not <>; when I see this, I know that the programmer is still writing C or C++ and has not learned declarative programming yet. This is why you use BEGIN-END as a replacement for the {} of the C family language you mimic. There will be if-then control flow, loops and other bad coding that will need to be removed. 

    Why do you worry about empty strings? Answer: C does not have NULLs! But it has empty strings! Your approach to SQL is wrong; you are writing C in SQL. 

    CREATE PROCEDURE Search_Contacts
    (@in_first_name VARCHAR(25), @in_middle_name VARCHAR(25),
     @in_last_name VARCHAR(25))
    AS
     SELECT contact_id 
       FROM Contacts
     WHERE COALESCE (@in_last_name, last_name) = last_name
       AND COALESCE (@in_first_name, first_name) = first_name
       AND COALESCE (@in_middle_name, middle_name) = middle_name;

    Even better would be to use the LIKE predicate: 

     WHERE last_name LIKE @in_last_name
       AND first_name LIKE @in_first_name
       AND middle_name LIKE @in_middle_name;

    Let your users provide patterns with wildcard (_ and %). 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:30 AM
    Saturday, September 28, 2013 6:18 PM
  • Answer: C does not have NULLs! But it has empty strings! Your approach to SQL is wrong; you are writing C in SQL. 



    *cough* excuse me Joe, wouldn't the following demonstrate an empty string AND a null string in ANSI C? 

    ANSI C does have NULLs ? 

    char *str2 = NULL; // Null string in ANSI C ? 

    Technically simply a null pointer, but since a string in C is nothing but a pointer to a null terminated array of characters we could say that the null pointer IS the same as a 'null string' in ANSI C ?  NULL being defined as a 0 cast void pointer ??

    #define NULL ((void *)0)

    Do I get some kind of hexidecimal check for $1.28 for this?

    (And before you say it, I do not want a check for NULL dollars, or made out to null!) 


    Thanks! Josh




    • Edited by Josh Ashwood Wednesday, October 02, 2013 4:02 AM
    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:30 AM
    Wednesday, October 02, 2013 3:57 AM

All replies

  • hmm, what code? Please post a concise and complete example. Include table DDL and sample data INSERT statments. Add your query and a tabular view of your desired result.

    And for the terminology: we work with sets of rows and rows consists of columns. No records.

    Saturday, September 28, 2013 2:48 PM
  • Sorry, forgot to provide the code.  Here it is!

    ALTER PROCEDURE [dbo].tblContactsSelectCommandWithNameToSearchFor	
    (
    	@FirstName varchar (25),
    	@MiddleName varchar (25),
    	@LastName varchar (25)	
    )
    AS
    	if len(rtrim(@LastName)) != 0
    	BEGIN
    	SELECT ContactID FROM tblContacts
    	WHERE @LastName = Lastname
    	End
    
    	if len(rtrim(@FirstName)) != 0 
    	BEGIN
    	SELECT ContactID FROM tblContacts
    	WHERE @FirstName = FirstName
    	End
    
    	if len(rtrim(@MiddleName)) = 0 
    	BEGIN
    	SELECT ContactID FROM tblContacts
    	WHERE @MiddleName = MiddleName
    	End


    DCSSR

    Saturday, September 28, 2013 3:26 PM
  • As SQL is set based, you need to think different. A procedural approach is under most circumstances not the best.

    Waht your are trying do to has Erland recaptured in his article Dynamic Search Conditions in T-SQL.

    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:29 AM
    Saturday, September 28, 2013 3:38 PM
  • Is it better to use the datareader in codebehind to do this, and then execute an select command to look for the array of items that are found with the datareader?


    DCSSR

    Saturday, September 28, 2013 3:40 PM
  • I suggest a dynamic SQL approach to build the WHERE clause with only the criteria specified.  For example (using NULL to indicate omitted criteria):

    ALTER PROCEDURE [dbo].tblContactsSelectCommandWithNameToSearchFor	
    	  @FirstName varchar (25) = NULL
    	, @MiddleName varchar (25) = NULL
    	, @LastName varchar (25) = NULL
    AS
    
    DECLARE 
    	  @SQL nvarchar(MAX) = N'SELECT ContactID FROM dbo.tblContacts'
    	, @WhereClause nvarchar(MAX);
    
    IF @FirstName IS NULL AND @MiddleName IS NULL AND @LastName IS NULL
    BEGIN
    	RAISERROR ('At least one search value must be specified', 16, 1);
    	RETURN 1;
    END;
    
    IF @LastName IS NOT NULL
    BEGIN
    	SET @WhereClause = COALESCE(@WhereClause + N' AND', N' WHERE') + ' LastName = @LastName'
    END;
    
    IF @FirstName IS NOT NULL
    BEGIN
    	SET @WhereClause = COALESCE(@WhereClause + N' AND', N' WHERE') + ' FirstName = @FirstName'
    END;
    
    IF @MiddleName IS NOT NULL
    BEGIN
    	SET @WhereClause = COALESCE(@WhereClause + N' AND', N' WHERE') + ' MiddleName = @MiddleName'
    END;
    
    SET @WhereClause = @WhereClause + ';';
    
    SET @SQL = @SQL + @WhereClause;
    
    EXEC sp_executesql 
    	@SQL
    	, N'@FirstName varchar (25),
            @MiddleName varchar (25),
            @LastName varchar (25)'
    	, @FirstName = @FirstName
    	, @MiddleName = @MiddleName
    	, @LastName = @LastName;
    
    RETURN @@ERROR;
    GO

    See http://www.sommarskog.se/dyn-search-2008.html for more details on this approach.  If you don't want to grant permissions directly to the underlying table, see http://www.sommarskog.se/grantperm.html.  I suggest the certificate method.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, September 28, 2013 3:53 PM
  • Hi

    PFB code

    	SELECT ContactID FROM tblContacts
    	WHERE (isnull(@LastName,'') = '' or Lastname = @LastName)
    	AND (isnull(@FirstName,'') = '' or FirstName = @FirstName)
    	AND (isnull(@MiddleName,'') = '' or MiddleName = @MiddleName)

    OR

    	If(IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')='' and IsNull(@MiddleName,'')='' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName
    	ELSE if (IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')<>'' and IsNull(@MiddleName,'')='' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName and FirstName = @FirstName
    	ELSE if (IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')<>'' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName and FirstName = @FirstName and MiddleName = @MiddleName
    	ELSE if (IsNull(@LastName,'')='' and IsNull(@FirstName,'')<>'' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE FirstName = @FirstName and MiddleName = @MiddleName
    	ELSE if (IsNull(@LastName,'')='' and IsNull(@FirstName,'')='' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE MiddleName = @MiddleName
    	ELSE if (IsNull(@LastName,'')<>'' and IsNull(@FirstName,'')='' and IsNull(@MiddleName,'')<>'' )
    	SELECT ContactID FROM tblContacts WHERE  Lastname = @LastName and MiddleName = @MiddleName

    Thanks

    Sarvana Kumar C

    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:30 AM
    Saturday, September 28, 2013 4:45 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 
    This is minimal polite behavior on SQL forums. 

    Putting “tbl-” in table names is called “tibbling”; it is a violation of ISO-11179 Standards and looks silly enough to get derogatory name. A procedure name is “<verb>_<object>” and since a properly designed module of code in any language will do one clear, simple task that verb is also direct and simple. 

    SQL uses <> and not <>; when I see this, I know that the programmer is still writing C or C++ and has not learned declarative programming yet. This is why you use BEGIN-END as a replacement for the {} of the C family language you mimic. There will be if-then control flow, loops and other bad coding that will need to be removed. 

    Why do you worry about empty strings? Answer: C does not have NULLs! But it has empty strings! Your approach to SQL is wrong; you are writing C in SQL. 

    CREATE PROCEDURE Search_Contacts
    (@in_first_name VARCHAR(25), @in_middle_name VARCHAR(25),
     @in_last_name VARCHAR(25))
    AS
     SELECT contact_id 
       FROM Contacts
     WHERE COALESCE (@in_last_name, last_name) = last_name
       AND COALESCE (@in_first_name, first_name) = first_name
       AND COALESCE (@in_middle_name, middle_name) = middle_name;

    Even better would be to use the LIKE predicate: 

     WHERE last_name LIKE @in_last_name
       AND first_name LIKE @in_first_name
       AND middle_name LIKE @in_middle_name;

    Let your users provide patterns with wildcard (_ and %). 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:30 AM
    Saturday, September 28, 2013 6:18 PM
  • Answer: C does not have NULLs! But it has empty strings! Your approach to SQL is wrong; you are writing C in SQL. 



    *cough* excuse me Joe, wouldn't the following demonstrate an empty string AND a null string in ANSI C? 

    ANSI C does have NULLs ? 

    char *str2 = NULL; // Null string in ANSI C ? 

    Technically simply a null pointer, but since a string in C is nothing but a pointer to a null terminated array of characters we could say that the null pointer IS the same as a 'null string' in ANSI C ?  NULL being defined as a 0 cast void pointer ??

    #define NULL ((void *)0)

    Do I get some kind of hexidecimal check for $1.28 for this?

    (And before you say it, I do not want a check for NULL dollars, or made out to null!) 


    Thanks! Josh




    • Edited by Josh Ashwood Wednesday, October 02, 2013 4:02 AM
    • Marked as answer by DCSSR Wednesday, October 02, 2013 11:30 AM
    Wednesday, October 02, 2013 3:57 AM