To Store Or Not To Store In The Db

Jul 31, 2006

Hi all

I am using .jpg images in my application, I am currently storing all my images in a file location, and placing their file path in the database. The problem with this is that I cannot display the images in crystal reports (version with vs.net 2005). I can however display the images if I store them as bytes in an image data type in my db.

So the question is should I store either the file path of the bytes of the image in the database table? What are the contraints, I know that size is one, but will speed & performance be affected?

Mike55.

View 2 Replies


ADVERTISEMENT

Store Procedure Not Store

Nov 20, 2013

My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure

exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'

I am getting below error :

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.

View 13 Replies View Related

Call Store Procedure From Another Store Procedure

Nov 13, 2006

I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]



AS

BEGIN

SET NOCOUNT ON;



DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

View 7 Replies View Related

Store Dd-MMM Only

Sep 6, 2007

I want to store only dd-MMM part of date in a DateTime field. Is it possible? If I take a varchar field, how to query while converting it to date.

View 4 Replies View Related

Help With Store Function.

Jul 6, 2006

Hi everybody.
 
I am using SQL Server 2000.
 
My users table looks lie this:
userID (primary key)
firstName
lastName
userName
userCode
Industry
Company
Office
dateCreated
Password

 
Every time that I create a new user I have to create userCode (for internal use).
userCode should be created from first letter of the FirstName, first letter from LastName and a number.
Example:
firstName:         lastName:         userCode:
Joe                   Blow                jb1
Jake                 Brinks              jb2
Ann                  Smith                as1

 
If same userCode already exist the in the same Industry and in the same Company and in the same Office then createCode function should increment the number (the third character of the userCode)  up to 9
 
I never did this before.
Any help would be highly appreciated.
Alex.
 

View 12 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 Procedure

Feb 17, 2008

Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT,  @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN  It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks 

View 4 Replies View Related

Store Procedure?

Feb 28, 2008

Hi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir

View 2 Replies View Related

Where Do You Store Your SQL Strings

Feb 28, 2008

In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you? 

View 4 Replies View Related

How To Run SQL Store Procedure Using Asp.net

Mar 5, 2008

I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
                 Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax 

View 2 Replies View Related

Store Procedures

Jan 27, 2004

hi everyone :)

I've got a store procedure(sp) on the database side ( MS SQL 2000 )

and want to access the sp via ASP.net

so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.

I've tested the SP and it does work fine

can you just give me a template of how to access the sp from ASP.net


mant thanks indeed

M

View 2 Replies View Related

Question About Best Way To Store An Up Or Down Value

Feb 11, 2004

I'm creating a table for maintenance records.

In each record many of the values are simply checkboxes.

In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?

-David

View 1 Replies View Related

Store Files In DB

Apr 26, 2004

Hi All,
I want to store files (any kind of file) in DB and retrieve them.What is the best method to do it and also please post sample code as I have never done that.
Thanks,
Kumar.

View 1 Replies View Related

Store Prodecure

Sep 13, 2004

I want to learn Store procedure whats the best way to start!
Any idea!
Thx

View 2 Replies View Related

Store A Calendar

Nov 8, 2004

Hi,

I'm developing a site in asp.net and one of the main parts is to manage a timesheet.
Now the problem i'm facing is; how do I save this timesheet in a database in a way that it's easy to save and easy to query from.
I'm thinking about problems as i.e. 1st of a month is middle of the week, so I can't save it like this:

Month Week Mon Tue Wed Thu Fri Sat Sun
1 5 .. .. .. .. .. .. ..
2 1 .. .. .. .. .. .. ..

Anyway, I got stuck to find the best method, maybe someone has made something like this?

Thanks in advance!

Pieje

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

SQL Store Procedure In Asp.net

Apr 11, 2005

I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance.
NetAdventure
 

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

Not Able To Store Boolean Value

Apr 24, 2006

