Return The New Id From A Store Proceedure

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


ADVERTISEMENT

How Do I Get The Returned Id From The Store Proceedure In Asp C#?

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

How To Return Event Description From Stored Proceedure?

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

Store Procedures Return

Apr 28, 2000

Hi guys, I want to know if it is possible to retrieve the fieldnames that a
Stored Procedure return, without executing this StoreProc.

Thank you

View 1 Replies View Related

Store Procedure Can Return More Than One Value?

Dec 8, 2005

Can the store procedure can return more than one value? If i need to return 2 values, how to do that??

View 1 Replies View Related

Return From Store Procedure.

Sep 12, 2005

Dear All, I read through all the post and flipped through the books but Istill can't find the answer to my problem.I'm inserting a new record via a stored procedure and want to return the idvia scope_identity, which I thought would be preety straight forward.The code I'm using is below and this keeps giving me "Multiple-step OLE DBoperation generated errors. Check each OLE DB status value, if available. Nowork was done."How do I pick the returning id value and could anyone see were I'm goingwrong below.Many thanks for any help you can offer.CREATE PROCEDURE [sp_insert_address]@ADDR_NAME_2 [char](70),@ADDR_NO_3 [char](10),@ADDR_ROAD_4 [char](50),@ADDR_DISTRICT_5 [char](50),@ADDR_TOWN_6 [char](50),@ADDR_BOROUGH_7 [char](50),@ADDR_PCODE_8 [char](12),@addr_id [int] OUTPUTAS INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]([ADDR_NAME],[ADDR_NO],[ADDR_ROAD],[ADDR_DISTRICT],[ADDR_TOWN],[ADDR_BOROUGH],[ADDR_PCODE])VALUES(@ADDR_NAME_2,@ADDR_NO_3,@ADDR_ROAD_4,@ADDR_DISTRICT_5,@ADDR_TOWN_6,@ADDR_BOROUGH_7,@ADDR_PCODE_8)set @addr_id = scope_identity()GO

View 10 Replies View Related

Using Return Value From A Store Procedure ?

May 9, 2008

Dear all,

I have a store procedure called ValidateUser which retrtive an authneticated ID from a database.
This procedure is defined as follow :




Code Snippet
ALTER PROCEDURE [dbo].[ValidateUser]
-- Add the parameters for the stored procedure here
@LoginId int = 0 OUTPUT,
@UserName varchar(50),
@pw varchar(60),
@LineId varchar(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- return the User id as validted
SELECT @loginID = Id
FROM [NomosConfig].[dbo].Users
WHERE [UserName] = @UserName
AND [PassWord] = @pw
AND [LineId] = @LineId
-- if no record returns, the request user is not allowed
if @@ROWCOUNT < 1
SELECT @loginID = 0

END




Runing this procesdure alone work well and return correct LoginId


What I would like to do is retrieve that return LoginID inside an other procedure. For that I am doinmg as follow :




Code Snippet
DECLARE @AuthenticatedUserId int;



-- verify that user is authorized to access to PSU login
EXECUTE @AuthenticatedUserId= [ValidateUser]
@UserName ='cal'
,@pw ='cal'
,@LineId='PR495250'
SELECT @AuthenticatedUserId




What is strange is that the @AuthenticatedUserId is always retunring 0 ..
Did I write something wrong ?

thnaks fro help
serge

View 3 Replies View Related

How To Return A String Value From The Store Procedure

Jun 3, 2008

Hi All, I have to return a string value from the store procedure. If condition success    BEGIN        RETURN 'Success'      END ELSE   BEGIN        RETURN 'Fail'    ENDI am retrieving this value with ExecuteReturnQuery() method. But it gives me the error like "Conversion failed when converting the varchar value to data type int."can anyone please help me for this?Thank you.Regards. 

View 5 Replies View Related

Can Store Procedure Return Rows?

Feb 28, 2006

Can store procedure return rows?
If store procedure can return rows, how to use ASP.NET to receive it? thanks!

View 2 Replies View Related

Return String From Store Procedure?

Aug 14, 2006

How can i do ?
It's always display error : "Can' not convert nvarchar to int"
Thank you very much.

View 8 Replies View Related

Store Return Result From Sp_columns

Oct 31, 2007

I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah

View 1 Replies View Related

Handle Store Procedure Return 2 Table

Sep 20, 2006

I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?

View 1 Replies View Related

How To Get Store Procedure's Return Value And Output Parameters From BLL?

Oct 10, 2007

ex:
myprocedure(@Cusname varchar(50), @Cusid int output)as
Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY
 i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?

View 1 Replies View Related

How To Code ASP.NET Page With Return Value Store Procedure?

Apr 12, 2005

Does anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
 
Thank you all!

View 6 Replies View Related

Dataset Store Procedure Return Values

Apr 1, 2008



Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.

Thanks in advance.

Glenn

View 5 Replies View Related

Store Procedure Does Not Return All Rows Sometimes, Slow Execution...!!!

Oct 14, 2007

Dear friends,I have a problem with a simple select statement and I don't know why it is happening.I have 2 tables, Fees and FeesDataRoles. Fees presents all the fees and FeesDataRole is a middle table between Fees and Roles table. So each fee can have multiple Roles and a Role can have many Fees.Now I have a select statement:Select    *From     Fees Inner Join FeesDataRoles ON Fees.FeeID = FeesDataRoles.FeeIDWhere  (FeesDataRoles.DataRoleID = @DataRoleID) AND (FeesDataRoles.RecordStatus = 1 ) AND (FeesDataRoles.ValidFrom >= getdate() ) AND ( FeesDataRoles.ValidTo <= getdate() OR FeesDataRoles.ValidTo is null)Now it shouldn't take that long to execute this procedure but surprisingly sometimes when I insert a value to the table and then execute this store procedure it does now show the data just added. Very strange.....!!!!I ran the procedure 5 times after inserting an item and nearly 1 out of 5 does not return the right result righ. ( It does not include the recently inserted rows)Anyone have any idea....?I used Tuning Advisor, no sugestion. I change the clustered index in FeesDataRoles from FeesDataRoleID(the primary key of the table) to DataRoleID to increase the performance, still it happens sometimes.Is my Where clause so costly that cause this problem.Please help. I really appreciate your help.Regards,Mehdi 

View 2 Replies View Related

I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar

Mar 21, 2008

My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ")        Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection)        cmdUpdate.CommandType = Data.CommandType.StoredProcedure        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int))        cmdUpdate.Parameters("@QuestionID").Value = QuestionID               cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output        'open connection        dbConnection.Open()        'Execute non query        cmdUpdate.ExecuteNonQuery()        'close connection        dbConnection.Close() 

View 8 Replies View Related

Transact SQL :: Why Store Procedure Not Return Any Value / Result After Using Exec

Jul 22, 2015

I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??

USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]

[Code] ....

View 6 Replies View Related

Stored Proceedure

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

Stored Proceedure

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

DTS / Stored Proceedure

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

Help With Stored Proceedure Please

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

Increment With Stored Proceedure

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

Sql String In Stored Proceedure

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

Stored Proceedure Problem

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

Calling One Stored Proceedure From Another

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

View Or Stored Proceedure?

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

Stored Proceedure Question - Looping? Or Is There A Better Way?

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

Stored Proceedure Merge Data

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

Automatic Execution Of A Stored Proceedure

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

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

Create New Date Table With A Stored Proceedure

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

Stored Proceedure Question, How To Pull An Autogenerate Field Value?

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







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