Use sp_executesql with dynamic number of arguments

• vpoomari

  SSC Enthusiast

  Points: 126

  More actions

  July 7, 2016 at 5:37 am

  #311563

  CREATE PROCEDURE GetUserDetails

  (

  @ParameterListWithDataType VARCHAR(MAX),

  @ParameterNameList VARCHAR(MAX),

  @ParameterValues VARCHAR(MAX),

  @WhereCondition VARCHAR(MAX)

  )

  AS

  BEGIN

  DECLARE @sql NVARCHAR(4000)

  SET @sql = ' SELECT FirstName, MiddleName, LastName, Address, Salary FROM UserDetails WHERE ' + @WhereCondition

  EXEC sp_executesql @sql, @ParameterListWithDataType, @ParameterNameList

  END

  EXEC GetUserDetails @ParameterListWithDataType = '@FirstName VARCHAR(100)', @ParameterNameList = '@FirstName', @ParameterValues = 'ABC', @WhereCondition = 'FirstName = @FirstName '

  EXEC GetUserDetails @ParameterListWithDataType = '@FirstName VARCHAR(100), @LastName VARCHAR(100)', @ParameterNameList = '@FirstName,@LastName', @ParameterValues = '''ABC'', ''XYZ''', @WhereCondition = 'FirstName = @FirstName OR LastName = @LastName'

  EXEC GetUserDetails @ParameterListWithDataType = '@FirstName VARCHAR(100), @LastName VARCHAR(100), @MiddleName VARCHAR(100)', @ParameterNameList = '@FirstName,@LastName,@MiddleName', @ParameterValues = '''ABC'', ''XYZ'', ''DEF''', @WhereCondition = 'FirstName = @FirstName OR LastName = @LastName OR MiddleName = @MiddleName'

  Is there any method to do like this? Because the Search criteria will be dynamic. Please give your comment or give me the alternate solution to use sp_executesql with dynamic number of parameters.

  Thanks in Advance.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  July 7, 2016 at 7:35 am

  #1888912

  Is there a finite and definite list of all of the possible parameters?

  If so, just list them all in the @params parameter and the Exec statement. If they aren't in the Where clause, they won't be used.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon