Stored Procedure and Field Alias

• scott.corbin

  SSC Enthusiast

  Points: 177

  More actions

  April 14, 2010 at 2:34 pm

  #94657

  I'm attempting to create a stored procedure that will facilitate the assignment of a person ID.

  I'd like to call the procedure with a series of fields serving as match keys

  Something like'

  CREATE PROCEDURE Foo

  ,@key1 VARCHAR (MAX)

  ,@key2 VARCHAR (MAX)

  ,@key3 VARCHAR (MAX)

  AS

  SELECT *

  FROM Leads A JOIN TableB B

  ON UPPER(a.@key1) = UPPER(b.@key1)

  AND (UPPER(a.@key2 = (UPPER(b.@key2)

  AND a.@key3 = b.@key3

  EXEC Foo @key1 ='firstname', @key2 = 'lastname', @key3 = 'email'

  I can't seem to get it to work. Looks like quotename () might let me do something like a. + ' quotename (@key1)' to put the alias with the field name.

  Thanks,

• WayneS

  SSC Guru

  Points: 95461

  More actions

  April 14, 2010 at 2:53 pm

  #1151757

  You need to convert this all to dynamic sql. And, the object names (in this case, the column names), can't be dynamic. Something like this:

  DECLARE @sql VARCHAR(8000)

  SET @sql =

  'SELECT *

  FROM Leads A JOIN TableB B

  ON UPPER(a.' + @key1 + ') = UPPER(b.' + @key1 + ')

  AND (UPPER(a.' + @key2 + ') = (UPPER(b.' + @key2 + ')

  AND a.' + @key3 + ' = b.' + @key3

  exec sp_executesql @sql

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2