Hi,
I have a FormView with a couple of TextBoxes, a DropDownList and two CheckBoxes. I have connected a SQLDataSource to the FormView with an InsertCommand to store the values entered into the different conrtrols. My problem is that the boolean values from the two CheckBoxes doesn't get stored in the Database (SQL Server Express). The columns in the DB are of type "bit", should I choose a different type? I don't see any errors just a NULL value in the DB.
I'm probably missing the obvious taken that I'm really fresh to this, so any help would be appreciated...
 
Code below:
<asp:SqlDataSource
id="SqlDataSource1" Runat="Server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TOP 1 * FROM Games"
InsertCommand="INSERT INTO Games (GameID, GameDate, Opposition, Result, FirstTeamGame, HomeGame) VALUES (@GameID, @GameDate, @Opposition, @Result, @FirstTeamGame, @HomeGame)" >
<InsertParameters>
<asp:Parameter Name="GameID" />
<asp:Parameter Name="GameDate" />
<asp:Parameter Name="Opposition" />
<asp:Parameter Name="Result" />
<asp:Parameter Name="FirstTeamGame" Type=Boolean />
<asp:Parameter Name="HomeGame" Type=Boolean />
</InsertParameters>
</asp:SqlDataSource>
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" OnItemInserted="FormView1_ItemInserted">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#E0E0E0">
<th><asp:Label ID="Label1" Text="Manage" Width="90px" Runat="Server"/></th>
<th><asp:Label ID="Label4" Text="ID" Width="60px" Runat="Server"/></th>
<th><asp:Label ID="Label2" Text="MatchDate" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label3" Text="Opponent" Width="118px" Runat="Server"/></th>
<th><asp:Label ID="Label5" Text="Result" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label6" Text="First Team Game" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label7" Text="Home Game" Width="80px" Runat="Server"/></th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<td><asp:Button ID="Button1" Text="New" CommandName="New" Runat="Server" Font-Size="8pt" Width="45px" Height="22px" /></td>
</tr>
</table>
</ItemTemplate>
<InsertItemTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<td>
<asp:Button ID="Button2" Text="Insert" CommandName="Insert" Runat="Server" Font-Size="8pt" Width="45px" Height="22px" OnClick="Button2_Click"/>
<asp:Button ID="Button3" Text="Cancel" CommandName="Cancel" Runat="Server" Font-Size="8pt" Width="45px" Height="22px"/>
</td>
<td>
<asp:TextBox id="AddGameID" Text='<%# Bind("GameID")%>' Runat="Server" Font-Size="8pt" Width="60" OnDataBinding="CreateNewGame" />
</td>
<td><asp:TextBox id="AddGameDate" Text='<%# Bind("GameDate")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>

<td><asp:DropDownList id="AddOpposition" Runat="Server" Font-Size="8pt" Width="118px" OnLoad="fillMe"/></td>
<td><asp:TextBox id="AddResult" Text='<%# Bind("Result")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:CheckBox id="AddIsFirstTeamGame" Text='<%# Bind("FirstTeamGame")%>' Runat="Server" Font-Size="8pt" Width="80" /></td>
<td><asp:CheckBox id="AddIsHomeGame" Text='<%# Bind("HomeGame")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:HiddenField id="Opposition" Value='<%# Bind("Opposition")%>' Runat="Server" /></td>
</tr>
</table>
</InsertItemTemplate>
<HeaderStyle Wrap="False" Font-Bold="True" />
<EmptyDataTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#E0E0E0">
<th><asp:Label ID="Label1" Text="Hantera" Width="90px" Runat="Server" Font-Size="10px" /></th>
<th><asp:Label ID="Label4" Text="ID" Width="60px" Runat="Server"/></th>
<th><asp:Label ID="Label2" Text="MatchDatum" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label3" Text="Motståndare" Width="118px" Runat="Server"/></th>
<th><asp:Label ID="Label5" Text="Resultat" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label6" Text="A-lags Match" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label7" Text="HemmaMatch" Width="80px" Runat="Server"/></th>
</tr>
</table>
</EmptyDataTemplate>
</asp:FormView>

