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


ADVERTISEMENT

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 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

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

Store Proc Question - Select Top @NbrItems

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

How To Edit Store Proc From Manegement Studio

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

Store Proc To Relate State-County, Etc....

May 30, 2002

Hello Everyone,

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.

View 2 Replies View Related

Transact SQL :: Include A Condition In Store Proc

Sep 29, 2015

I have an existing store proc with insert and update statements, I want to add a condition at the top of the proc with conditions like;

declare @dayofweek int
set datefirst 1
select @dayofweek = datepart(dw,getdate())
--select @dayofweek
if @dayofweek = 1

[Code] ...

If the condition meets with either of the above 2 conditions, than have to run the actual store proc.

My actual Storeproc in which I need to incorp the above conditions is:

CREATE PROCEDURE [dbo].[Load_Product]
@FileDate date = NULL
AS
BEGIN
DECLARE @EventText varchar(500),
@Rows int = 0,
@RowsTotal int = 0,

[Code] .....

View 12 Replies View Related

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 View Related

MSOLAP_NODE_SCORE Values Returned By Store Proc DTGetNodeGraph

Oct 19, 2006

Hello--

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:

System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('{model-name}', value)

The stored procedure returns a resultset with columns: [Node_type], [Node_unique_name_1], [Node_unique_name_2], and [MSOLAP_NODE_SCORE]

Are there any pointers, references or descriptions of the values of [MSOLAP_NODE_SCORE]?

Thanks,

- Paul

View 5 Replies View Related

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

Same Store Proc Run 100x Slower In Identical Sql-server Setup

May 13, 2008

Please let me know what type of Traces I can run to identify the underlying cause.



Both setup are in Dev. This is a brain teaser. Same everything but 100x slower.

declare
@AN_CustID int,
@LastName varchar(30),
@FirstName varchar(30)
select @AN_CustID = 2824, @LastName = 'XXXXX', @FirstName = 'XXXXX'

SET NOCOUNT ON

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

SELECT
@AN_CustID = ISNULL(@AN_CustID, 0),
@LastName = RTRIM(ISNULL(@LastName, '')),
@FirstName = RTRIM(ISNULL(@FirstName, ''))

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

View 6 Replies View Related

Like Statement Is SQL Stored Proc

May 29, 2008



i have a sp with the following sql statement inside it

select
name
from
customers
where
name
like
@ShopperName + '%'
--------------------------
If I paas to the store proc '%123' as the value for shoppername it will return all values in the table. Instead I should get all record starting with %123.
What could be a perfect solution

View 1 Replies View Related

Problem With If Statement In Stored Proc

Nov 7, 2006

Hi All,
I'm haveing problems with a simple if statement within a stored proc. Here is a snippet of the stored proc
    SELECT *    FROM [tbl_jobs], [tbl_users]      WHERE tbl_jobs.companyid = tbl_users.id     IF @industry = 31     BEGIN    AND industry = @industry     END
 The error message i get from enterprise manager is:
Error 156: Incorrect syntax near the keyword 'AND'
If i remove the if statement and select and select an 'industry' value other than 31 the it works fine.
Thanks

View 6 Replies View Related

Building A Sql Statement In A Stored Proc

May 12, 2008

Hi All,
What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign.
I've tried various ways to create this query and it always truncated around the point of the % sign.
Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings!
Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc.
Thanks for any help on this!
regrads
davej
@var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE'
@var3 = ''%london%' OR item_Text LIKE '%solicitor%''
@var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'
@varfull = @var1+@var2+@var3+@var4
 

View 11 Replies View Related

Store Ouput From Select Statement

Aug 25, 2004

I am running a select statment which retruns one row how I can assign that results to a memory variable ?

thanks

View 2 Replies View Related

How To Use The Data From A Store Procedure Within A SQL Statement?

Dec 6, 2007

Is it possible to do something like this?

SELECT * from (

exec Store_Procedure
)
--StartDate is a column from "Store_Procedure"
where StartDate >= @Param

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved