STORED PROCEDURES

(Home)
Top 3 Resources 
Resource Dev Hood
Resource WWW Coder
Resource CSharpFriends.com
Quick Reference
   
Code
CREATE AND FILL TABLE set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[spHed_Output_Hedis_Advantage]
@tblExpHedis sysname,
@tblXifin sysname,
@tblAdv sysname
AS

BEGIN TRY

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tblExpHedis) 
EXEC ('DELETE ' + @tblExpHedis) 
ELSE
EXEC ('CREATE TABLE ' + @tblExpHedis +
'(
First_Name nvarchar(20),
Last_Name nvarchar(30),
Pat_DOB nvarchar(10),
Member_ID nvarchar(30),
Plan_Number nvarchar(50),
Ser_Date nvarchar(10),
Accession nvarchar(15),
Provider_Name nvarchar(50),
Client# nvarchar(15),
Referring_MD nvarchar(30),
Test_Code nvarchar(15),
Test_Name nvarchar(50),
LOINC nvarchar(1),
Result nvarchar(255),
Units nvarchar(50) 
)')

EXEC ('INSERT INTO ' + @tblExpHedis + 
' SELECT hxp_fname as First_Name, hxp_lname as Last_Name, 
hxp_dob as Pat_DOB,
hxp_subs_id as Member_ID,
hxp_pyr_name as Plan_Number, hxp_receipt_date as Ser_Date,
ReqNo as Accession, 
hxp_bil_acctname as Provider_Name, hxp_clin_abbrv as Client#,
rdr.rd_name as Referring_MD,
adv.test_code as Test_Code,
adv.Analyte_Name as Test_Name, '''' as LOINC, adv.result as Result, adv.units as Units
FROM ' + @tblXifin + ' xif 
INNER JOIN ' + @tblAdv + ' Adv
ON Adv.ReqNo = xif.hxp_account_id
LEFT OUTER JOIN refDrHL rdr
ON Adv.refDr = rdr.rd_code
--LEFT OUTER JOIN tblHed_RefDoc_Filter rdr
--ON Adv.refDr = rdr.rdr_refDr
--AND Adv.srcfac = rdr.rdr_srcfac 
ORDER BY hxp_lname, hxp_fname, hxp_account_id')

--COMMIT TRANSACTION
END TRY

BEGIN CATCH
DECLARE @ErrorNumber nvarchar(50)
DECLARE @ErrorSeverity nvarchar(50)
DECLARE @ErrorState nvarchar(50)
DECLARE @ErrorProcedure nvarchar(50)
DECLARE @ErrorLine nvarchar(50)
DECLARE @ErrorMessage nvarchar(255)

SELECT 

ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
SET @ErrorProcedure = ERROR_PROCEDURE()
SET @ErrorLine = ERROR_LINE()
SET @ErrorMessage = ERROR_MESSAGE()

EXEC spHed_ErrLog_Insert @ErrorNumber, @ErrorSeverity,@ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage
END CATCH
Stored Procedure With Parameters private void btnSave_Click(object sender, System.EventArgs e)

{

//**************************************************

// DATA TO UPDATE

//**************************************************

//mod_tech_signature nvarchar 5

//mod_status int

//mod_complete_date datetime

//mod_completed_by nvarchar 15

//mod_tech int

//mod_tech_assigned_by nvarchar 5

//mod_tech_assign_date datetime

//mod_tech_viewed bit

this.Cursor = System.Windows.Forms.Cursors.WaitCursor;

SqlConnection conn = new SqlConnection("User ID=;password=;Initial Catalog=dbName;Server=serverName; integrated security = sspi");

SqlCommand cmdModReq = new SqlCommand("spMR_Update", conn);

cmdModReq.CommandType = CommandType.StoredProcedure;

 

//**************************************************

// FILL MEMBERS WITH DATA

//**************************************************

//tech signature

this.MR.mod_tech_signature = mod_tech_signature.SelectedValue.ToString();

//status

this.MR.mod_status = Convert.ToInt32(mod_status.SelectedValue);

//Completed By

this.MR.mod_completed_by = mod_completed_by.Text;

//mod_tech_assigned_by

this.MR.mod_tech_assigned_by = mod_tech_assigned_by.Text;

//mod_tech

this.MR.mod_tech = Convert.ToInt32(mod_tech.SelectedValue);

//**************************************************

// DATE AND TIMES

//**************************************************

//mod_complete_date

this.MR.mod_complete_date = Convert.ToDateTime(DateTime.Now);

//mod_tech_assign_date

this.MR.mod_tech_assign_date = Convert.ToDateTime(DateTime.Now);

 

//pk

cmdModReq.Parameters.Add("@myID",this.MR.modRec);

//balance of fields

cmdModReq.Parameters.Add("@mod_tech_signature", this.MR.mod_tech_signature);

cmdModReq.Parameters.Add("@mod_status", this.MR.mod_status);

cmdModReq.Parameters.Add("@mod_complete_date", Convert.ToDateTime(this.MR.mod_created_date));

cmdModReq.Parameters.Add("@mod_completed_by", this.MR.mod_completed_by);

cmdModReq.Parameters.Add("@mod_tech_assigned_by", this.MR.mod_tech_assigned_by);

cmdModReq.Parameters.Add("@mod_tech_assign_date", Convert.ToDateTime(this.MR.mod_tech_assign_date));

cmdModReq.Parameters.Add("@mod_tech_viewed", this.MR.mod_tech_viewed);

cmdModReq.Parameters.Add("@mod_tech", this.MR.mod_tech);

//open and the close connection around the execution

conn.Open();

cmdModReq.ExecuteNonQuery();

conn.Close();

this.Cursor = System.Windows.Forms.Cursors.Arrow;

 
 ADODB Stored Procedure With Parameters Set cmd = new Adodb.Command
cn.Open cnStr
Set cmd.ActiveConnection = cn
cmd.CommandText = "usp_insertOrderToOrderedItems_T"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("IT_Tag", adVarNumeric, adParamInput, 6, lngIT_Tag)
cmd.Parameters.Append cmd.CreateParameter("IT_Tag_ID", adVarNumeric, adParamInput, 6, lngIT_Tag_ID)
cmd.Parameters.Append cmd.CreateParameter("OrderNo", adVarChar, adParamInput, 50, OrderNo)
cmd.Execute