Hewlp Needed Creating A Stored Proceedure
Feb 21, 2006
Can someone please help me....I have created a DNN module that works on
the test site but when I upload the module zip to a new site I get an
error on creating my stored proceedure as follows:
StartJob
Begin Sql execution
Info
Executing 01.00.00.SqlDataProvider
StartJob
Start Sql execution: 01.00.00.SqlDataProvider file
Failure
SQL Execution resulted in following Exceptions:
System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'.
Line 51: Incorrect syntax near '@Str_Title'. at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText) at
DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean
UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder
tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF
ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID],
[PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner],
[Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like
COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text
'%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN
@SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either
field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text],
[DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID =
@PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like
COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN
DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC
EndJob
End Sql execution: 01.00.00.SqlDataProvider file
The SP looks like this:
/* -------------------------------------------------------------------------------------
/ ListTAS_Journal
/ ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@PortalID int,
@SortOrder tinyint = NULL,
@Str_Title varchar(100) = '',
@Str_Text varchar(100) = ''
AS
IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = ''
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @PortalID AND
(Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY
(CASE
WHEN @SortOrder = 1 THEN DateAdded
WHEN @SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @PortalID AND
(Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY
(CASE
WHEN @SortOrder = 1 THEN DateAdded
WHEN @SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
This SP works on the test site.
Any help would be creatly apreciated
Mark
View 2 Replies
ADVERTISEMENT
Mar 23, 2008
Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Thanks.
View 3 Replies
View Related
Feb 21, 2007
Hi people,
I'm using the following SP to return the rank of users but I really want it to return just a single Row column and also a Count() of the numer of Users there are....at the moment it sending me the whole table of ranked users.
Any ideas?
ALTER PROCEDURE dbo.RankUsersOnScore
(
@UserID INT
)
AS SET NOCOUNT ON;
SELECT Row, Score, UserID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Score DESC)
AS Row, Score, UserID FROM dbo.tblUserStats )
AS tblUsersRanked
View 1 Replies
View Related
Mar 2, 2004
I was using a proceedure i created in code but because of the way i am using it i decided that a stored proceedure will work better
i have a few add proceedures that work for insert and update but when i tried a select command, no matter what the data entered it returns the first record in the table when I fill the dataset does any one have a clue as to why it would do this
is there something you have to return through the stored proceedure
like you do when you use @@Identity
this is what i currently have,
CREATE PROCEDURE Location_Select
@p1 char(15)
AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
go
do i need this? and if so what can i return i tried LocationID and it yelled at me saying that is an invalid column name
CREATE PROCEDURE Location_Select
@p1 char(15),
@retval int output
AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
SET @retval =LocationID
GO
thankyou
View 2 Replies
View Related
Apr 16, 2004
How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the db
View 17 Replies
View Related
Jul 20, 2005
Hope someone can help.I am trying to write a stored proceedure to display sales activity by monthand then sum all the columbs.The problem is that our sales year starts in April and end in March.So far I have been able to get the sales info my using to sp's, one that saymonth >3 and the other says <4. I pass in a year parameter, that for thisyears figures would be 2003 for sp1 and 2004 for sp4.I am sure there is a better way.Below is a copy of one of my sp's.Hope you are able to help.JohnALTER PROCEDURE dbo.sp_SalesAnalFigures_P1(@Year nvarchar(50),@CCode varchar(50),@SCode varchar(50),@OType varchar(50))AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) ASMonth, SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS Sales,SUM(dbo.InvoiceItemsCostQry.TotalCost) AS Cost,SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet -dbo.InvoiceItemsCostQry.TotalCost) AS Margin,COUNT(dbo.InvoiceHeaderTbl.InvoiceNo) AS NoOfInvoices,AVG(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS AverageValueFROM dbo.InvoiceHeaderTbl INNER JOINdbo.InvoiceItemsCostQry ON dbo.InvoiceHeaderTbl.InvoiceNo =dbo.InvoiceItemsCostQry.InvoiceNoWHERE (DATEPART(yyyy, dbo.InvoiceHeaderTbl.InvoiceDate) = @Year) AND(dbo.InvoiceHeaderTbl.CompanyCode LIKE @CCode) AND(dbo.InvoiceHeaderTbl.SalesManCode LIKE @SCode) AND(dbo.InvoiceHeaderTbl.OrderType LIKE @OType)GROUP BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)HAVING (DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) > 3)ORDER BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)
View 6 Replies
View Related
Jan 4, 2007
Howdy team,
How would I increment a field of type 'int' with a stored proceedure with sql2005
Thanks.
View 5 Replies
View Related
Jan 28, 2004
I've got a stored procedure which contains a fast_forward cursor. I was wondering whether it is possible to pass in an sql query string into this stored procedure for the cursor, i.e:
Create Procedure (@sqlString as text)
as
DECLARE aCursor CURSOR
FAST_FORWARD
FOR @sqlString
View 4 Replies
View Related
Mar 8, 2004
CREATE PROCEDURE BatchID_Select
@Que bigint,
@retval varchar(10) OUTPUT
AS
SELECT BatchID FROM t_Que WHERE (QueID = @Que)
SET @retval =BatchID
GO
This stored proceedure will not let me save is there any reason why
if i take out the set line it says the syntax works
if i leave this line in however it says that batchid is not a valid column name
can you only return @@ variables?
i just cant figure out what its problem is
View 2 Replies
View Related
Oct 16, 2007
yet another question unfortunately
I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,
ie
say i have select projectid, project name from projects into temp from projects.
how can i then loop around all the rows in temp, to call my stored proceedure for each record?
in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.
can this be done for sql?
I am trying to do something like
for each record in #temp (projectid, project name) find the stored sprceedure value
so my end result will look like
projectid, project name, @storedproceedure return value
lprojectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
any help appreciated
View 1 Replies
View Related
Jul 20, 2005
Hope you can give me some advise.I am wanting to build a databse driven website. I am using Access toconnect to an SQL 2000 server to create tables etc.I am using ASP/ASP.Net to build my site.The question I have is on best method to retrive data. Lets say I havea Table of Products, and one of those fileds is CurrentProduct and itsTrue or False. On my web page I want to retreive and list all productsthat are marked as CurrentProduct True.I could do this as part of the SQL statement in the web page ie"Select * From Products Where CurrentProduct=True", I could create aView that only shows Current Products and use Select * FromCurrentProductsView or I could create a stored prodceedure, that looksvery much like a view.I know a bit about Access and Queries which is why I am using accessto manage Sql, but very liitle if anything about Stored Proceedures.When should I use what? And what are the advantages / dis-advantagesof each approach?Many thanks for any help you are able to provide.
View 1 Replies
View Related
Jan 17, 2008
I am writing a bit of code for our intranet using ASP.NET C# and SQL2005. We have a program called Aboutface that is a web based firm directory. We are using the SQL database it has in order to pull data out of it and integrate it into our intranet as we dont really care for its original interface.
I am most interested in setting up relationships.
An attorney only has one secretary that supports them, but a secretary can have many attorneys they support. This is the nature of my problem. The code below is my stored procedure. I am passing in an int which is the ID, and my goal is to generate the ID of and the name of the person who supports/is supported by. With 1 to 1 relationships, it works fine.. With more than that, it blows up because its finding multuple names (of attorneys being supported by a secretary)
I was told I might want to use a FOR EACH loop in the SP. I assume I would want to also generate a COUNT variable (to be used in the procedure and to also build the rows of my table outside of it.) and a NAME variable for each name it finds to populate the table.... but from there I am not sure if I am on the right lines of thinking or where to begin.
Any thoughts/suggestions would be greatly appreciated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int)
AS
BEGIN
DECLARE @First as varchar(200)
DECLARE @Middle as varchar(50)
DECLARE @Last as varchar(200)
SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id ) SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'') AS FullName
END
View 1 Replies
View Related
Mar 1, 2004
I have some data that I am inputing and if the record already exists i would like to add data to the fields that are not populated ONLY if they are not populated
for example
Already in the table
address= "123 this place"
city= ""
State="MI"
Zip="48462"
FirstName=""
LastName=""
being added
address= "123 this place"
city= "Ontate"
State=""
Zip="48462"
FirstName="Person"
LastName="Guy"
once i find that this record already exists because of the address and zip (which i already have complete)
I would like it to update the City first name and last name in the data that is already in the table. thank you for your help
View 4 Replies
View Related
Jul 23, 2005
How do you set a stored proceedure for automatic execution?--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Sep 21, 2007
Hi,
I have a table from which I need to create a report via MSRS2005, however the data in the table is awful in its construction and I was hoping to be able to use a stored proceedure to create a new table in which I can manupulate the data, but my T-SQL programming skills aren't that clever, so if anyone can offer any advice I'd be most grateful:
In the existing table there are two columns; StartDate and EndDate which is pretty self explanitory - what I would like to do is create a new table with only one date column and if there is more than one day between StartDate and EndDate I would like it to fill in every date in between.
For example, if the StartDate is 01/06/2007 and the EndDate 10/06/2007 I'd like the new table to list dates 01/06/2007 through 10/06/2007 inclusive in one column.
Is this possible? All suggestions welcome.
Thanks in advance,
Paul
View 1 Replies
View Related
Nov 15, 2006
I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..
can the stored proceedure send the text?
View 1 Replies
View Related
Feb 6, 2008
I have a stored proceedure that is adding a record to a database table. When the record is added using an insert statement, the ID field is autogenerated.
I have a second insert statement that inserts into a second table, however, I want/need? to use that ID field in order to link this additional information to the proper record in the initial table.
Is there an easy way to do a select or just pull the ID? I was thinking I could do a select before the final insert using 2 or 3 required fields of which used in a select altogether would be unique, but before I did that, I wanted to see if I was missing a better way. I posted the code below....
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Add_Employee_Data]
(
@LastName as varchar(100),@FirstName as varchar(100),
@MiddleName as varchar(100),@Position as varchar(100),
@Department as varchar(100),
@DirectDial as varchar(100),
@Ext as varchar(100),
@Fax as varchar(100),
@HomePhone as varchar(100),
@CellPhone as varchar(100),
@Partner as varchar(100),
@TimeKeeper as varchar(100),
@Notary as varchar(100),
@Practice as varchar(100),
@SummerAddress as varchar(250),
@SummerPhone as varchar(100),
@LegalNonLegal as varchar(100),
@Bar as varchar(100),
@OtherEmail as varchar(100),
@HomeAddressComplete as varchar(100),
@HomeAddress as varchar(100),
@HomeCity as varchar(100),
@HomeState as varchar(100),
@HomeZip as varchar(100),
@School as varchar(100),
@Degree as varchar(100),
@Status as varchar(100),
@Floor as varchar(100)) AS
DECLARE @Code as varchar(100)
INSERT Into tblMain2(LastName,FirstName,MI,Position,Dept,Extension,DirectDial,[FAX DID],HomePhone,CellularPhone,SpousePartner,
TimeKeeper,Notary,PrGroup,SummerAddress,SummerPhone,LegalNonLegal,Bar,OtherEmail,HomeAddressComplete,HomeAddress,HomeCity,
HomeState,HomeZip,Floor,Active)
Values(@LastName,@FirstName,@MiddleName,@Position,@Department,@Ext,@DirectDial,@Fax,@HomePhone,@CellPhone,@Partner,@TimeKeeper,
@Notary,@Practice,@SummerAddress,@SummerPhone,@LegalNonLegal,@Bar,@OtherEmail,@HomeAddressComplete,@HomeAddress,
@HomeCity,@HomeState,@HomeZip,@Floor,@Status)
<<<<< Put select statement here to pull in @Code where LastName = @LastName and Extension =@Ext ??
INSERT Into Education(Code,CollegeSchool,DegreeCert) Values(@Code,@School,@Degree)
View 6 Replies
View Related
Sep 2, 2004
Hi
Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records
item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete
my view should be
itemgroup status
1 incomplete
2 complete
All the Statuses are not predefined...they get added as and when required........
Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.
Thanks
View 6 Replies
View Related
May 14, 2008
I know the method where you select each and every table and manually create foreign key relationships to other tables in the database visually or by sql. Is there any way in SQL server 2005, to create these relationships using wizard, where it checks for the same column names in different tables and creates the relationships. If yes, please let me know how to do it in SQL server 2005.
View 3 Replies
View Related
Feb 16, 2007
I want a stored procedure to run everytime the batch Status field is set to 'Released'
<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]
AS
DECLARE @RC int
DECLARE @Batch int
SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)
EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch
</Code>
View 9 Replies
View Related
Mar 20, 2007
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
View 2 Replies
View Related
Jan 22, 2008
I am using a poker application that imports all played hands into a PostgreSQL database. I'm trying to write some queries for that database so I can make good use of the data. I figure PostgreSQL and SQL server probably have similar language. And I know these forums are great. So I'm posting here for help.
This is a working code snippet that retreives three properties of a player:
Code Snippet
SELECT sum(totalhands) AS HANDS, sum(vpiphands) AS VPIPHANDS, sum(pfrhands) AS PFRHANDS
FROM compiledplayerresults WHERE compiledplayerresults_id in (
SELECT compiledplayerresults_id FROM compiledresults WHERE play
SELECT player_id FROM players WHERE playername='PLAYERNAME'));
This is all I can do with my skills right now. However I very much want to learn to query for more things:
- I'd like to be able to make a query for several players at the same time
- I'd like to be able to get the aggregate properties for all players who are not PLAYERNAME1 or PLAYERNAME2
- I'd like to be able to get the aggregate vpiphands property from all players whose totalhandsis > 100 and < 1000.
I hope you can help me with that. If you can, that will make me very happy and grateful! Thanks!
View 4 Replies
View Related
Mar 13, 2006
Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.
So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.
Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.
Thanks Steve
View 7 Replies
View Related
Jan 30, 2008
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View 1 Replies
View Related
Mar 12, 2007
my store proceedure gets the id:
CREATE PROCEDURE createpost( @userID integer, @categoryID integer, @title varchar(100), @newsdate datetime, @story varchar(250), @wordcount int) asDECLARE @newNewsID integer
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
SELECT @newNewsID = @@IDENTITY
then im calling it in the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046"); con.Open(); cmdselect = new SqlCommand("createpost", con); cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@userID", userID); cmdselect.Parameters.Add("@categoryID", categoryID ); cmdselect.Parameters.Add("@title", title.Text ); cmdselect.Parameters.Add("@newsdate", newsdate.Text ); cmdselect.Parameters.Add("@story", story.Text ); cmdselect.Parameters.Add("@wordcount", "1" ); int valueinserted = cmdselect.ExecuteNonQuery(); Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted); con.Close();
as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?
View 2 Replies
View Related
Mar 12, 2007
i have a store procedure which i need to get the returned id from how do i do this??
the sp:
CREATE PROCEDURE createpost(
@userID integer,
@categoryID integer,
@title varchar(100),
@newsdate datetime,
@story varchar(250),
@wordcount int
)
as
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
im calling it from asp, maybe i need to do something here as well im a bit lost really.. any help be great!
the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();
cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@userID", userID);
cmdselect.Parameters.Add("@categoryID", categoryID );
cmdselect.Parameters.Add("@title", title.Text );
cmdselect.Parameters.Add("@newsdate", newsdate.Text );
cmdselect.Parameters.Add("@story", story.Text );
cmdselect.Parameters.Add("@wordcount", "1" );
int userinserted = cmdselect.ExecuteNonQuery();
Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id=1");
View 1 Replies
View Related
Aug 8, 2007
I have a created a stored procedure which gets the values from the 'Orders' table. The procedure goes like this:
CREATE PROC DataSource_GetDetails@OrderId int
ASDECLARE @ColNumber int
SELECT @ColNumber = (Select Coalesce(Col_length('Orders','BranchId'),0))
IF(@ColNumber >0)
BEGIN
SELECT Orders.OrderID,
Orders.Buyer,Orders.Seller
Orders.BranchIDFROM Orders
WHERE Orders.OrderID = @OrderId
END
ELSE
BEGIN
SELECT Orders.OrderID,
Orders.Buyer,Orders.Seller
FROM Orders
WHERE Orders.OrderID = @OrderId
END
Here i check whether a column named 'BranchId' exists in the 'Orders' table in a particular database and based on the result 2 different select statements are returned. The problem is, if i run this stored proc in the database which does not have a column 'BranchId' it throws me an errorMsg 207, Level 16, State 3, Line 1
Invalid column name 'BranchID'.
How do i tackle this in the stored proc?
Thanks
View 22 Replies
View Related
Jun 22, 2005
SP's are still not my forte, but getting there!I am trying to hit two tables in this one and return the values but keep getting the error code = 1 built into the sp and can't see why.CREATE PROCEDURE dbo.retrieveScores @studentId VARCHAR(20), @courseId VARCHAR(20), @lessonLocation VARCHAR(20) OUTPUT, @lessonStatus VARCHAR(20) OUTPUT, @lessonScoreRaw VARCHAR(10) OUTPUT, @lessonScoreMin VARCHAR(10) OUTPUT, @lessonScoreMax VARCHAR(10) OUTPUT, @objId VARCHAR(20) OUTPUT, @objStatus VARCHAR(20) OUTPUT, @objScoreRaw VARCHAR(10) OUTPUT, @objScoreMin VARCHAR(10) OUTPUT, @objScoreMax VARCHAR(10) OUTPUT ASBEGIN DECLARE @errCode INT
DECLARE c_courseScores CURSOR FOR SELECT lessonLocation, lessonStatus, lessonScoreRaw, lessonScoreMin, lessonScoreMax FROM courseScores WHERE studentId = @studentId AND courseId = @courseId FOR READ ONLY
OPEN c_courseScores FETCH c_courseScores INTO @lessonLocation, @lessonStatus, @lessonScoreRaw, @lessonScoreMin, @lessonScoreMax CLOSE c_courseScores DEALLOCATE c_courseScores
DECLARE c_objScores CURSOR FOR SELECT objId, objStatus, objScoreRaw, objScoreMin, objScoreMax FROM objScores WHERE studentId = @studentId AND objId = @objId AND courseId = @courseId FOR READ ONLY
OPEN c_objScores FETCH c_objScores INTO @objId, @objStatus, @objScoreRaw, @objScoreMin, @objScoreMax CLOSE c_objScores DEALLOCATE c_objScores
IF( @@FETCH_STATUS <> 0 ) BEGIN SET @errCode = 1 GOTO HANDLE_APPERR END
SET @errCode = 0 RETURN @errCode
HANDLE_APPERR: IF( CURSOR_STATUS( 'local', 'c_courseScores' ) >= 0 ) BEGIN CLOSE c_courseScores DEALLOCATE c_courseScores END
SET @errCode = 1 RETURN @errCodeENDGOSuggestions?Thanks all,Zath
View 1 Replies
View Related
Sep 24, 1998
Hi,
I am trying to create a SP where selected fields are passed as parameters. Can
this be done ?
Thank you for your time,
Robert
View 2 Replies
View Related
Sep 30, 2007
userid logintime logouttime subject question answer correct status
1000 9/2/2007 12:10:10 chemistry 0
1000 chemistry 1 T 1
1000 chemistry 2 F 0
1000 chemistry 3 a 1
1000 chemistry 4 a 0
1000 chemistry 5 b 1
1000 9/2/2007 12:20:30 chemistry 1
3000 9/2/2007 12:40:00 Math 0
3000 Math 1 c 1
3000 Math 2 a 1
3000 Math 3 T 0
3000 Math 4 F 1
3000 Math 5 b 0
3000 9/2/2007 12:50:25 Math 1
2000 9/2/2007 12:10:10 Biology 0
2000 Biology 1 T 1
2000 Biology 2 F 0
2000 Biology 3 a 1
Logintime means time person started the test.
Logouttime means time person stopped the test.
Correct 1 means correct answer
Correct 0 means wrong answer
Status 0 means test is started.
Status 1 means test is stopped. (Some time people don't stop test due to network failure. So that record got no status 1 or logouttime).
i need to find total score of correct answer.
Based on below table, i need to find who passed and who failed.
Subject Total_Question Main_Question Pass_Score
Math 5 3,5 3
Biology 5 4 4
Chemistry 5 1,2 5
i need to write a stored procedure to do calculation.....so i want to find the below result......
userid logintime logouttime Duration Subject Score Question_Missed Result
1000 9/2/2007 12:10:10 9/2/2007 12:20:30 10:20 Chemistry 2 2 Fail
3000 9/2/2007 12:40:00 9/2/2007 12:50:25 10:25 Math 3 3,5 Pass
2000 9/2/2007 12:10:10 Biology 2 4 Fail
How can i combine both table and get this result?...Please help me. I need help.
View 13 Replies
View Related
Mar 31, 2007
Hey all,
Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation]
@Id int,
@Name varchar
AS
DELETE FROM Classifieds_Terminals
WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this:
USE [CLASSIFIEDSDB2.MDF]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RemoveLocation]
@Id = 18,
@Name = N'Terminal A'
SELECT 'Return Value' = @return_value
GO
It returns 0 and nothing happens....???
Here is the table:
id | Name
18 Terminal A18 Terminal B18 Terminal C
View 2 Replies
View Related
May 1, 2007
Hi,
I have a stored procedure which returns a dataset after some intense calculations. It actually calculates the daily work hours (wages) for each employee in a month. I am using a temporary table where for each employee, a day's calculations are stored as a row. So if e.g. there are 2 employees, a total of 2 x 31 = 62 rows are saved in the temporary table. In the end i flip the temporary table horizontally (each employee row than displays daily calculations for upto 31 days possibly) to produce a monthly report. The problem is that as the data has grown alot, it takes a very very long time (more than a minute) to calculate these values. The actual time is consumed in flipping the table horizontally. User can select any montly from Jan-Dec.
Can any expert given me some advice on redefining this stored procedure (to optimize for speed) where i can calculate monthly wedges (each day included in the report) for each employee (over 400). In short, we can look at all daily calculations (31 days possibly) in one row for each employee.
Regards....
View 7 Replies
View Related
Jul 20, 2007
How do most people handle database searches for things like a product database? What I mean by that is your typical product table may look like:
ProductIDProductTitleProductDescriptionProductCategoryIDEtc...
Assuming you have a Full Text Catalog set up on the ProductDescription field, would you use Dynamic Sql or a Stored Procedure?
I'm wrestling with this because I haven't found a good way to either parse a parameter in SQL to make a stored procedure work with the same flexibility as Dynamic TSQL, and I would prefer not to have any direct access to the table from the application.
My other option (which to me isn't a great one) is to have a fairly large number of optional "keyword" parameters in the stored procedure and then parse them on the application side...this is less than favorable in that in theory someone could pass more keywords than I've allotted in my stored procedure.
How would you approach this challenge?
Thanks in advance.
Ryan
View 2 Replies
View Related
