MS SQL Server :: Transact SQL :: Include A Condition In Store Proc

Transact SQL :: Store Date Permanently In Stored Proc

Aug 24, 2015

I have stored procedure which runs on some period, what i want is when it first run i want to store that date permanently or till the next time it runs again, and then i need to take my data from that store proc , where hist_date between (date stored when it ran first time,example, 08/21/2015) and today date.(exampple, 08/24/2015)

Now next time when it runs , date stored should be updated in this case it should be (08/24/2015) .

How can i do this in stored proc, I tried to use temp table but it didn't work .

View 13 Replies View Related

Hi I Want To Know How To Write Stored Procedure ..then I Have To Include (IF Condition ) With SP ..

Sep 20, 2007

hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????

View 5 Replies View Related

Transact SQL :: How To Add Condition In Where Clause According To Another Condition

Oct 17, 2015

I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :

create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc

[Code] .....

View 3 Replies View Related

SQL Server 2012 :: Include Source Feature In More Than One Stored Proc

Sep 2, 2015

I need to use the same SQL source code in more than one stored proc.

Is there a way to -include mysql.sql or something like that ?

Is InsertSnippet the only way to do this ?

,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal

View 9 Replies View Related

Exiting The Stored Proc On Condition

May 1, 2008

CREATE PROCEDURE [dbo].[WWspLoadData]
@Planid varchar(5), @OverMar int = Null

need to check here if the @PlainID exists in the tbOldCustomers.PlainID
if it does exists then do nothing and exit the store procedure, and if it does not exists continue
to the rest of the store procedure.

View 5 Replies View Related

Update Condition(store Procedure)

Mar 2, 2004

can anyone help to create storeprocedure in SQL SERVER 2000?
the case is like this:
i have one table call [TIMETABLE] which contains these fields:ID,start_time,finish_time,subject_code,venu e
and another table call [attendant_sheet] has these following fields:ID(which should be from TIMETABLE),student_id,subject_code,status,date.

and the storeprocedure will works like this:

if the time(system time) has pass the finish_time on TIMETABLE table then insert the related field into attendant_sheet. and then update the status field to 'absent'
and the student_id should be get it from another table call [enrolment] who havent take their attendance and enrols related subect?

could this possible? PLZ HELP ME!

View 4 Replies View Related

Transact SQL :: Include Minimum Item Values Only

Aug 3, 2015

My sql gives me the results as shown in #TEMTAB Table.some times Mitem_id is repeated for the same Group_id,SGroup_id with different item_id

ex: SQ322,SQ323,SQ324,SQ325 are repeated here.
CREATE TABLE #TEMTAB
(Group_id int, SGroup_id int, item_id int, Mitem_id varchar(10)
,YN varchar(2),value varchar(8),reason varchar(20))

[code]...

View 8 Replies View Related

Transact SQL :: Update Statement To Include Multiple Records At Once

Apr 20, 2015

I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.

update
mklopt
set
FRMDAT =
'12/31/2014'
where
JOBCOD =
'PH14789'

I also want to include the following instead of running it one at a time

PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757

View 9 Replies View Related

Trying To Create A Proc That Will Insert Values Based On A Condition That Is Another Table

Feb 16, 2005

Can someone give me a clue on this. I'm trying to insert values based off of values in another table.

I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).

Two questions

1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.

2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.

Thanks in advance, bellow is the code that I have so far:

RB

Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')

View 1 Replies View Related

SQL Store Proc - Need Help

May 4, 2007

I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd
I also tried
SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.

View 6 Replies View Related

Store Proc - Need Some Help Please

Jan 7, 2005

I am doing an insert and I get the error "Cast from type 'DBNull' to type 'Integer' is not valid." if the contact name already exist.

I dont see my error so if someone could enlight me I would appreciate.
Thanks

"****************STORE PROC***********************
ALTER procedure dbo.Add_Cl_Contact
(
@ContactNamenvarchar(75),
@Departmentnvarchar (50)=null,
@Titlenvarchar(50)=null,
@Phone1char(20)=null,
@Phone2char(20)=null,
@Phone3char(20)=null,
@Ext1char(10)=null,
@Ext2char(10)=null,
@Ext3char(10)=null,
@Faxnvarchar(50)=null,
@Emailnvarchar(50)=null,
@ContactTypeIDint=null,
@Resultinteger OUTPUT

AS

if Exists
(
Select *
From Cl_Contacts
Where ContactName = @ContactName
)
Return 1
Else

insert into Cl_Contacts (
ContactName,
Department,
Title,
Phone1,
Phone2,
Phone3,
Ext1,
Ext2,
Ext3,
Fax,
ContactTypeID,
Email

)
values (
@ContactName,
@Department,
@Title,
@Phone1,
@Phone2,
@Phone3,
@Ext1,
@Ext2,
@Ext3,
@Fax,
@ContactTypeID,
@Email
)
Select @Result = SCOPE_IDENTITY()
return 0
"****************END STORE PROC***********************

'******************************************************************
'******************************************************************
Public Function Add_Cl_Contact(ByVal strContactName As String, _
ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _
ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _
ByVal strPhone3 As String, ByVal strTitle As String, ByVal iContactTypeId As Integer, _
ByRef iResult As Integer, ByRef strError As String) As Boolean
'******************************************************************
Dim bSuccess As Boolean = True
Dim connect As New SqlConnection(strConnection)
Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect)
Dim paramReturnValue As SqlParameter

cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
cmdSelect.Parameters.Add("@ContactName", strContactName)
cmdSelect.Parameters.Add("@Department", strDep)
cmdSelect.Parameters.Add("@Title", strTitle)
cmdSelect.Parameters.Add("@Phone1", strPhone1)
cmdSelect.Parameters.Add("@Phone2", strPhone2)
cmdSelect.Parameters.Add("@Phone3", strPhone3)
cmdSelect.Parameters.Add("@Ext1", strExt1)
cmdSelect.Parameters.Add("@Ext2", strExt2)
cmdSelect.Parameters.Add("@Ext3", strExt3)
cmdSelect.Parameters.Add("@Fax", strFax)
cmdSelect.Parameters.Add("@Email", strEmail)
cmdSelect.Parameters.Add("@ContactTypeID", iContactTypeId)

paramReturnValue = cmdSelect.Parameters.Add("@Result", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.Output

paramReturnValue = cmdSelect.Parameters.Add("ReturnValue", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.ReturnValue

Try
connect.Open()
cmdSelect.ExecuteNonQuery()
iResult = cmdSelect.Parameters("@Result").Value
connect.Close()

If cmdSelect.Parameters("ReturnValue").Value = 0 Then
strError = "Contact has been added"
Else
strError = strContactName & " already exist!"
bSuccess = False
End If

Catch ex As Exception
bSuccess = False
strError = ex.Message
Finally
If connect.State = ConnectionState.Open Then
connect.Close()
End If
End Try
Return bSuccess
End Function

View 3 Replies View Related

Need Help With A Store Proc

Jun 29, 2005

For a reason that I dont see my store proc is always
returning 0 records but if I use a commandType.text instead of
StoredProcedure then I get my results. So I must have some kind
of error somewhere.
Store Proc
ALTER PROCEDURE dbo.Get_Cl_IssuesBySystemID

    @SystemId        nvarchar(255)

AS

    SET NOCOUNT ON

SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue

FROM dbo.IssueTracker_Issues t1

LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2

ON t1.IssueId = t2.IssueId



Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%@SystemId%'

order by t1.DateCreated



    SET NOCOUNT OFF

Function
Public Function Get_Cl_IssuesBySystemID(ByVal strSystemId As String) As DataView
      Dim objDs As New DataSet
      Dim objDv As New DataView
      Dim connect As New SqlConnection(strConnection)

    '***For CommanType.Text***
      Dim strSelect As String

      strSelect = "SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue" & _
              " FROM dbo.IssueTracker_Issues t1" & _

" LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2" & _
              " ON t1.IssueId = t2.IssueId" & _

" Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%" &
strSystemId & "%'" & _
              " order by t1.DateCreated"
      Dim cmdSelect As New SqlCommand(strSelect, connect)
      cmdSelect.CommandType = CommandType.Text
       '***End For CommanType.Text***

       '***For CommanType.StoredProcedure***
      'Dim cmdSelect As New SqlCommand("Get_Cl_IssuesBySystemID", connect)
      'cmdSelect.CommandType = CommandType.StoredProcedure
      'PARAM
      'cmdSelect.Parameters.Add("@SystemId", strSystemId)
   '***End For CommanType.StoredProcedure***

      Dim objAdapter As New SqlDataAdapter(cmdSelect)

      Try
        objAdapter.Fill(objDs)
        objDv = objDs.Tables(0).DefaultView

      Catch objErr As Exception
        Throw New Exception(objErr.Message)
      End Try
      Return objDv
End Function

View 2 Replies View Related

Store Proc

May 21, 2008

the storeproc gives out put one data with one column as Day 25 or 50 as a column in a table. if the condition

inside the storeproc matches otherwise the column it returns will not have any value i.e null..i am trying to run

this query to get some boolean output . Is it valid to run store proc in while exists case. here is the query

SELECT CASE

WHEN EXISTS (EXEC dbo.sp_CheckingLobValue

@varfilename = N'Customers.xml')

BEGIN

'YES'

ELSE

'NO'

END

can any one has any solution to it.

thanks in regards.

View 5 Replies View Related

How To Debug Store Proc?

Aug 3, 2006

Can someone help me with debugging store proc. I am usig VS 2003 and SQL Server 2005 and I have no clue as how to do that if it is possible. I would like to place a break point in my store proc if that is possible. I know if I was in VS 2005 it would be a charm but it's not my case.
Thanks

View 3 Replies View Related

Need Help, Error With Store Proc

Aug 9, 2004

I am trying to do an insert with a SPand I get the following error: "The name 'A2LA Website' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
When iParentID=30 and strTexte="A2LA Website"

What an I doing wrong?

Table:
ID int(identity)
ParentID int
Text nvarchar(50)
Valeur nvarchar(50) Allow Null
Ordre int Allow Nulls

Public Sub addItemToDdSelection(ByVal strTable As String, ByVal iParentID As Int16, ByVal strTexte As String)
Dim connect As New SqlConnection(strConnection)
Dim cmdSelect As New SqlCommand("AddDropDownContent", connect)
Dim paramReturnValue As SqlParameter
Dim strError As String

cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
cmdSelect.Parameters.Add("@intParentID", iParentID)
cmdSelect.Parameters.Add("@strTexte", strTexte)
Try
connect.Open()
cmdSelect.ExecuteNonQuery()
connect.Close()
Catch ex As Exception

strError = ex.Message
Finally
If connect.State = ConnectionState.Open Then
connect.Close()
End If
End Try
End Sub

ALTER PROCEDURE dbo.AddDropDownContent
(
@intParentID int,
@strTexte varchar(50)
)
AS
EXEC ('INSERT INTO DropDownMenus (ParentID, Texte) VALUES(' + @intParentID + ',"' + @strTexte + '")')

View 3 Replies View Related

Optimizing Store Proc

May 17, 2006

I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it.
Thanks set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn]
(
@strSearchTermColumnNamenvarchar (50),
@strSearchTermSearchTermnvarchar (200)
)

as

if (@strSearchTermColumnName = 'Monitor')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@strSearchTerm) or contains(Monitor2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'MonitorSerial')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@strSearchTerm) or contains(Monitor2Serial,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'Microscope')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2
FROM Cl_Systems
WHERE contains(Microscope1,@strSearchTerm) or contains(Microscope2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'SerialMicroscope')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2
FROM Cl_Systems
WHERE contains(SerialMicroscope1,@strSearchTerm) or contains(SerialMicroscope2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'Controller')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2
FROM Cl_Systems
WHERE contains(Controller1,@strSearchTerm) or contains(Controller2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'ControllerFirmware')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2Firmware
FROM Cl_Systems
WHERE contains(Cont1Firmware,@strSearchTerm) or contains(Cont2Firmware,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'SerialController')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2
FROM Cl_Systems
WHERE contains(SerialController1,@strSearchTerm) or contains(SerialController2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'Joystick')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2
FROM Cl_Systems
WHERE contains(Joystick1,@strSearchTerm) or contains(Joystick2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'JoystickFirmware')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2Firmware
FROM Cl_Systems
WHERE contains(Joy1Firmware,@strSearchTerm) or contains(Joy2Firmware,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'SerialJoystick')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2
FROM Cl_Systems
WHERE contains(SerialJoystick1,@strSearchTerm) or contains(SerialJoystick2,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'Camera')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4
FROM Cl_Systems
WHERE contains(Camera1,@strSearchTerm) or contains(Camera2,@strSearchTerm) or contains(Camera3,@strSearchTerm) or contains(Camera4,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'CameraSerial')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4Serial
FROM Cl_Systems
WHERE contains(Camera1Serial,@strSearchTerm) or contains(Camera2Serial,@strSearchTerm) or contains(Camera3Serial,@strSearchTerm) or contains(Camera4Serial,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'ZMotor')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3
FROM Cl_Systems
WHERE contains(ZMotor1,@strSearchTerm) or contains(ZMotor2,@strSearchTerm) or contains(ZMotor3,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'Stage')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3
FROM Cl_Systems
WHERE contains(Stage1,@strSearchTerm) or contains(Stage2,@strSearchTerm) or contains(Stage3,@strSearchTerm)
return 0
end

if (@strSearchTermColumnName = 'Lens')
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3
FROM Cl_Systems
WHERE contains(Lens1,@strSearchTerm) or contains(Lens2,@strSearchTerm) or contains(Lens3,@strSearchTerm)
return 0
end

View 4 Replies View Related

Problem With Store Proc

Mar 19, 2002

Hello everyone,

I am trying to code a store procedure using nested cursor and I don't know where I am doing wrong.
I am getting message:

Server: Msg 170, Level 15, State 1, Procedure sp_alicare_99, Line 212
Line 212: Incorrect syntax near 'first_cursor'.

Can someone please help me with this, it's very urgent and I have to use it for production purposes.

Here is the store proc:

-- 05/24/2000 V1.0
-- populate Los Table

print 'sp_alicare_99'
go
if exists(select name
from sysobjects
where name='sp_alicare_99'
and type='P')
drop procedure sp_alicare_99
go
CREATE PROC sp_alicare_99
As
Begin
/**********
* sp_alicare_99 (V603.132)
***********/

set nocount on
declare

@diagnosis_code char(6) -- patient diagnostic code
,@age_group char(1) -- patient age_group
,@diagnosis_procedure char(1) -- patient diagnosis_procedure
,@category_code char(1) -- patient category_code
,@regioncode char(1) -- patient regioncode
,@sum_level char(1) -- patient sum_level
,@total_pts char(8) -- patient total_pts
,@average_stay char(3) -- patient average_stay
,@variance char(3) -- variance
,@pct10 char(2) -- pct10
,@pct10gt99 char(1) -- pct10gt99
,@pct25 char(2) -- pct25
,@pct25gt99 char(1) -- pct25gt99
,@pct50 char(2) -- pct50
,@pct50gt99 char(1) -- pct50gt99
,@pct75 char(2) -- pct75
,@pct75gt99 char(1) -- pct75gt99
,@pct90 char(2) -- pct90
,@pct90gt99 char(1) -- pct90gt99
,@pct95 char(2) -- pct95
,@pct95gt99 char(1) -- pct95gt99
,@pct99 char(2) -- pct99
,@pct99gt99 char(1) -- pct99gt99
,@icd9_code char(6)
,@cpt4_code char(6)

declare first_csr cursor for
select
icd9_code
,cpt4_code
from los_copy

open first_csr

fetch next from first_csr into

@icd9_code
,@cpt4_code

While @@fetch_status = 0

begin
declare ext001_csr cursor for
select
diagnosis_code
,age_group
,diagnosis_procedure
,category_code
,regioncode
,sum_level
,total_pts
,average_stay
,variance
,pct10
,pct10gt99
,pct25
,pct25gt99
,pct50
,pct50gt99
,pct75
,pct75gt99
,pct90
,pct90gt99
,pct95
,pct95gt99
,pct99
,pct99gt99
from
los
where
diagnosis_code = @icd9_code

open ext001_csr

fetch next from ext001_csr into

@diagnosis_code
,@age_group
,@diagnosis_procedure
,@category_code
,@regioncode
,@sum_level
,@total_pts
,@average_stay
,@variance
,@pct10
,@pct10gt99
,@pct25
,@pct25gt99
,@pct50
,@pct50gt99
,@pct75
,@pct75gt99
,@pct90
,@pct90gt99
,@pct95
,@pct95gt99
,@pct99
,@pct99gt99

if @@fetch_status = 0
Begin

-- insert data
insert into los_load
(
diagnosis_code
,age_group
,diagnosis_procedure
,category_code
,regioncode
,sum_level
,total_pts
,average_stay
,variance
,pct10
,pct10gt99
,pct25
,pct25gt99
,pct50
,pct50gt99
,pct75
,pct75gt99
,pct90
,pct90gt99
,pct95
,pct95gt99
,pct99
,pct99gt99

)
values
(
@cpt4_code
,@age_group
,@diagnosis_procedure
,@category_code
,@regioncode
,@sum_level
,@total_pts
,@average_stay
,@variance
,@pct10
,@pct10gt99
,@pct25
,@pct25gt99
,@pct50
,@pct50gt99
,@pct75
,@pct75gt99
,@pct90
,@pct90gt99
,@pct95
,@pct95gt99
,@pct99
,@pct99gt99

)

fetch next from ext001_csr into
@diagnosis_code
,@age_group
,@diagnosis_procedure
,@category_code
,@regioncode
,@sum_level
,@total_pts
,@average_stay
,@variance
,@pct10
,@pct10gt99
,@pct25
,@pct25gt99
,@pct50
,@pct50gt99
,@pct75
,@pct75gt99
,@pct90
,@pct90gt99
,@pct95
,@pct95gt99
,@pct99
,@pct99gt99

close ext001_csr
deallocate ext001_csr

fetch next from first_cursor
into
@icd9_code
,@cpt4_code

close first_cursor
deallocate first_cursor

go
if not exists(select name
from sysobjects
where name='sp_alicare_99'
and type='P')
print '>>ERROR:procedure sp_alicare_99 not created.'
go

Thanks in advance!!

View 1 Replies View Related

Store Proc - All Where Statement For Int

Nov 3, 2000

Hey,
How do I put anything there for an INT type???
It is in a stored procedure and I have six parameters that I am passing in, some are blank and I want to put a default all variable in the where statement.

Select tbl_EventDate.EventDate , tbl_EventDate.EventDateID_p FROM tbl_EventDate WHERE EventDateID_p = %

This does not work.... I tried * too..... hmmmm....

View 1 Replies View Related

Store Proc Problem

Mar 10, 2004

Hi,

Below is my store proc:

create procedure usp_find_case_by_date_usrcode_client

@usrcode as varchar(5) = '%',
@disch_dt_start as varchar (12) = '%',
@disch_dt_end as varchar (12) = '%',
@client_id char(2) = '%'
as

select distinct t.patient_id,p.first_name,p.last_name,convert(char (12),p.birthdate,101) Birthdate,sex,t.auth_id,t.place_of_service,convert (char(12),t.discharge_date,101) Discharge_date, i.service_id from patient_transaction t
inner join inpatient_service i on t.patient_id = i.patient_id and t.tran_id = i.tran_id
and CONVERT(VARCHAR,ISNULL(i.service_id ,0)) LIKE @usrcode and t. place_of_service = '1' and t.decision in ('1', '2') and
t.discharge_date between convert(char(12),@disch_dt_start,101) and convert(char(12),@disch_dt_end,101) and
CONVERT(VARCHAR,ISNULL(substring(t.patient_id,1,2) ,0)) LIKE @client_id
inner join patient p
on t.patient_id = p.patient_id
order by 1

I want to get all clients if I don't specify the parameter @client_id
but when I do that I get 0 records. What am I doing wrong??

Thanks in advance.

View 6 Replies View Related

Create A Store Proc

Mar 22, 2006

samir khatri writes "hello sir,

i m new in the world of database so please help me to create a perticular store proc

i want to get data from 3 tables and 2 field of a table is match with the same field of another table and it cant work
i write that code so u can understand easily but it dont work so please modify that and make it workable

select a.a_dis_id,b.name as fromname,b.name as toname,a.a_dis_km,c.a_all_name,c.a_all_rate from
a_distance a,levels b,a_allowance c where
a.a_dis_from=b.cid and a.a_dis_to=b.cid and c.a_all_id=a.a_all_id

