The Salty Economist

Things I Should Have Learned in High School
posts - 56, comments - 0, trackbacks - 0

TSQL Paramaterized Views

I really like views in sql Server.

Today, I ran into a case where I wanted create a view that joined to another view in one circumstance and another view in an alternative circumstance.  Specifically, I have a bunch on payment data stored in one view that I wish to sometimes join to an EMPLOYEES table and sometimes join to a VENDORS table.  I could Union the EMPLOYEES and VENDORS, but what I really wanted was a way of dynamically joining 2 tables in a view.

After googling for a bit, I figured out a way to use a function that returns a TABLE as an alterative to a view.  In my function, I can pass a parameter that acts a switch to return the EMPLOYEES data or the VENDORS data.  The function acts just like a view.

My function looks like this:

CREATE FUNCTION [dbo].GET_ADDRESSES (@IS_VENDORS_ONLY INT)
RETURNS @MYDATA TABLE
(

/*  DEFINE YOUR RETURN TABLE STRUCTURE HERE */
FULL_NAME varchar(250),

ADDRESS_1 varchar(250),

ADDRESS_2 varchar(250),

CITY varchar(100),

STATE varchar(100),

ZIP varchar(20)

)

AS
BEGIN
IF @IS_VENDORS_ONLY = 1

 INSERT INTO @MYDATA
 SELECT FULL_NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP FROM VENDORS

ELSE

 INSERT INTO @MYDATA
 SELECT FULL_NAME, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP FROM EMPLOYEES

END

I can then "join" to this function to create another fiew.

For example:

CREATE VIEW PAY_ADDRESS AS

SELECT GUY, PAY, PAY_RATE, PAY_BASIS, PAY_DATE FROM PAYMENT_DATA PD

INNER JOIN [dbo].GET_ADDRESSES(1) AD ON PD.GUY = AD.FULL_NAME

I think this is way cool! 

Print | posted on Tuesday, May 27, 2014 6:41 PM |

Powered by:
Powered By Subtext Powered By ASP.NET