[SQL Server] Beginner Using Procedures

Posted: 16 Aug 2013 12:05 AM PDT

[code]CREATE PROCEDURE [dbo].[Procedure_table] (@records INT OUTPUT, @location nvarchar(18) = NULL, @exclude tinyint = NULL, @exclude2 tinyint = NULL, @locationName nvarchar(50) = NULL, @Adult tinyint = NULL, , @Youth nvarchar(8) = NULL, @Case nvarchar(50) = NULL, @explanation nvarchar(50) = NULL, @FirstName nvarchar(50) = NULL, @LastName nvarchar(50) = NULL, @PhoneNumber nvarchar(50) = NULL, @orderField nvarchar(50) = NULL, @orderDir nvarchar(4) = NULL)ASIf @orderField IS NULLBEGIN SET @orderField = 'ID'ENDIf @orderDir IS NULLBEGIN SET @orderDir = 'ASC'ENDBEGINSELECT ID, FirstName, LastName, PhoneNumber, Adult, Youth, DateParticipation, DateExit, Code, LocationName, ReportDate, Explanation FROM dbo.tableWHERE 1 = 1 AND Code = ISNULL(@location, Code) AND Exclusion <= ISNULL(@exclude, Exclusion) AND Exclusion >= ISNULL(@exclude2, Exclusion) AND Adult = ISNULL(@Adult, Adult) AND Youth LIKE ISNULL('%' + @Youth + '%', Youth) AND LocationName LIKE ISNULL ('%' + @LocationName + '%', LocationName) AND......[/code]I am currently rewriting a database to learn more about sql, I have hit a point in procedures that I can't piece together. Any direction much appreciated,Essentially what this procedure is saying pull ID through explanation from dbo.table, and placing the data in procedure_table. I don't understand what the @variables are after Create Procedure? Once I understand that I can better interpret what the 'WHERE' is truly doing. Thank you.

Changing a User Defined Function into a View

Posted: 16 Aug 2013 06:22 AM PDT

Hi. I inherited a User Defined function I'm trying to modify and having some trouble. :crazy: I would like to modify it into a regular query and then make it into a view. The function is [b]ufn_B2H_Enrolled_Clients_List[/b]. This function pulls a list of clients enrolled in program during a time period selected. Within [b]ufn_B2H_Enrolled_Clients_List[/b] is a function [b]ufn_B2H_STATUSES_THROUGH_DT[/b] . This selects the maximum status change a client had during the time range selected so if a client was enrolled then disenrolled and then enrolled again the function is not function them into the count.What I would like is to take [b]ufn_B2H_Enrolled_Clients_Lis[/b]t and turn it into a regular query/view:[code="sql"]**ufn_B2H_Enrolled_Clients_List Function**CREATE FUNCTION [dbo].[ufn_B2H_Enrolled_Clients_List](@From_DT datetime, @To_DT datetime)RETURNS @retB2H_Enrolled_Clients_List TABLE ( CLT_NBR int )AS BEGIN INSERT INTO @retB2H_Enrolled_Clients_List --Include all clients enrolled prior to the period end date SELECT CLT_NBR FROM ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 WHERE s1.B2H_STATUS=4 AND s1.Max_Effect_DT <DATEADD(d,1,@To_DT) --Exclude all clients disenrolled or transferred out prior to the start date EXCEPT SELECT s2.CLT_NBR FROM ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s2 JOIN ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 ON s1.CLT_NBR=s2.CLT_NBR WHERE s2.B2H_STATUS IN (7,9) AND s2.Max_Effect_DT <@From_DT AND s2.Max_Effect_DT>s1.Max_Effect_DT AND s1.B2H_STATUS IN (4,8) --Exclude all clients who transferred in after the prior end date. EXCEPT SELECT s3.CLT_NBR FROM ECMS.dbo.B2H_STATUS s3 JOIN ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 ON s1.CLT_NBR=s3.CLT_NBR WHERE s3.B2H_STATUS = 8 AND s1.B2H_STATUS IN (4,8) AND s3.EFFECT_DT > @To_DT AND s3.Effect_DT>s1.Max_Effect_DTRETURNENDGOCREATE FUNCTION [dbo].[ufn_B2H_STATUSES_THROUGH_DT](@Through_DT datetime)RETURNS @retB2H_STATUSES_THROUGH_DT TABLE ( CLT_NBR int ,B2H_STATUS int ,Max_EFFECT_DT datetime )AS BEGIN INSERT INTO @retB2H_STATUSES_THROUGH_DT S ELECT CLT_NBR ,B2H_STATUS ,MAX(EFFECT_DT) AS Max_Effect_DT FROM [ECMS].[dbo].[B2H_STATUS] WHERE EFFECT_DT<DATEADD(d,1,@Through_DT) GROUP BY CLT_NBR, B2H_STATUSRETURNENDGO[/code]

Sql samples, sql tips

Friday, August 16, 2013

[SQL Server] Beginner Using Procedures

[SQL Server] Beginner Using Procedures

No comments:

Post a Comment

Search This Blog