sql result:
startdate enddate price
2004-1-2 2004-1-4 677
2004-1-5 2004-1-8 900
2004-1-9 2004-1-12 2000
i want display in page this format:
2004-1-2 2004-1-3 2004-1-4 2004-1-5 ... 2004-1-12
677 677 677 900 2000
how can i write store procedure
Hi,
I am new to using store procedures Can anyone please check and tell me whats wrong in it
create procedure Poll_Grid
@top int,
@ofset int,
@orderby varchar(50)
as
if (@orderby = "question")
select top @ofset substring(question,1,50) question, startdate, username,categoryname from polls
join users on polls.userid= users.userid
join category on polls.categoryid= category.categoryid
where pollid not in (select top @top pollid from polls order by Substring(@orderby,1,50))
order by substring(@orderby,1,50)
else
select top @ofset substring(question,1,50) question, startdate, username,categoryname from polls
join users on polls.userid= users.userid
join category on polls.categoryid= category.categoryid
where pollid not in (select top @top pollid from polls order by @orderby)
order by @orderby
I want to check if @orderby is passed as question it should fire the first query other wise last and also I want to dynamically insert the Ofset and top value for my paging require ment
Please help..
I have these 3 tables that are related to each other. I am trying to change the brand name of a product and the type of the product. How can I do this? 1 CREATE TABLE Products
2 (
3 ProductID int IDENTITY (1,1) PRIMARY KEY,
4 ProductSKU nvarchar(100) NOT NULL UNIQUE,
5 ProductName nvarchar(255),
6 ProductDescription nvarchar(MAX),
7 isInStock bit,
8 SalePrice decimal (18,2),
9 UnitPrice decimal (18,2),
10 isOnSale bit,
11 isSpecial bit,
12 isActive bit,
13 ProductRating int,
14 ProductImageBig varchar(255),
15 ProductImageMedium varchar(255),
16 ProductImageSmall varchar(255)
17 );
18
19 CREATE TABLE Brands
20 (
21 BrandID int IDENTITY (1,1) PRIMARY KEY,
22 BrandName nvarchar(255) NOT NULL UNIQUE
23 );
24
25 CREATE TABLE Types
26 (
27 TypeID int IDENTITY (1,1) PRIMARY KEY,
28 TypeName nvarchar(150) NOT NULL UNIQUE
29 );
30
31 // The store procedure I am having trouble with.
32
33 CREATE PROC SetProductsSKU_Brand_Type
34 @ProductID int,
35 @BrandName nvarchar(255),
36 @TypeName nvarchar(150),
37 @ProductSKU nvarchar(100),
38 @ProductName nvarchar(255),
39 @isInStock bit,
40 @SalePrice decimal (18,2),
41 @UnitPrice decimal (18,2),
42 @isOnSale bit,
43 @isSpecial bit,
44 @isActive bit
45 AS
46 UPDATE Products, Types, Brands
47 SET [BrandName] = @BrandName, [TypeName] = @TypeName, [ProductSKU] = @ProductSKU, [ProductName] = @ProductName, [isInStock] = @isInStock,
48 [UnitPrice] = @UnitPrice, [isSpecial] = @isSpecial, [isActive] = @isActive
49 FROM Products prod INNER JOIN ProductsBrands pb
50 ON prod.ProductID = pb.ProductID INNER JOIN Brands b
51 ON pb.BrandID = b.BrandID INNER JOIN ProductsTypes tp
52 ON prod.ProductID = tp.ProductID INNER JOIN Types t
53 ON tp.TypeID = t.TypeID
54 WHERE prod.ProductID = @ProductID
55 AND [ProductSKU] = @ProductSKU
56 AND [ProductName] = @ProductName
57 AND [isInStock] = @isInStock
58 AND [UnitPrice] = @UnitPrice
59 AND [isSpecial] = @isSpecial
60 AND [isActive] = @isActive
61 AND b.BrandName = @BrandName
62 AND t.TypeName = @TypeName
Some one please tell me how do I write store procedure that receives/takes parameter values
I want to write store procedure which takes ID as a parameter
some one tell me how do I write store procedure that takes parameter
if possibel please show me example of it
iam new to this
thankyou
maxs
hi,
I want to write a CLR store procedure like this:
public static void SetStateByBatch(SqlInt32[] ids)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlTransaction tran = conn.BeginTransaction();
foreach (SqlInt32 id in ids)
{
try
{
conn.Open();
string commString = string.Format("update dbo.Notification set state=1 where noid='{0}'", id);
SqlCommand comm = new SqlCommand(commString, conn);
SqlContext.Pipe.ExecuteAndSend(comm);
}
catch
{
tran.Rollback();
}
}
tran.Commit();
conn.Close();
}
}
I'know that is not correct,how to resole this problem?
thanks in advance
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
I have the following table:
CREATE TABLE [dbo].[IntegrationMessages]
(
[MessageId] [int] IDENTITY(1,1) NOT NULL,
[MessagePayload] [varbinary](max) NOT NULL,
)
I call the following insertmessage stored proc from a c# class that reads a file.
ALTER PROCEDURE [dbo].[InsertMessage]
@MessageId int OUTPUT
AS
BEGIN
INSERT INTO [dbo].[IntegrationMessages]
( MessagePayload )
VALUES
( 0x0 )
SELECT @MessageId = @@IDENTITY
END
The c# class then opens a filestream, reads bytes into a byte [] and calls UpdateMessage stored proc in a while loop to chunk the data into the MessagePayload column.
ALTER PROCEDURE [dbo].[UpdateMessage]
@MessageId int
,@MessagePayload varbinary(max)
AS
BEGIN
UPDATE [dbo].[IntegrationMessages]
SET
MessagePayload.WRITE(@MessagePayload, NULL, 0)
WHERE
MessageId = @MessageId
END
My problem is that I am always ending up with a 0x0 value prepended in my data. So far I have not found a way to avoid this issue. I have tried making the MessagePayload column NULLABLE but .WRITE does not work with columns that are NULLABLE.
My column contains the following:
0x0043555354317C...
but it should really contain
0x43555354317C...
My goal is to be able to store an exact copy of the data I read from the file.
Here is my c# code:
public void TestMethod1()
{
int bufferSize = 64;
byte[] inBuffer = new byte[bufferSize];
int bytesRead = 0;
byte[] outBuffer;
DBMessageLogger logger = new DBMessageLogger();
FileStream streamCopy =
new FileStream(@"C:vsProjectsSandboxBTSMessageLoggerInSACustomer3Rows.txt", FileMode.Open);
try
{
while ((bytesRead = streamCopy.Read(inBuffer, 0, bufferSize)) != 0)
{
outBuffer = new byte[bytesRead];
Array.Copy(inBuffer, outBuffer, bytesRead);
string inText = Encoding.UTF8.GetString(outBuffer);
Debug.WriteLine(inText);
//This calls the UpdateMessage stored proc
logger.LogMessageContentToDb(outBuffer);
}
}
catch (Exception ex)
{
// Do not fail the pipeline if we cannot archive
// Just log the failure to the event log
}
}
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!
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'.
Hi,I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.The stored procedure accepts 2 parameters like this:Create proc [dbo].[spGetPeerFromCode]( @FromCode VARCHAR(50)) (@WebVersionString VARCHAR(50))as please assist.
View 1 Replies View Related
Hi all,
I want to write a stored procedure for the following,
The table have the following field,
CategoryID, CategoryName, Parent_categoryID
123 Kids Dresses 0
321 Kids Boys 123
322 Kids Baby 123
401 Kids Boys school Uniform 321
501 Kids Boys Formals 321
541 Kids Baby school Uniform 322
542 Kids Baby Formals 322
601 Household Textile 0
602 Bathrobe 601
603 Carpet 601
604 Table Cloth 601
From the above example the categoryID acts as a parent_categoryID for some products,
when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
For example when i pass categoryID as 123 it should return the following subcategoryIDs
321,322,401,501,541,542 because these are all subcategory of categoryID 123.
How to write stored procedure for this?
Thanks!
Hai Friends,
I heard that stored procedures can be written using two servers
how to write stored procedures by using two servers
CAN ANYONE GIVE INFORMATION ABT THIS
Malathi Rao
Hi,
Please tell me how can i write a stored procedure inside another stored procedure.
With Regards,
litu
litu
Hi,
Please tell me how can i write a stored procedure inside another stored procedure.
With Regards,
litu
litu
Hi, where do you write an SQL stored procedure?
View 1 Replies View Related How to write stored procedure with two parametershow to check those variables containing values or empty in SP
if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one
Select * from orders plz write this SP for me thanks
Hi all,
I want to write a stored procedure for the following,
The table have the following field,
CategoryID, CategoryName, Parent_categoryID
123 Kids Dresses 0
321 Kids Boys 123
322 Kids Baby 123
401 Kids Boys school Uniform 321
501 Kids Boys Formals 321
541 Kids Baby school Uniform 322
542 Kids Baby Formals 322
601 Household Textile 0
602 Bathrobe 601
603 Carpet 601
604 Table Cloth 601
From the above example the categoryID acts as a parent_categoryID for some products,
when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
For example when i pass categoryID as 123 it should return the following subcategoryIDs
321,322,401,501,541,542 because these are all subcategory of categoryID 123.
How to write stored procedure for this?
Thanks!
Hai friends,
The project i'm working on is an asp.net project with SQL Server 2000 as the database tool.
I've a listbox (multiline selection) in my form, whose selected values will be concatenated and sent to the server as comma separated numbers (Fro ex: 34,67,23,60).Also, the column in the table at the back end contains comma separated numbers (For ex: 1,3,7,34,23).
What i need to do is -
1. Select the rows in the table where the latter example has atleast one value of the former example (In the above ex: 34 and 23).
2. Check the text value of these numbers in another table (master table) and populate the text value of these numbers in a comma separated format in a grid in the front end.
I've programmed a procedure for this. but it takes more than 2 minutes to return the result. Also the table has over 20,000 rows and performance should be kept in mind.
Suggessions on using the front-end (asp.net 2.0) concepts would also be a good help.
Anybody's helping thought would be greatly appreciated...
Thanks lot...
Can I write a procedure that should connect to a different SQL server and execute a series of SELECT statement on that SQL SERVER.Reason being is I do not have permission to create procedure on that destination server while I have permission to execute a SELECT statement. Please clarifyThanks,
View 3 Replies View Related I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
Thank you.
hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????
Could you write a special stored procedure for me in SQL 2005?
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= SportTagName= NewTagName= Health
I hope there is a stored procedure which can join all TagName into a single string and return,it means when I pass @IDTopic to the stored procedure, it return "Sport,New,Health"
How can write the stored procedure?
Hi All,
I need some help from you experts.
I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml.
I should be able to give this as a job to exectue daily ,etc.
Can we do this using stored procedures in SQLServer.
Pls paste some sample code, if any or direct me to any url with better info.
Thanks in advance
I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?
View 6 Replies View RelatedCan you, and if yes, how do you, write an xml file using a stored procedure. For example the sp below writes this new record to a sql table. How would I change it to write it to an xml file ?
CREATE Procedure [spOB_AddtblOB_Properties]
@strPropertyRef varchar (100),
@strRouteNo numeric,
@strAdd1 nvarchar(1000),
@strPostcode nvarchar(10)
as
Insert into tblOB_Properties (
PR_PropertyRef,
PR_RouteNo,
PR_Add1,
PR_Postcode)
values(
@strPropertyRef,
@strRouteNo,
@strAdd1,
@strPostcode)
GO
hi,
I need to write a clr stored prodeure.i have to call that stored procedure from my prediction query.My Application is going to make use of that query to get the prediction output.
Is it possible to write clr stored procedure with out using adomd server.How?
For example:
In My Assembly i am having a function PredictPerformance(),Which is having my DMX Query.I need to execute that function from my analysis service by calling like this,
select aly. PredictPerformance() FROM [Model].
how to do this?
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as
TagName= http://www.hothelpdesk.com
TagName= http://www.hellocw.com
TagName= http://www.supercoolbookmark.com
I hope there is a stored procedure which can join all TagName into a single string and return,
it means when I pass @IDTopic to the stored procedure, it return "http://www.hothelpdesk.com, http://www.hellocw.com, http://www.supercoolbookmark.com"
How can write the stored procedure?
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 RelatedHi 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
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
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
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