Thank You
Waiting for yur reply....."

View 3 Replies View Related

Help On Store Proc CURSOR

Aug 28, 2007

---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot.
select id,fname,lname,sponsor from masterfile where id='TARZAN'

---from those 20 rows there is id that sponsored some one else
---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan)
---but Shaggy has sponsored 2 others
select id,fname,lname,sponsor from masterfile where id='SHAGGY'

---will display 3 rows and if from one of those 3 others that belongs to shaggy
---I also want to get their information ID,fname,lname
---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches

---Explain:
---Let's assume that we have an OAK Tree that has 4 main branches
---and out of those 4 main branches 2 of them have other branches with leaves under it

--I would like to do this process in a cursor (Store Proc) is possible
--the way I have it now taking way too long
--because in within so many (do while loop)

TIA
Please pardon me, I could not find better layout to explain this.

View 4 Replies View Related

Transact SQL :: How To Delete Row With Condition

Aug 6, 2015

create table target
(Taid int, studenid int, std int,div int,year int ,ActivityId int)
insert into target values(1,1,1,2015,1),
(1,1,1,2015,2),(1,1,1,2015,3),(1,1,2,2015,1)
,(1,1,2,2015,4) ,(1,1,2,2015,3);

create table activity(ActivityId int,Activity nvarchar)
insert into activity values (1,'sport')
,(2,'swim')
,(3,'read')
,(4,'dace');

I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like this

that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked

I want to update above information means unchecked alredy check data should delete and new check data add , already check data update

I have use merge statement but problem is that at single time only one row execute that is check because data come from data gridview.

View 5 Replies View Related

Transact SQL :: Using Isnull In Where Condition

Jul 10, 2015

I am in assumption that we cannot do NULL value comparison in where clause directly (results will not be proper) we have to use ISNULL. Is my understanding correct? Is using ISNULL efficient when we have to this operation for large volume?

View 5 Replies View Related

Transact SQL :: How To Apply Condition

Jul 24, 2015

I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.

View 7 Replies View Related

Store Proc & File System

Dec 21, 2001

I was wondering if anybody can help me in a storeProc for SQL 7 which can get the realational tables frm a database and put them into filesystem...

Cheers
--Nik

View 1 Replies View Related

Store Proc Execution Plan

Jun 19, 2003

Is there anyway to force sql server to use the same execution plan?

One of the sp for web page takes about 2 minutes to execute. Once it's executed through query analyser, it takes relatively less time.

Is there any explanation for this?

View 5 Replies View Related

Validating Keywords Thru Store Proc

Jul 1, 2002

Hello everyone,

I am looking for a store procedure which validates certain keywords like delete,truncate,update,insert etc. and restricts them to be used by users in all of my store procs which takes strings as inputs.

Thanks.

View 3 Replies View Related

Exec DTS Package From Store Proc In SQL 2K

Nov 8, 2000

What command do you use to run a DTS package from a stored procedure....
The XP Copy is not working???

Thanks,
~Lee

View 1 Replies View Related

Modify All Store Proc In DB In One Shot

May 19, 2004

Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??

I have so many created allready and it will be to long to go throught each one of them to modify my text inside.

what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!

any advise or example..

thanx.

View 4 Replies View Related

Change A Date In Store Proc

Apr 4, 2008

Hello i have this store proc with the syntax below. The getdate get the current date but i need to change the date this one time to 3/20/2005. I was wondering is there a way to do that an not modify my sp. I tried to harcode 3/20/2005 as asofdate and i get all 0 in my table.

getDate() AS AsOfDate'

View 4 Replies View Related

Store Proc Locked After Crash...

Jan 25, 2008

Hi all,

One of the Sql server crashed and when the server restart, the Store Procs have locked icon. Can't modify or do anything with it, but the proc still run. What could have cause this, and how can I remove the lock icon?

thanks

View 1 Replies View Related

Store Proc Backup / Transfer

Jul 26, 2006

QuestionWhat is the best way to transfer Stored Procedures to another db without having to script it?

WHYWe need to transfer stored procedures from one development database to another on different machines not on the same network.

PlatformStudio 2005
SQL Server Management Studio Express
advTHANKSance

View 5 Replies View Related