Store Proc - How To Get Patient Status As Output To Form
Dec 15, 2013
I am using below code to get patient status as an out put to my form. not sure whats happening but each time I run this its not executing my last "IF" if set to "N" show me N if not show me "Y" but it is by passing my first "IF" condition and jumps to last?
The column alerts_ind shows only Y or N in the table patient_status.
The table patient_status_mstr show the description of the patient which "discharged". All I want to do is if the patient is flagged with "discharge" the columns "alerts_ind" shows "Y". but something wrong? below is the code.
Alter PROCEDURE GBCheckPatientStatus (@enc_id varchar(36), @data_ind Char(1) OUTPUT)
as
begin
declare
@alerts_ind char(1);
select @alerts_ind =pm.alerts_ind
[Code] ....
View 5 Replies
ADVERTISEMENT
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Oct 10, 2006
Vuyo writes "how do you output values from the database to a form(.NET Platform)?"
View 2 Replies
View Related
Apr 27, 2007
I have a form and a connectionString to a SQL database. If the textbox at the form is empty i want to store a null value there but when i pass this value as a parameter it brings the following error: Failed to convert parameter value from a String to a Int32..
cmd.Parameters("@Segundo_nombre").Value = txtSecondName.Text.ToString --> suposing is null it brings an Error.
cmd.Parameters.Add(Apellido)
How can i manage this? I want to store this value if it is null or not.
Also i don't know how to assign a null value to a variable. I tried with
v_flag = check_selection.check_string(v_idioma)
If v_flag = 1 Then 'la variable posee el texto Seleccione
v_idioma = DBNull.Value
but it's not working.
Thanks!!
View 1 Replies
View Related
Apr 24, 2004
Assume that if i have a product called Jacket that has the following descriptions, which written in bullet form
• 600 Denier Carbolex® coated 1000mm Polyurethane
• Anatomically fitted for the riding position
• Ballistic nylon in protective areas
• FS Rainguard® 100% waterproof/breathable jet-liner
• Polyester mesh non-allergenic liner
• Removable CE armour equipped in the hips and knees
• Padding at knees, shin, hips and kidney area
• Removable zip-out satin quilted liner
• Reflective Phoslite®
• Stretch in the knees
• Gusseted with Velcro front zip closure
• Velcro adjustable waist
• 2 front pockets
• Storm flap system
How am i going to store in the Products table ? But not all products have these descriptions, other products may have no description at all.... If I store them in a text/memo DESCRIPTION field, then , I do not know how to format them into bullet form...
I would appreciate anyone knows the solution for me... Thanks in advance !
View 4 Replies
View Related
Dec 15, 2005
Hi All, i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .Thanks in Advance . Regards ,raja
View 2 Replies
View Related
Oct 17, 2014
I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.
Also, I need to make sure the table structure will allow for new forms.
View 5 Replies
View Related
Apr 9, 2007
I have a package load all flat files under a directory. Some of them may fail because formatting issue.
What's the best way to generate a report at the end, shows the list of failed transfer? Maybe with error message.
Thanks
View 2 Replies
View Related
Jan 8, 2007
Hi guys , may I know is there any examples of store procedure/scripts for monitoring replication status and performance ? I just know about sp_replmonitorhelppublisher. Thx for the assistance.
From,
Hans
View 1 Replies
View Related
Jul 13, 2007
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
View 2 Replies
View Related
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
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
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
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
Apr 29, 2015
i have used CMS in SQL 2008 R2. i have added couple of servers in its group. i have executed a query & i need to send the query output in the form of email.
basically query is checking the rows count from couple of user tables in servers.
issue here is how to copy the data that is used by CMS? i need to work on automate the rows count in difft user table in db servers.
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 31, 2004
I try to get the TOP of the query but SQL do not allow me to do so. Is there a way to do this.
thanx
===============
Incorrect syntax near '@NbrItems'. Line 14
===============
create procedure NewsList
@ModuleID int,
@NbrItems int
as
if @NbrItems = 0
select * from TblNews where ModuleID = @ModuleID order by CreationDate DESC
else
select top @NbrItems * from TblNews where ModuleID = @ModuleID order by CreationDate DESC
GO
View 1 Replies
View Related
Mar 23, 2006
I am new to sql server 2005 but this should be easy but what ever. Could someone explain how I can edit my existing store procedure from Management Studio? Any time I do a save it wants to save a .sql file !
Thanks
View 7 Replies
View Related
