DECLARE @SdxLastName char(4)
DECLARE @TheFirstName varchar(30)
DECLARE @HitCountLastNameOnly int
DECLARE @HitCountFirstName int
DECLARE @ThresholdLastName int
DECLARE @ThresholdFirstName int
-- Set minimum row hit count for when to use the first name in addition to last name for search
SET @ThresholdLastName = 2 --if we get at least this number of rows back, ignore the first name completely
-- Set maximum row hit count for when to use the exact first name instead of first name soundex in addition to last name soundex for search
SET @ThresholdFirstName = 12 --if we get over this number of rows back, look for exact first name match
IF @AN_CustID < 1 OR @LastName = '' RETURN --required params
-- Whenever a doctor's first name is unknown, the user will enter "DR"; therefore, do not soundex the first name in this case
IF @FirstName='DR.' OR @FirstName='DR' SET @FirstName=''
-- Get the soundex of the specified physician
SELECT @SdxLastName = dbo.aif_Soundex(@LastName), @TheFirstName = dbo.aif_Soundex(@FirstName)
-- Determine how many rows are returned based solely on similarity of the last name
SELECT
@HitCountLastNameOnly = COUNT(*) FROM PhysicianMstr p (NOLOCK)
WHERE
p.AN_CustID = @AN_CustID
AND p.Active = 1
AND @SdxLastName = dbo.aif_Soundex(RTRIM(p.LastName))
-- Check if using just the last name soundex returns too few rows
IF @HitCountLastNameOnly < @ThresholdLastName
BEGIN
-- Determine how many rows are returned based on last name soundex or first name soundex
SELECT
@HitCountFirstName = COUNT(*)
FROM PhysicianMstr p (NOLOCK)
WHERE
p.AN_CustID = @AN_CustID
AND p.Active = 1
AND (
@SdxLastName = dbo.aif_Soundex(RTRIM(p.LastName))
OR @TheFirstName = dbo.aif_Soundex(RTRIM(p.FirstName))
)
-- If too many rows, switch to exact match on first name instead of soundex of first name
IF @HitCountFirstName > @ThresholdFirstName
BEGIN
SET @TheFirstName = @FirstName
SET @HitCountLastNameOnly = -1 --negative value indicates first name exact match, not soundex
END
END
-- List physicians with similar sounding names
SELECT
p.AN_PhysicianID,
pm.AN_CustID,
RTRIM(p.LastName) AS LastName,
RTRIM(p.FirstName) AS FirstName,
RTRIM(p.StateLicNo) AS UPIN,
RTRIM(mis.Specialty) AS Specialty1,
pm.Specialty2,
p.Active,
RTRIM(pm.Degree) AS Degree,
pm.Extra,
p.CreatedBy,
p.CreatedOn,
p.ModifiedBy,
p.ModifiedOn,
p.PLEPhysicianMIID
FROM
PLEPhysicianMstrMI p (NOLOCK)
INNER JOIN PhysicianMstr pm (NOLOCK) ON
p.AN_PhysicianID = pm.AN_PhysicianID
LEFT JOIN PLEPhysicianSpecialtyMstrMI mis (NOLOCK) ON
p.PLEPhysicianSpecialtyMIID = mis.PLEPhysicianSpecialtyMIID
WHERE
pm.AN_CustID = @AN_CustID
AND p.Active = 1
AND (
@SdxLastName = dbo.aif_Soundex(RTRIM(p.LastName))
OR @TheFirstName =
CASE
-- Include first name only when we don't have the minimum number of rows
WHEN @HitCountLastNameOnly < 0 THEN p.FirstName --exact first name match
WHEN @HitCountLastNameOnly < @ThresholdLastName THEN dbo.aif_Soundex(RTRIM(p.FirstName))
ELSE '****' --force false for first name test
END
)
ORDER BY p.LastName, p.FirstName
I have searched many forums and found some cases of people also reporting slower rendering in RS than in Management Studio / Query Analyzer. However, none of the other solution suggestions seem to make a difference for me.
I'm a VS/VB developer and have got multiple reports built -- all using stored procedures on the backend -- that all take many times longer to run than if executed via Management Studio (SSMS). My simplest proc takes a couple of parameters (no defaults included) and does a simple select against one table with a few joins. Nothing complicated. It runs in 8 secs for 6867 rows via SSMS. Through RS (running locally through Visual Studio 2005 at this point) it takes around 25-28 secs. Yet, when I'm in the report on the DATA tab (not the PREVIEW tab) the run takes the expected 8 secs ?!?!
All reports are behaving this way.
I am not using cursors. I have no default values on parameters. I have added the "WITH RECOMPILE" to the proc statement. I have "SET NOCOUNT ON" as the first line of the proc. I hate to say this, but I even connected the proc to Crystal Reports to see how it behaved. It ran in the expected 8 secs.
I've seen some mention by someone that perhaps this is a known issue of RS that it reads the proc twice. Any truth to this?
Also a couple posts have traced and demonstrated that the report is generating significantly more data "reads" via RS than through SSMS.
We're a shop that is considering a switch from Crystal to RS, but we do everything through stored procedures. I need to clear up this issue before I can go forward recommending a switch. I'm including a copy of a typical proc below for review... What am I missing? What's the deal here with RS?
IF OBJECT_ID('dbo.rpt_InactiveAccounts') IS NOT NULL
DROP PROCEDURE dbo.rpt_InactiveAccounts
GO
CREATE PROCEDURE dbo.rpt_InactiveAccounts
(@pRunDate datetime
,@pSalesperson varchar(5000)
,@pIncludeOpen char(1)
)
WITH RECOMPILE
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- CREATE/SETUP TEMP TABLE FOR USE IN PARSING MULTI-VALUED STRING INPUTS
DECLARE @NumberPivot TABLE (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=4999 BEGIN
INSERT INTO @NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
-- CREATE TEMP TABLES TO HOLD PARSED VALUES FROM MULTI-VALUE STRING INPUT PARAMETERS
DECLARE @SalespersonTable TABLE
(tmpSalesperson varchar(30))
-- PARSE OUT @pSALESPERSON PARAMETER AND STORE VALUES IN TEMP TABLE
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
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'.
'****************************************************************** '****************************************************************** 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
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
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
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
" 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***
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records. 2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is: I run the package in the SQL SERVER 2005 Store Procedue like this:
do dtexec.exe package.dtsx loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower. the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why? The package don't drop the object it create during the loop in the Store Procedue ? Thanks!
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
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 + '")')
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
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) ***********/
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....
@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??
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
---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.
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.
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 !!
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.
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?
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
Please stick with me for a second... If someone dealt with tables related to US State/County and ZIP. We found a company on the Web which have ALL US counties, states, zips and cities in one big table. We need somehow to break this table( about 76K records) into multiple related tables and estabsish Some sort of relationship among these tables. 1.State table(design) ID State_Name 2.County table( design) ID County_Name Relationship_to_State For example if state = 'NY'; then all Counties falls in NY should have relationship '01'. For NJ '02' and so on. 3.Zipcode table( design-same as for county)
If someone has any idea how to solve this problem that would be really appreciated. Thanks a lot in advance.
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
For extracting the link structure of a dependency network with a large number of nodes (for problems having a large number of variables), we have been using the stored procedure:
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 .