ROW_NUMBER() Function Is Not Recognized In Store Procedure.

Dec 24, 2007

Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change.

CREATE PROCEDURE GetProductsOnCatalogPromotion
(@DescriptionLength INT,
@PageNumber INT,
@ProductsPerPage INT,
@HowManyProducts INT OUTPUT)
AS
-- declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion bit,
OnCatalogPromotion bit)
-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1
-- return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
AND RowNumber <= @PageNumber * @ProductsPerPage

View 4 Replies


ADVERTISEMENT

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

SQL2K5 SP2 - 'ROW_NUMBER' Is Not A Recognized Function Name

Aug 15, 2007

The row_number functions doesn't seem to be operable in our version of SQL Server 2005 SP2.

We have completely tried everything, including using the sample database, and Query posted in the 2005 SQL Server 2005 Book, which is as follows:


SELECT empid, qty,

ROW_NUMBER() OVER(ORDER BY qty) AS rownum

FROM dbo.Sales

ORDER BY qty;


I get the following error:


Msg 195, Level 15, State 10, Line 2

'ROW_NUMBER' is not a recognized function name.

Below you will find the versions of SQL we are using of 2005.


Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Please help!! Thanks in Advance,

Jason

View 4 Replies View Related

Store Procedure Insert Into Function

Aug 10, 2014

I have 2 tables

First Table is CUSTOMERS with the columns (CustomerId, CustomerName) Second Table is LICENSES with the columns (LicenseId, Customer) The column customer from the second table is the CustomerId from the First table

I wanted to create a store procedure that insert values into table 2

Insert into Licenses (Customer) Values( Same As CustomerId)

HOW i can get this data from the other table?

View 11 Replies View Related

Call Store Procedure From Function

Nov 13, 2007

Hi,is there any method to call a store procedure into a function?ThanksFabio

View 1 Replies View Related

'min' Is Not A Recognized Function Name

Oct 8, 2004

I am using MS SQL Server 2000. I am reading right off of microsofts web site under Trasact-SQL Reference, on how to use the MIN function. Yet when I try to use it, I get the error:

'min' is not a recognized function name

Here is the sql statement copied right out of Query Analyzer:

select name, count(name), min(
case when not(var6 = '') then 7 else
case when not(var5 ='') then 6 else
case when not(var4 ='') then 5 else
case when not(var3 ='') then 4 else
case when not(var2 ='') then 3 else
case when not(var1 ='') then 2 else 1 end end end end end end as cols
)
from syitabs
where dupef=0
group by name

View 2 Replies View Related

'EncryptByPassPhrase' Is Not A Recognized Function Name.

Mar 2, 2006

I am trying to run the following code in SQL Server 2005:

DECLARE @cleartext NVARCHAR(100)

DECLARE @encryptedstuff NVARCHAR(100)

DECLARE @decryptedstuff NVARCHAR(100)

SET @cleartext = 'XYZ'

SET @encryptedstuff = EncryptByPassPhrase('12345', @cleartext)

SELECT @encryptedstuff

SET @decryptedstuff = DecryptByPassphrase('12345', @encryptedstuff)

SELECT @decryptedstuff



and am recieving an error:

Msg 195, Level 15, State 10, Line 5

'EncryptByPassPhrase' is not a recognized function name.

Msg 195, Level 15, State 10, Line 7

'DecryptByPassphrase' is not a recognized function name.



It appears as though this EncryptByPassPhrase and DecryptByPassphrase as supported in 2005 T-SQL commands but when I execute this code in SQL Server Studio it errors out.

Anyone know why?

View 1 Replies View Related

'INITCOLVS' Is Not A Recognized Built-in Function Name.

Nov 27, 2007

I have read all of the messages related to the above problem and none have solved the issue.
We have migrated a SQL 2000 database to SQL 2005.  Detached from one and attached to the other and then based on some of the conversation I also used the SQL Server 2005's copy option and copied the database.  Detached the old one and renamed the new database which was created in the copy process to the old name.  Basically getting back to where I started.  By the way there are no triggers generated during the process (as was mentioned), nothing to delete as some of the discussion was suggesting.  
Big databse, lots of tables and stored procedures.  All screens seem to work except when we try to update some data using a stored procedure which takes the data and updates the table.  End up getting the above error during the execution.  All used to work fine with SQL Server 2000.  I have taken the SQL out of the stored procedure and executed manually and it worked but leaving the stored procedure alone and calling it with the updated data we get the INITCOLVS problem.  The database is also set to be compatible to 90 so that suggestion also has not fixed the problem.  Looking for additional suggestions and solutions.  Some one was talking about making a dummy INITCOLVS function, have not done this yet but don't particularly like this suggestion, like to know the cause and the proper solution to the problem.
Again in summary:
Migrated SQL 2000 database to 2005
Have done the copy and attach process to make sure the whole conversion to SQL Server 2005 has taken place.
Have set the compatibility of the database to 90
Updates cause the above error. 
 
 Thanks in advance.
 Don 
 

View 1 Replies View Related

Date Is Not A Recognized Built-in Function Name

Mar 21, 2013

how to format the last part of this query which comes from Access.:

SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID
FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID
WHERE TITLES_WARNING_SENT_qry.TitleID Is Null
GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID, TITLES.TitleID
HAVING (TITLES.RecDT<Date()-31)

I am getting:
'Date' is not a recognized built-in function name.

It is probably something simple but how would I go about converting this part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy with?

View 4 Replies View Related

SQL 2012 :: AVG Is Not A Recognized Built-in Function Name

Nov 10, 2014

I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4 'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me?

SELECT
TT.PartNumber
,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]
,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])
FROM dbo.vt_TroubleTicket TT
WHERE TT.Closed = '-1'
and TT.DateClosed between '1/1/2013' and '1/1/2014'
and (TT.PartNumber = '12345')
GROUP BY TT.PartNumber

View 3 Replies View Related

OBJECT_SCHEMA_NAME Is Not A Recognized Built-in Function Name

Jan 28, 2008

why I am getting this error on the SQl Server 2005 but not getting it on the SQL Server Express? and how to ifx?





Code Snippet

Msg 195, Level 15, State 10, Procedure sp_check_sp, Line 14
'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.


this is my code:





Code Snippet

USE shefa
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_check_sp]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DB_NAME(st.dbid) DBName
, OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName
, OBJECT_NAME(st.objectid, dbid) StoredProcedure
, MAX(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = 'proc'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid, st.dbid),
OBJECT_NAME(objectid, st.dbid)
ORDER BY MAX(cp.usecounts)
END

View 4 Replies View Related

SQL Error 'INITCOLVS' Is Not A Recognized Built-in Function Name.

Jan 30, 2007

Hello All,
Can anyone tell me what is  error is and how to resolve it?
Thanks.

View 4 Replies View Related

FREETEXT And CONTAINS Keywords Generate `not A Recognized Function Name`

Sep 14, 1998

I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.

View 2 Replies View Related

SQL Server 2012 :: Getting A Variable Recognized In A Function

Apr 4, 2014

I am having a hard time getting a variable recognized in a function. The variable is not being seen properly in the charindex function.

@ExtType contains = X
@PhoneNo contains = +1 (202) 123-9876 X012

select @intPos = charindex(@ExtType,Upper(@PhoneNo))

View 1 Replies View Related

The Function Is Not Recognized By SQl Server Compact Edition

Sep 8, 2007



Unfortunatly this appers when i am trying to execute the following Query :


SELECT CurrentDate AS EXPR2
FROM [Date]
WHERE (DAy(CurrentDate) = 2)

It seems it doesn't support the (Day) function...Is there any solution or work around for that ?

View 1 Replies View Related

Using Row_Number Without Stored Procedure

Nov 30, 2006

Is there a way to use Row_Number without using a stored procedure? All the examples on the internet are using stored procedure.

View 10 Replies View Related

Using Joins Inside Stored Procedure With Row_Number

May 18, 2007

This example is working: 
Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
 and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'.
I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.

View 2 Replies View Related

&"MAXRECURSION&" Keyword Not Recognized Within Function?

Mar 20, 2007

Koroner writes "Hi all. I'm very new to SQL programming so when I started coding I already expected some problem like the one I now describe to present itself soon.
I'm not an English native speaker, pardon me for making any mistake.

I'm currently programming in ASP.NET using Visual Studio.

I've defined a function which consists of one quite simple query calling itself in a recursive way. The problem occurrs when running a view to check whether the function works prorperly, since the "nesting limit exceeded" error (not exact words) pops up.
I didn't know there was such a limit.

Searching the Web I've learnt there is a way to modify this limit, that is using the OPTION clause and the MAXRECURSION hint.

But the fact is whenever I add the OPTION clause to the SELECT of my function Visual Studio tells me that there is a syntax error near "OPTION" keyword. I'm sure VS is not recognizing the "MAXRECURSION" keyword, because there are no syntax errors and the word is not colored in blue (unlike other keywords).

What puzzles me most is that I tried to define a recursive SELECT provided with OPTION clause within a view or an on-the-fly query and all works fine there.

Thanks for any reply."

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

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

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

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.

Sep 12, 2006

 Hi everybody,   I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End


oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">&quot;@CatId&quot;</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); &lt;&lt;--- Error occured here


 

View 1 Replies View Related

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.

Jan 19, 2007

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View 9 Replies View Related

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied

Mar 26, 2007

Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
 
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
 
Thanks Nickdel68

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

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

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

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

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







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