[SQL Server] Is this Correct Use of Dynamic SQL???

---

Is this Correct Use of Dynamic SQL??? Posted: 27 Sep 2013 04:19 PM PDT [code="sql"]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SearchBiography] @firstname nvarchar(50), @middlename nvarchar(50), @lastname nvarchar(50), @sexID int, @statusID int ASBEGIN SET NOCOUNT ON; DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max) SET @SqlQuery = '' SET @SqlQueryStatus = '' SET @SqlQueryFirstname = '' SET @SqlQueryMiddlename = '' SET @SqlQueryLastName = '' SET @SqlQuerySex = '' SET @SqlQueryStatus = '' IF @sexID <> 0 SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID) IF @statusID <> 0 BEGIN IF LEN(@SqlQuerySex) > 0 SET @SqlQueryStatus = ' AND statusID = ' + convert(varchar(20), @statusID) ELSE SET @SqlQueryStatus = ' WHERE statusID = ' + convert(varchar(20), @statusID) END IF LEN(@firstname) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 SET @SqlQueryFirstname = ' AND firstname like ''%' + @firstname + '%''' ELSE SET @SqlQueryFirstname = ' WHERE firstname like ''%' + @firstname + '%''' END IF LEN(@middlename) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 SET @SqlQueryMiddlename = ' AND middlename like ''%' + @middlename + '%''' ELSE SET @SqlQueryMiddlename = ' WHERE middlename like ''%' + @middlename + '%''' END IF LEN(@lastname) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 or LEN(@SqlQueryMiddlename) > 0 SET @SqlQueryLastname = ' AND lastname like ''%' + @lastname + '%''' ELSE SET @SqlQueryLastname = ' WHERE lastname like ''%' + @lastname + '%''' END SELECT @SqlParam = ' @xfirstname nvarchar(50), @xmiddlename nvarchar(50), @xlastname nvarchar(50), @xsexID int, @xstatusID int ' EXEC sp_executesql, @SqlParam, @firstname,@middlename,@lastname,@sexID,@statusID END[/code]

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610