View 1 Replies View Related

Store Procedure Maybe????

Jun 8, 2006

Hello, I am very new to ASP.NET and SQL Server.  I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me.
Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
 
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
 FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and  eq.SerialNo=@SerialNo
EmployeeID and SerialNo are primary keys.
I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work
CREATE PROCEDURE dbo.inventoryinfo  AS
    DECLARE @EmployeeID int    DECLARE @SerialNo  varchar(50)
    IF( @SerialNo is NULL)
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
 FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID
 ELSE
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
 FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and  eq.SerialNo=@SerialNoGO
Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
 
 

View 1 Replies View Related

Possible To Store DB On Another Drive?

Jun 19, 2002

I'm running out of space on my C. Is it possible to store additional DBs on another drive or do I need to load SQL server onto the new drive?

View 6 Replies View Related

Store Procedure

Aug 30, 2000

I want to know when does SQL flushes Store Procedure from the cache.



Thank You,
Piyush Patel

View 1 Replies View Related

Store Procedure

Dec 10, 2004

Hi, All,
I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month

Month salefromagent directsale total
Jan 1100 2300 3400
Feb 800 500 1300
..........
Dec
---------------------------------
I know we can handle this in the program use two queries.
But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue.
Betty

View 1 Replies View Related

Store Procedure Help

Nov 1, 2005

Hi all,
I need to write a sales store procedure.
The sales summary is basically group by agentCode.
But the problem is some agents have subagents.
i.e., in the sales table.
one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales.
what should I do in the store procedure.

Thanks
Betty.

View 2 Replies View Related

Help With A Store Procedure

Jan 14, 2005

I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?

Thanks

Lystra

View 5 Replies View Related

Get ID From Store Procedure

Feb 22, 2006

I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:

CREATE PROCEDURE AddEmployee
(@efname nvarchar(20),
@elname nvarchar(20)
)
AS

insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@efname,@elname)
GO



Thanks.

View 5 Replies View Related

STORE PROCEDURE And DTS

Mar 13, 2006

I created a batch file to run a DTS saved as a STRUCTURED STORE FILE. Works fine on the command promt.

Can I use a SPROC to call something like a shell command to execute that batch file? If yes, how is this be possible?

Thanks

View 2 Replies View Related

Store Procedure

Apr 20, 2004

This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure


PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);

IF Contr_tot_all = 0 THEN

Select Contr_type into sContractType from ae_contract where db_contract = db_id;

IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;

ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;

View 1 Replies View Related

Store Procedure

Apr 4, 2008

1. can a stored procedure call itself? if else how ?>
2. how to run a job for a stored procedure to exucte every day.

View 2 Replies View Related

Store Procedure

Apr 17, 2006

hello
iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure
SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "

TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page.
depending on querystring the record is selected.

View 13 Replies View Related

Store Procedure

Apr 20, 2006

hello
iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank.
if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel.
the stroe procedure is

CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100))
AS
SELECT * FROM employee_master WHERE empuser_id IN (@ID)


and this is the statement that i use in my .aspxpage to call store procedure

Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn)
cmd.SelectCommand.CommandType = CommandType.StoredProcedure
cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100))
cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID")

any suggestions wht may be wrong

View 3 Replies View Related

Store Procedure

May 24, 2006

iam using store procedure to return certain no. of columns frm table.the query is
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ")

i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records.
i tried this in query analyzer as well it runs normally returing rows.
only in store procedure it gives me blank record.
any suggestions what is wrong ?

View 11 Replies View Related

How To Store Images?

Oct 10, 2006

hi experts,
how can i store images in database? and how can i call them back?
please give me in detail.


thank you very much

View 3 Replies View Related







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