Inconsistent Order By Using Insert Into In A Stored Procedure

Inconsistent Stored Procedure Results
I'm testing some code to look up values from my database and update a specific field when certain conditions are met. I'm having trouble with some code that is giving me the results I expect when I submit one set of parameters, but is not finding anything in the database for another set, when I know the data exists.

Here's the code for my stored procedure, SP:

Code:

@flightid bigint,
@departuretime smalldatetime
SELECT flightid, flightno, departuretime, origincode, destinationcode
FROM flightschedules

WHERE flightid <> @flightid
AND departuretime = CONVERT(SMALLDATETIME, @departuretime, 120)



And here's the vbscript that calls it:


Code:

vOutboundID = 452
vReturnID = 453

'--- Get the flight details ---
strOrigin = "confirmflightdetails '" & vOutboundID & "';"
set rsOrigin = Server.CreateObject("ADODB.Recordset")
rsOrigin.Open strOrigin, objConn
response.write "origin " & rsOrigin("departuretime") & "<BR>"

strReturn = "confirmflightdetails '" & vReturnID & "';"
set rsReturn = Server.CreateObject("ADODB.Recordset")
rsReturn.Open strReturn, objConn
response.write "return " & rsReturn("departuretime") & "<BR>"

strGetOFID = "SP '" & vOutboundID & "', '" & rsOrigin("departuretime") & "';"
set rsOFID = Server.CreateObject("ADODB.Recordset")
rsOFID.Open strGetOFID, objConn

DO WHILE NOT rsOFID.EOF
response.write "OFNO " & rsOFID("flightno") & " " & rsOFID("flightid") & "<br>"
rsOFID.MoveNext
Loop

strGetRFID = "SP '" & vReturnID & "', '" & rsReturn("departuretime") & "';"
set rsRFID = Server.CreateObject("ADODB.Recordset")
rsRFID.Open strGetRFID, objConn

DO WHILE NOT rsRFID.EOF
response.write "RFNO " & rsRFID("flightno") & " " & rsRFID("flightid") & "<br>"
rsRFID.MoveNext
Loop




Here's the code for confirmflightdetails:

Code:

@flightid bigint
AS
SELECT flightid, flightno, departuretime
FROM flightschedules
WHERE flightid = @flightid



When confirmflightdetails is tested, I the proper results, as confirmed by the response.write statements:

4521092006-07-29 08:00:00
4531102006-07-29 12:05:00


I put the response.write statements and loops in so I could verify the functionality.

Here's what it produces:

out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450

Here's what it should produce:

out 452
ret 453
origin 7/29/2006 8:00:00 AM
return 7/29/2006 12:05:00 PM
OFNO 109 450
RFNO 110 451

If I do this in query analyzer:

Code:

select flightid, flightno, departuretime
from flightschedules
where flightid > 449 and flightid < 454



this is what I get from the database:

flightid flightno departuretime origin destination
4521092006-07-29 08:00:00 A C
4501092006-07-29 08:00:00 A B
4531102006-07-29 12:05:00 C A
4511102006-07-29 13:15:00 B A

What I'm trying to do is look up the chosen flight, then find the flight with the matching origin/destination (the other flight leg) on the same day.

I can't figure out why it's working for one set of parameters and not for the other.

Thanks in advance for any help!

View Replies !

---

Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View Replies !

---

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
 
below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN

 
However, how do i make it that  it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
 

CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN

 
help is needed asap! thanks!

View Replies !

---

Two Order By's In Same Stored Procedure?
Is it possible to have to ORDER BY statements in the same storedprocedure?I am trying to use the same stored procedure for two different pagesbut the data returned needs to sorted DESC on one page and ASC onanother. Below is my SP:CREATE procedure sp_getLeads@p_SortType int,@p_PropID intASSELECT ID, PropID, Name, StatusFROM LeadsWHERE PropID = @p_PropIDIF @p_SortType = '1'(ORDER BY DateCreated DESC)ELSEIF @p_SortType = '2'(ORDER BY DateCreated ASC)ENDRETURN 1GOCan someone tell me what I am doing wrong?

View Replies !

---

Conditional Order By Stored Procedure
I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.

Not done much dynamic sql so any help would be appreciated.

Fuzzy

View Replies !

---

Stored Procedure With Order By Variable
I am trying to create a stored procedure that is sent a column name and orders by the column name.   conn = New SqlConnection(SQLserver)SQL = New SqlCommand("SearchECN", conn)
SQL.CommandType = CommandType.StoredProcedure
SQL.Parameters.Add("@Search", SqlDbType.Variant)SQL.Parameters.Add("@Sort", SqlDbType.Variant)
SQL.Parameters(0).Value = Search.Text
SQL.Parameters(1).Value = "ECN.ECN"
SQL.Connection.Open()
GVECN.DataSource = SQL.ExecuteReader()
GVECN.DataBind()
SQL.Connection.Close()
SQL.Connection.Dispose()
 
Stored Procedure
@Search NVARCHAR(MAX),
@Sort NVARCHAR(MAX)
SELECT ECN.ECN, ECN.A, ECN.B FROM ECN WHERE ECN.ECN LIKE @Search OR ECN.A LIKE @Search ORDER BY @Sort
I get the following error
Msg 1008, Level 16, State 1, Line 10
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
 
Any Ideas

View Replies !

---

Dynamic ORDER BY Within Stored Procedure
I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.


CREATE PROCEDURE procedure_name
@regNum varchar(14),
@sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @regNum
ORDER BY Obs_Date DESC
GO


Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:


ORDER BY CASE WHEN @sortOrder = 1 THEN Obs_Date END DESC


but I frequently get the following error when I try to do so:

"ORDER BY items must appear in the select list if the statements contain a UNION operator"

If anyone can offer any suggestions, I would appreciate it. Thanks.

View Replies !

---

Stored Procedure -order By Question
Hi
I´m newbie using SP, so excuse me if my doubt is stupid!!!

I´m trying to do a SP with like this

create procedure Test
@arg char(10)
as

select * from table

order by @arg

go

when i try to create this i got this error:

The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

This is possible to do?What i want is the query sorted by the variable i´ve passed(in this case arg).

If someone know how to do this, please tel me.

Thanks.

View Replies !

---

ORDER BY Command In SQL Stored Procedure
How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%',
@Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@Order
GO

and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanks

View Replies !

---

Order Of Recursion In Stored Procedure
I've encountered some strange behavior in a recursive procedure I'mwriting for a bill of materials. First let me ask directly if what Ithink is happening is even possible:It seems like the procedure is not following the recursion in serialorder, but in parallel. In other words, after one instance of theprocedure calls itself, it continues executing lines below therecursion before the recursion is done. Is that possible? I lookedfor SQL Server Options that might deal with recursion or threading butI couldn't find anything.Now let me explain what's happening in terms of the BoM. All the rowsI expect are returned, but not in the correct order. Let's assume thefollowing tree:1|-2| |-5| | |-7| | -8| -6| -9|-3| |-10| |-11| | |-13| | -14| | |-15| | |-16| | -17| -12| -18| -19| -20| |-21| -22-4-23|-24-25-26This is stored in table P using MemberID and ParentID fields. Forexample,MemberID ParentID-------- --------1 NULL2 13 14 15 26 2(etc...)Based on how I wrote the recursion (I will provide the procedurebelow), I would expect output when starting from MemberID of 1 to looklike this:MemberID Depth Sort-------- ----- ----2 1 15 2 27 3 38 3 46 2 59 3 6(etc... basically, the line order of the graphical tree above, or acounter-clockwise traverse around the tree)Instead, I get this (I'll provide the whole thing because I don't seea pattern):MemberID Depth Sort-------- ----- ----2 1 15 2 23 1 210 2 37 3 34 1 36 2 39 3 423 2 48 3 411 2 413 3 512 2 524 3 525 3 618 3 614 3 615 4 719 4 726 4 720 5 816 4 817 4 921 6 922 6 10Call me crazy, but it looks like my tree was parsed in the same orderthat a set of dominos arranged in the same shape would topple. Theonly way I could see that happening is if the recursion is non-linear,allowing both children and siblings to be parsed simultaneously. Itwould also explain why my sort counter didn't increment properly, butthe depth counter is always correct.Now here are the procedures. There's also a Qty column, since this isa BoM after all, but I didn't need to mention it for my illustrationof the problem above.CREATE PROC makebom @root bigint---- This would be called by the client to find all the parts andquantities-- under a specific part (@root)--ASSET NOCOUNT ONCREATE TABLE #result (MemberID bigint, Qty bigint, Depth bigint, sortbigint)EXEC bomrecurse @root, 1, 0SET NOCOUNT OFFSELECT MemberID, Qty, Depth, sort FROM #result ORDER BY sortGOCREATE PROC bomrecurse @root bigint, @depthcounter bigint,@sortcounter bigint---- This is the recursive procedure, called once by makebom, butrecalling-- itself until the whole tree is parsed, filling the #result table--ASDECLARE @memberid bigint, @qty bigint, @nextdepth bigintDECLARE children_cursor CURSOR LOCAL FORSELECT MemberID, Qty FROM PWHERE ParentID = @rootORDER BY MemberIDOPEN children_cursorFETCH NEXT FROM children_cursorINTO @memberid, @qtyWHILE @@FETCH_STATUS = 0BEGINSET @sortcounter = @sortcounter + 1INSERT INTO #result VALUES (@memberid, @qty, @depthcounter,@sortcounter)SET @nextdepth = @depthcounter + 1EXEC bomrecurse @memberid, @nextdepth, @sortcounterFETCH NEXT FROM children_cursorINTO @memberid, @qtyENDCLOSE children_cursorDEALLOCATE children_cursorGOI'm surprised this even worked as well as it did because I'm a newbiewhen it comes to stored procedures and I put this together fromexamples I found around this group, online and in the T-SQL Help. Sofeel free to comment on other aspects of my code or approach, but I'mmost interested in understanding the behavior of this recursion.

View Replies !

---

Order BY Problem In Stored Procedure
i am creating a Stored Procedure and trying to set the value for the sort column value as a variable which is passed from my c# program. But i get an error..is there a way, where i can specify a dynamic value for the sort column in the order by clause . My Procedure is as follows :-

ALTER PROCEDURE PhoneBook_search @startdate varchar(30), @enddate varchar(30), @sortType varchar(15),

AS
DECLARE @starttime AS varchar(30)
DECLARE @endtime AS varchar(30)
SET @starttime = @startdate + " 00:00"
SET @endtime = @enddate + " 23:59"

SELECT *
FROM phonebook
WHERE (exp_date BETWEEN @starttime AND @endtime order by
@sortType )




Any help is appreciated !!!

View Replies !

---

Dynamic ORDER BY In Stored Procedure
Hello,

I have this stored procedure:
SELECT * from purchase
ORDER BY
CASE @OrderBy
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as bit)
END

the @OrderBy is a nvarchar(100) parameter
traveller_name is an nvarchar(100) field
canceled is a bit field


When I execute the stored procedure it works fine until I execute it sorting by canceled field. After that, I cannot sort it again using the traveller_name field. I get this eror:
Conversion failed when converting the nvarchar value 'Jason' to data type bit.
(Jason is a record in the traveller_name)
Removing the castings and sorting by just the column name does not help.

Any ideas?
Thank you

View Replies !

---

Dynamic Sql Where And Order By Clauses In Stored Procedure
Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate]     -- Add the parameters for the stored procedure here @finfileid int,     @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;     -- Insert statements for procedure here     SELECT     MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId,                       SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail                        / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId)                       AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId)                       AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName,                       MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName,                       MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId,                       MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM         ProjectFinFileItems INNER JOIN                       ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN                       FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN                       Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN                       Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN                       ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN                       Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid  --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)

View Replies !

---

Order By Clause With Variables In A Stored Procedure
Hi,
I need to include two input variables
in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me
to do so and gives an Error 1008.
How can i solve this problem?

Thanks for your help!!

View Replies !

---

Passing Table Name And Order By Parameter To Stored Procedure
can i pass the name of the table and the "order by" column name to stored procedure?
 i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?

View Replies !

---

Stored Procedure To Update A Display Order Field.
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID  description   DisplayOrder  Action0           item 1           0                     Moveup/move down1           item 2           1                     Moveup/move down2           item 3           2                     Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark

View Replies !

---

Inconsistent Errors Using Bulk Insert With A Format File
As part of a c# program, utilizing .Net 2.0, I am calling a sproc via a SqlCommand to bulk load data from flat files to a various tables in a SQL Server 2005 database.  We are using format files to do this, as all of the incoming flat files are fixed length.  The sproc simply calls a T-SQL BULK INSERT statement, accepting the file name, format file name and the database table as input paramaters.  As expected, this works most of the time, but periodically (to often for a production environment), the insert fails.  The particular file to fail is essentially random and when I rerun the process, the insert completes successfully.
A sample of the error messages returned is as follows (@sql is the string executed):
Cannot bulk load. Invalid destination table column number for source column 1 in the format file "\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt".
Starting spRAS_BulkInsertData.
@sql = BULK INSERT Raser.dbo.EMODT3_Work FROM '\RASDMNTTRAS_ROOTAmeriHealthworkpdclmsemodt3.20060511.0915.txt.DATA' WITH (FORMATFILE = '\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt');

The format file for this particular example is as follows (I apologize for the length):
8.0
62
1        SQLCHAR 0 1  ""  1   Record_Type  SQL_Latin1_General_CP1_CI_AS
2        SQLCHAR 0 15  ""  2   Vendor_Number  SQL_Latin1_General_CP1_CI_AS
3        SQLCHAR 0 20  ""  3   Extract_Subscriber_Number  SQL_Latin1_General_CP1_CI_AS
4        SQLCHAR 0 20  ""  4   Extract_Member_Number  SQL_Latin1_General_CP1_CI_AS
5        SQLCHAR 0 2  ""  5   Claim_Nbr_Branch_Code  SQL_Latin1_General_CP1_CI_AS
6        SQLCHAR 0 8  ""  6   Claim_Nbr_Batch_Date_CCYYMMDD  SQL_Latin1_General_CP1_CI_AS
7        SQLCHAR 0 3  ""  7   Claim_Nbr_Batch_Sequence_Nbr  SQL_Latin1_General_CP1_CI_AS
8        SQLCHAR 0 3  ""  8   Claim_Nbr_Sequence_Number  SQL_Latin1_General_CP1_CI_AS
9        SQLCHAR 0 3  ""  9   LINE_NUMBER  SQL_Latin1_General_CP1_CI_AS
10        SQLCHAR 0 1  ""  10   Patient_Sex_Code  SQL_Latin1_General_CP1_CI_AS
11        SQLCHAR 0 3  ""  11   Patient_Age  SQL_Latin1_General_CP1_CI_AS
12        SQLCHAR 0 4  ""  12   G_L_Posting_Tables_Code  SQL_Latin1_General_CP1_CI_AS
13        SQLCHAR 0 50  ""  13   G_L_Posting_Tbls_Code_Desc  SQL_Latin1_General_CP1_CI_AS
14        SQLCHAR 0 2  ""  14   Fund_TYPE  SQL_Latin1_General_CP1_CI_AS
15        SQLCHAR 0 1  ""  15   Stop_Loss_Or_Step_Down_Code  SQL_Latin1_General_CP1_CI_AS
16        SQLCHAR 0 2  ""  16   Stop_Loss_Fund  SQL_Latin1_General_CP1_CI_AS
17        SQLCHAR 0 50  ""  17   Stop_Loss_Fund_Desc  SQL_Latin1_General_CP1_CI_AS
18        SQLCHAR 0 8  ""  18   Post_Date  SQL_Latin1_General_CP1_CI_AS
19        SQLCHAR 0 1  ""  19   Rebundling_Status_Indicator  SQL_Latin1_General_CP1_CI_AS
20        SQLCHAR 0 8  ""  20   Co_Payment_Grouper  SQL_Latin1_General_CP1_CI_AS
21        SQLCHAR 0 50  ""  21   Co_Payment_Grouper_Desc  SQL_Latin1_General_CP1_CI_AS
22        SQLCHAR 0 8  ""  22   Co_Payment_Accumulator  SQL_Latin1_General_CP1_CI_AS
23        SQLCHAR 0 50  ""  23   Co_Payment_Accumulator_Desc  SQL_Latin1_General_CP1_CI_AS
24        SQLCHAR 0 8  ""  24   Co_Insurance_Grouper  SQL_Latin1_General_CP1_CI_AS
25        SQLCHAR 0 50  ""  25   Co_Insurance_Grouper_Desc  SQL_Latin1_General_CP1_CI_AS
26        SQLCHAR 0 8  ""  26   Co_Insurance_Accumulator  SQL_Latin1_General_CP1_CI_AS
27        SQLCHAR 0 50  ""  27   CI_Accumulator_Desc  SQL_Latin1_General_CP1_CI_AS
28        SQLCHAR 0 8  ""  28   Coverage_Grouper  SQL_Latin1_General_CP1_CI_AS
29        SQLCHAR 0 50  ""  29   Coverage_Grouper_Desc  SQL_Latin1_General_CP1_CI_AS
30        SQLCHAR 0 8  ""  30   Coverage_Accumulator  SQL_Latin1_General_CP1_CI_AS
31        SQLCHAR 0 50  ""  31   Coverage_Accumulator_Desc  SQL_Latin1_General_CP1_CI_AS
32        SQLCHAR 0 8  ""  32   Deductible_Grouper  SQL_Latin1_General_CP1_CI_AS
33        SQLCHAR 0 50  ""  33   Deductible_Grouper_Desc  SQL_Latin1_General_CP1_CI_AS
34        SQLCHAR 0 8  ""  34   Deductible_Accumulator  SQL_Latin1_General_CP1_CI_AS
35        SQLCHAR 0 50  ""  35   Deductible_Accumulator_Desc  SQL_Latin1_General_CP1_CI_AS
36        SQLCHAR 0 8  ""  36   Unit_Grouper  SQL_Latin1_General_CP1_CI_AS
37        SQLCHAR 0 50  ""  37   Unit_Grouper_Desc  SQL_Latin1_General_CP1_CI_AS
38        SQLCHAR 0 8  ""  38   Unit_Accumulator  SQL_Latin1_General_CP1_CI_AS
39        SQLCHAR 0 50  ""  39   Unit_Accumulator_Desc  SQL_Latin1_General_CP1_CI_AS
40        SQLCHAR 0 8  ""  40   Out_Of_Pocket_Grouper  SQL_Latin1_General_CP1_CI_AS
41        SQLCHAR 0 50  ""  41   Out_Of_Pocket_Grouper_Desc  SQL_Latin1_General_CP1_CI_AS
42        SQLCHAR 0 8  ""  42   Out_Of_Pocket_Accumulator  SQL_Latin1_General_CP1_CI_AS
43        SQLCHAR 0 50  ""  43   Out_Of_Pocket_Acc_Desc  SQL_Latin1_General_CP1_CI_AS
44        SQLCHAR 0 3  ""  44   Service_Edit_Code  SQL_Latin1_General_CP1_CI_AS
45        SQLCHAR 0 50  ""  45   Service_Edit_Code_Desc  SQL_Latin1_General_CP1_CI_AS
46        SQLCHAR 0 8  ""  46   System_Date_MEDMAS_CCYYMMDD  SQL_Latin1_General_CP1_CI_AS
47        SQLCHAR 0 8  ""  47   Last_Change_MEDMAS_CCYYMMDD  SQL_Latin1_General_CP1_CI_AS
48        SQLCHAR 0 10  ""  48   Medicare_Termination_Reason_Code  SQL_Latin1_General_CP1_CI_AS
49        SQLCHAR 0 10  ""  49   User_ID_MEDMAS  SQL_Latin1_General_CP1_CI_AS
50        SQLCHAR 0 10  ""  50   User_ID_Last_Modified  SQL_Latin1_General_CP1_CI_AS
51        SQLCHAR 0 8  ""  51   Adjudication_Date_CCYYMMDD  SQL_Latin1_General_CP1_CI_AS
52        SQLCHAR 0 9  ""  52   Adjudication_Time  SQL_Latin1_General_CP1_CI_AS
53        SQLCHAR 0 10  ""  53   Adjudication_User_ID  SQL_Latin1_General_CP1_CI_AS
54        SQLCHAR 0 9  ""  54   A_P_Batch_Number  SQL_Latin1_General_CP1_CI_AS
55        SQLCHAR 0 7  ""  55   A_P_Sequence  SQL_Latin1_General_CP1_CI_AS
56        SQLCHAR 0 3  ""  56   CPA_Batch_Number  SQL_Latin1_General_CP1_CI_AS
57        SQLCHAR 0 8  ""  57   CPA_Date_CCYYMMDD  SQL_Latin1_General_CP1_CI_AS
58        SQLCHAR 0 1  ""  58   Manual_Authorization_Flag  SQL_Latin1_General_CP1_CI_AS
59        SQLCHAR 0 50  ""  59   Fund_Description  SQL_Latin1_General_CP1_CI_AS
60        SQLCHAR 0 1  ""  60   DRG_Inclusion_Indicator  SQL_Latin1_General_CP1_CI_AS
61        SQLCHAR 0 1  ""  61   Future_Expansion  SQL_Latin1_General_CP1_CI_AS
62        SQLCHAR 0 2  "
"  62   Company_Number  SQL_Latin1_General_CP1_CI_AS



Has anyboy run across this before, or have any ideas as to what might be happening?
Thanks in advance.

View Replies !

---

Inconsistent Results From Stored Procudure
I have a stored procedure (see below), in which I would like to check if the create an identity column and make it a primary key succeeded. I check @@error after the exec statement. This used to pick up an error if the table already had an identity column. It has stopped doing that. Why? And, if this is not the way to capture the error after the exec statement, how do I do it?


CREATE PROCEDURE rasp_test3
/*
Written by Judith Farber Abraham
this procedure loops thru sysobjects looking for user tables.
If a user table, does it have a primary key?
If not, add an identity column to table and make it a primary key
*/
--would like to have sp in main db but use from all three
@fixDB nvarchar(50)--the db to which to add PKs

AS
Declare @TableName varchar(50)
Declare @TableID int
Declare @Msg varchar (50)
Declare @ColumnName varchar(50)
Declare @IndexName varchar(50)
Declare @MyCursor nvarchar(500)
declare @MyCursorC nvarchar(500)
declare @CName sysname
--Set @Msg = "********* Finished adding Ident fields *************"
/* */
/*
do for all user tables ( xtype = u )
*/
set @Mycursor = N'Declare SysCursor cursor for select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
execute sp_executesql @mycursor
open syscursor
Fetch next from SysCursor into @TableName, @TableID
/* -1 = no record; -2 = row deleted; 0 = got a row */
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
Begin /* have a user row (table) */
/* */
set @ColumnName = @TableName + 'ID'
set @IndexName = 'PK_' + @columnName

--only add ident and PK if no primary key in table
If not exists (Select * from Sysobjects where Parent_obj = @TableID and xtype = 'PK')

--add an identity column to user table and make it a Primary key

EXEC ('ALTER TABLE ' + @tablename + ' ADD ' + @columnName + ' INT IDENTITY CONSTRAINT ' + @IndexName + ' PRIMARY KEY ' )
--
Begin
--if error, assume already ident column, so find column name & make PK
print @@error
if @@error <> 0 print "jerror occured"
--set @MycursorC = N'Declare SysCursorC cursor for SELECT c.name
--FROM syscolumns c, sysobjects o
--WHERE ((c.id = o.id) AND (c.status = 128)) AND (o.name = ' + @tablename + ')'
--execute sp_executesql @mycursorC
--Open SyscursorC
--Fetch next from SysCursorC into @CName
--print @cname
--close syscursorc
--deallocate syscursorc
--Exec ('ALTER TABLE ' + @tablename + ' ADD ' + @columnName + ' INT IDENTITY CONSTRAINT ' + @IndexName + ' PRIMARY KEY ' )
--select @cname=c.name
--print c.name
End

End
Fetch next from SysCursor into @TableName, @TableID
End
--Print @Msg
Close SysCursor
Deallocate SysCursor
Return

Thanks for any help,
Judith

View Replies !

---

Insert Using Stored Procedure
I have a table with UserID, UserName, UserPassword
I have a stored procedure as follows:ALTER PROCEDURE UserInsert
@UserName varchar(50),
@UserPassword varchar(50)
AS
BEGIN
INSERT Users (UserName, UserPassword)
VALUES (@UserName, @UserPassword)
END
I have a GridView bound to the Users Table and seperate textboxes (UserName, UserPassword) on a webform. 
Couple of Questions...
1. how and/or what code do I use to execute the Stored Procedure to insert what is in the textboxes into the Table using a button click event?
2. Since UserID is autogenerated on new records....does UserID need to be in the code?
Many Thanks

View Replies !

---

Two Insert In One Stored Procedure
Hi,
I have two tables "people' and "dept". What I need is a stored procedure to insert into both tables.
I need first, insert into "people" table, and then, insert into "dept" table since the first insert returns people id(peo_id), which
is an input parameter for "dept" table.
Here is my stored procedure, but I got error:Create PROCEDURE [dbo].[insert_people]
@peo_last_name varchar(35),
@peo_mid_initial varchar(1) = null,@peo_first_name varchar(10),
@peo_address1 varchar(50) = null,
@peo_city varchar(30) = null,
@peo_state varchar(2) = null,
@peo_zip varchar(10) = null,
@peo_ph1 varchar(30) = null,
@peo_ph2 varchar(30) = null,
@peo_email varchar(40) = null,
@dept_id int, @peo_id int
 
AS
SET @peo_id = (INSERT INTO people (peo_last_name, peo_mid_initial, peo_first_name, peo_address1, peo_city, peo_state, peo_zip, peo_ph1, peo_ph2, peo_email)
VALUES (@peo_last_name, @peo_mid_initial, @peo_first_name, @peo_address1, @peo_city, @peo_state, @peo_zip, @peo_ph1, @peo_ph2, @peo_email))
INSERT INTO dept (dept_id, peo_id)
VALUES (@dept_id, @peo_id)
GO
Could somebody help out?
Thanks a lot!

View Replies !

---

Using Insert Stored Procedure With C#
I'm trying to insert the details in the "registration form" using stored procedure to my table. My stored procedure is correct, but I dunno what is wrong in my code or I dunno whether I've written correct code. My code is below. Please let me know what is wrong in my code or my code is itself wrong..... protected void RegisterSubmitButton_Click(object sender, EventArgs e)    {               SqlConnection conn = new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds;Integrated Security=SSPI");        SqlCommand cmd;        cmd = new SqlCommand("registeruser", conn);        SqlParameter par = null;        par= cmd.Parameters.Add("@fname", SqlDbType.VarChar, 30);        par.Value = RegisterFirstNameTextBox;        par = cmd.Parameters.Add("@lname", SqlDbType.VarChar, 30);        par.Value = RegisterLastNameTextBox;        par = cmd.Parameters.Add("@uname", SqlDbType.VarChar, 30);        par.Value = RegisterUserNameTextBox;        par = cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 20);        par.Value = RegisterPasswordTextBox;        par = cmd.Parameters.Add("@email", SqlDbType.VarChar, 40);        par.Value = RegisterEmailAddressTextBox;        par = cmd.Parameters.Add("@secque", SqlDbType.VarChar, 50);        par.Value = RegisterSecurityQuestionDropDownList;        par = cmd.Parameters.Add("@secans", SqlDbType.VarChar, 40);        par.Value = RegisterSecurityAnswerTextBox;        try        {            conn.Open();            cmd.ExecuteNonQuery();        }        catch (Exception ex)        {            throw new Exception("Execption adding account. " + ex.Message);        }        finally        {            conn.Close();    } }

View Replies !

---

Help With Insert Stored Procedure
I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below
ALTER PROCEDURE [dbo].[AddNewFundAllocation] @Payment_ID Int,@Receipt_ID Int,@Fund_ID Int,@Amount_allocated money,@DateEntered datetime,@EnteredBy nvarchar(50)ASSELECT     (SUM(tblReceiptsFunds.Amount_allocated) + @Amount_allocated) AS total_allocations, Sum(tblReceipts.AmountPaid) as total_paymentsFROM         tblReceiptsFunds INNER JOIN                      tblReceipts ON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@Payment_IDIF (total_allocations<total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)

Values (@Receipt_ID,@Fund_ID,@Amount_allocated,@DateEntered,@EnteredBy)

ELSE BEGIN
PRINT 'You are attempting to allocate more to funds than your total payment.'
END I get the following error when I try and save the stored procedure:
Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26
Invalid column name 'total_allocations'.
Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26
Invalid column name 'total_payments'.

View Replies !

---

Insert Stored Procedure Help
OK I have a stored procedure that inserts information into a database table.  Here is what I have so far:
I think I have the proper syntax for inserting everything, but I am having problems with two colums.  I have Active column which has the bit data type and a Notify column which is also a bit datatype.  If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns.  I tried using the set command, but it will give me a xyntax error  implicating the "=" in the Active = 1 How can I set these in the stored procedure?1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:xxxxxxxx
7 -- Create date: 10/31/07
8 -- Description:Insert information into Registration table
9 -- =============================================
10 ALTER PROCEDURE [dbo].[InsertRegistration]
11
12 @Name nvarchar(50),
13 @StreetAddress nchar(20),
14 @City nchar(10),
15 @State nchar(10),
16 @ZipCode tinyint,
17 @PhoneNumber nchar(20),
18 @DateOfBirth smalldatetime,
19 @EmailAddress nchar(20),
20 @Gender nchar(10),
21 @Notify bit
22
23 AS
24 BEGIN
25 -- SET NOCOUNT ON added to prevent extra result sets from
26 -- interfering with SELECT statements.
27 SET NOCOUNT ON;
28
29 INSERT INTO Registration
30
31 (Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify)
32
33 VALUES
34
35 (@Name, @StreetAddress, @City, @State, @ZipCode, @PhoneNumber, @DateOfBirth, @EmailAddress, @Gender, @Notify)
36
37 --SET
38 --Active = 1
39
40 END
41 GO
 

View Replies !

---

INSERT INTO With Stored Procedure
Having problem do INSERT values to my SQL DB with an StoredProcedure. SELECT works fine.
My StoredProcedure :
CREATE PROCEDURE insert_test
@id  int ,@Rubrik char(25), @Info  char(60) , @Datum datetime
ASINSERT INTO test_news(ID, Rubrik, Info, Datum) VALUES (@id, @Rubrik, @Info, @Datum)GO
The StoredProcedure  works fine in the SQL Query Analyzer
 
My Code;
int num= 1234; string rub = "KLÖKÖLKÖLKÖL"; string ino = "slökdjfkasdkfjsdakf";SqlConnection myConnection = new SqlConnection("server='SOLDANER\DAER'; trusted_connection=true; database='SeWe'");
SqlCommand myCommand = new SqlCommand("insrt_test", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add( "@id",num);myCommand.Parameters.Add( "@Rubrik",rub );myCommand.Parameters.Add( "@Info", ino);myCommand.Parameters.AddWithValue( "@Datum", DateTime.Now );
 Even tried AddWithValues
Dont get any error.....

View Replies !

---

Stored Procedure Insert
Hi,
I am having trouble inserting 2 fields in a row using a stored procedure.
This works fine:
Exec ('Insert Into NumbersPull (Number)'+ @SQL)
but when I try to insert another value into field 2 it errors out:
I try this:
Exec ('Insert Into NumbersPull
(Number,resultID) Select ('+ @SQL
+ '),' + @resultID'
)
and get this error:
ERROR: Line 2: Incorrect syntax near ')'.
Thanks,
Doug

View Replies !

---

Using Insert Into ... Stored Procedure...
Hi,
I need to insert a new user if the user (user_login) does not exist in the table (abcd_user) using a stored procedure.
I created a stored procedure called "insert_into_abcd_user". Here is the complete strored procedure...
CREATE PROCEDURE [dbo].[insert_into_abcd_user] (     @first_name  [VARCHAR](30),      @last_name  [VARCHAR](30),      @email  [VARCHAR](60),      @user_login [VARCHAR](50))  AS INSERT INTO [dbo].[abcd_USER] ([first_name],  [last_name],  ,  [user_login])VALUES (@first_name,  @last_name,  @email,  @user_login)
I need to to insert a new user if the user (user_login) does not exist int the table (abcd_User).
Any one shade on my code?
I appreciate your help in advance.
 
-- Srinivas Gupta.

View Replies !

---

INSERT INTO ... From A Stored Procedure
For example:

INSERT INTO Table_1
SELECT
Source_Table.Field_1,
Source_Table.Field_2,
Source_Table.Field_3,
???? OUTPUT parameter returnet from a stored procedure ???????
FROM Source_Table

Is posible this ?

View Replies !

---

Insert Stored Procedure
Mistake sorry

View Replies !

---

INSERT INTO.. A Stored Procedure!
I have created a stored procedure which simply inserts two records into a table. Here is my stored procedure:-
//BEGIN
ALTER PROCEDURE [dbo].[pendingcol]
@cuser varchar(100)
AS
Declare @sqls nvarchar(1000)

SELECT @sqls = 'INSERT INTO' + @cuser + '(UserName, Pending) VALUES ("recordone", "recordtwo")'

EXECUTE sp_executesql @sqls

RETURN
//END

This is the code i am using to call my stored procedure using VB.NET:-
//BEGIN
'variables
Dim user As String
user = Profile.UserName

'connection settings
Dim cs As String
cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)

'parameters
Dim cmd As New SqlCommand("pendingcol", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuser", SqlDbType.VarChar, 1000)
cmd.Parameters("@cuser").Value = user

'execute
scn.Open()

cmd.ExecuteNonQuery()

scn.Close()
//END

Now when i execute this code i get an error point to cmd.ExecuteNonQuery() that says
" The name "recordone" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. "

As far as i can see theres nothing wrong with the VB code, im guessing that the problem lies somewhere in my stored proc!

Can anyone please enlighten me on where i may be going wrong?
Cheers

View Replies !

---

Execute Insert Stored Procedure
Hi,
 I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a  temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
 
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
 
please can someone point me in the right direction

View Replies !

---

Insert Does Not Take Place If Stored Procedure Is Run From ASP.NET
Hello,
I am having an issue with the insert command in a stored procedure in SQL Server 2005. The procedure is designed to generate a Customer number and insert an initial value (a delivery charge) into an order table. Finally, it returns the customer number back to the caller. When I execute the stored procedure from SQL Server Management Studio, it works perfectly. However, when I call it from ASP.net, the procedure properly generates a Customer Number and returns it to my application, but the insert never takes place. Can someone help me figure out why it won't insert?
My ASP.Net (C#) code is here: SqlDataSource dsSelect = new SqlDataSource();
dsSelect.ConnectionString = ConfigurationManager.ConnectionStrings["obeConn"].ConnectionString;
dsSelect.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
dsSelect.SelectCommand = "getTempCustNo";

DataView reader = (DataView)dsSelect.Select(DataSourceSelectArguments.Empty);

Session["CustNo"] = reader[0]["CustNumber"].ToString().Trim();
Session["PriceCode"] = reader[0]["PriceCode"].ToString().Trim();
Session["orderNo"] = reader[0]["OrderNo"].ToString().Trim();  
  My stored procedure is here:ALTER PROCEDURE [dbo].[getTempCustNo]
AS
BEGIN
declare @CustNo int;
declare @prodDesc VARCHAR(max);
declare @prodNo int;
declare @stdPrice float;
declare @orderNo int;
declare @priceCode int;

-- populate values
SELECT @custNo = ISNULL(MAX(OD_CUSTNO)+1, 100000000), @prodNo = 999, @orderNo = 1, @priceCode = 1000
FROM [dbo].[tblOrderDet]
WHERE OD_CUSTNO >= 100000000;

-- get the price of our product
SELECT @stdPrice = PR_RATE_1
FROM tblPrices
WHERE PR_PRODNO = @ProdNo
AND PR_RCDCOD = @priceCode;

-- get the description
SELECT @ProdDesc = PM_PRDESC + ' ' + PM_VOLUME FROM dbo.tblPromas WHERE PM_PRODNO = @ProdNo;

--Add a delivery charge to the customer's order table
INSERT INTO tblOrderDet (OD_ORDRNO, OD_CUSTNO, OD_PRODNO, OD_QUANTITY, OD_ORG_QUANTY, OD_PRICE, OD_DESCR, OD_LOW)
VALUES (@orderNo, @CustNo, @prodNo, 1, 0, @stdPrice, @prodDesc, 'N');

--Return the customer values
SELECT @custNo AS 'CustNumber', @orderNo AS 'OrderNo', @priceCode AS 'PriceCode';
END 

View Replies !

---

Need Help In Creating Stored Procedure Insert
Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert
2
3 @companyid int,
4 @companyname varchar(20),
5 @address1 varchar(30)
6
7 AS
8
9 INSERT INTO companymaster
10 ( companyname, address1)
11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which
was not supplied.

The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....    

View Replies !

---

How To Invoke Insert Stored Procedure
Feeling really dumb tonight - below is my stored procedure and code behind which completes (it puts "completed" in TextBox4) but does not insert anything into database.
Questions:1) do in need to include the primary key field in the insert stored procedure?2) do I need a DataAdapter to actually get it running and open and close the connection? STORED PROCEDURE running on SQL 2000 server: ______________________________________
CREATE PROCEDURE newuser003.InsertCompanyInfo
@CS_CompanyName nchar(100),@CS_City nchar(500),@CS_Phone varchar(20)
AS
INSERT into tblCompanyInfo_Submit(CS_CompanyName, CS_City, CS_Phone)VALUES ('@CS_CompanyName', '@CS_City', '@CS_Phone')RETURN
C# CODE BEHIND: ______________________________________________________ 
public partial class ContractorSubmision : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {       }    protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("CompanyInfoSubmit", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.AddWithValue("@CS_CompanyName", TextBox1.Text);        cmd.Parameters.AddWithValue("@CS_City", TextBox2.Text);        cmd.Parameters.AddWithValue("@CS_Phone", TextBox3.Text);        TextBox4.Text = "Completed";    }}
 

View Replies !

---

Can I INSERT INTO A Temp Tbl Twice In One Stored Procedure
In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl?
Any help here is appreciated.

View Replies !

---

Insert Or Update With Stored Procedure
I'm doing this more as a learning exercise than anything else.  I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key.  If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.for starters can this even be done with a stored procedure?if so, can someone provide some guidance as to how?thanks in advance,Burr

View Replies !

---

Select + Insert Into + Stored Procedure. Please Help Me.
So I have simply procedure: Hmmm ... tak sobie myÅ›lÄ™ i ...

Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w
ten sposob co napisalem kilka postow wyzej (select ID as UID from
aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest
wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic    @user_id int,    @topic_katId int,    @topic_title nvarchar(256),    @post_content nvarchar(max)as    insert into [forum_topics] (topic_title, topic_userId,topic_katId)    values (@topic_title, @user_id, @topic_katId)       insert into [forum_posts] (topic_id, user_id,post_content)    values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id".  Can anyone help me ? I tried lots of ways but nothing works.

View Replies !

---

Can I Use A Stored Procedure For INSERT In SqlDataSource?
Hello,
I have created a web page with a FormView that allows me to add and edit data in my database.  I configured the SqlDataSource control and it populated the SELECT, INSERT, UPDATE and DELETE statements.  I've created a stored procedure to do the INSERT and return to new identity value.  My question is this: Can I configure the control to use this stored procedure?  If so, how?  Or do I have to write some code for one of the event handlers (inserting, updating???)
Any help would be appreciated.
-brian

View Replies !

---

Stored Procedure - Insert Not Working
Having a little trouble not seeing why this insert is not happening.... --snip--  DECLARE c_studId CURSOR FOR  SELECT studentId  FROM students FOR READ ONLY   OPEN c_studId  FETCH NEXT FROM c_studId INTO @studentId  IF( @@FETCH_STATUS = 0 )  BEGIN   SET @studRec = 'Found'  END CLOSE c_studId DEALLOCATE c_studId
 BEGIN TRAN IF (@studRec <> 'Found')  BEGIN  INSERT INTO students  (studentId)  VALUES  (@studentId)    END  Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath

View Replies !

---

Update/Insert Stored Procedure
I am trying to take some SQL queries written by Visual Studio, one insert and one update and combine them into a single stored procedure. The insert procedure should be included in the update procedure and a check should be done for an existing record based upon the primary key. If it exist, an update command should be performed, else an insert. I also need to wrap the procedure in a transaction and rollback if any errors have occurred, else commit the transaction. If I have the following Insert and Update statements, can anyone help me write the stored procedure I need? Again, the current statements were automatically created and could be modified as needed.

INSERT INTO tblClub(ClubPKID, ClubName) VALUES (@ClubPKID, @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @@IDENTITY)

UPDATE tblClub SET ClubPKID = @ClubPKID, ClubName = @ClubName WHERE (ClubPKID = @Original_ClubPKID) AND (ClubName = @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @ClubPKID)

Thanks!

View Replies !

---

Stored Procedure To Insert Record
I have an SP to add a record to the database but i get the error shown below. Any help appreciated.

Stored Procedure:

CREATE PROCEDURE addUser
@username char(15),
@password char(12)

AS
INSERT INTO users(username, password) VALUES (@username, @password)

GO


Code calling SP:

Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)

Dim cmd As New SqlCommand("addUser", myConnection)
MyDataAdapter = New SqlDataAdapter()
'MyDataAdapter = New SqlDataAdapter("addUser", myConnection)

With cmd 'MyDataAdapter
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@username", SqlDbType.Char).Value = username
.Parameters.Add("@password", SqlDbType.Char).Value = password
End With

Try
myConnection.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Finally
myConnection.Close()
End Try


I get an error message on the line: cmd.ExecuteNonQuery()

System.FormatException: Input string was not in a correct format.

View Replies !

---

Using A Stored Procedure To Insert A New Record
ok I have a stored procedure......
I pass in the variables that are requried....What is the best way to add a record
using my stored procedure in VB.net code in a button click event......
How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn




CREATE procedure dbo.Appt_AddAppt
(
@ClinicID int,
@AccountNum nvarchar(10),
@DOS nvarchar(12),
@POSCODE nvarchar(5)
)
as
Insert into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID)
Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)



GO

View Replies !

---

Insert Query With Stored Procedure
Hi,is it possible to create an "INSERT INTO ..... "Select from storedprocedure" Query?I want to create an temporary table. In this table I want to enter the data,which I can get from an stored procedure.But in the FROM-clause a stored procedure is not allowed?

View Replies !

---

How To Insert A Float Using Stored Procedure
I'm creating a product database and I need to store product length in decimal format (e.g. product with ID=25 has length=2.5)

I have the following code:
----------------------
prod_length = CDbl(Request.QueryString("prodLength"))
' where prodLength is a floating point number
...

' then I send to stored procedure
Set prm = conn.CreateParameter("",adDouble , adParamInput,8,prod_length)
conn.Parameters.Append prm
conn.Execute
----------------------

In my stored procedure I define the prodLength value thusly:
----------------------
CREATE PROCEDURE [dbo].[SP_UPDATE_OBJECT_SIZE]
(
@PROD_LENGTHfloat
)
----------------------

but this just doesn't work. What two floating point assignments in vbscript and SQL are compatible?? I've tried using Single,Double,Numeric with float and numeric in SQL and it either does not work or (naturally) it strips the decimal numbers off and just gives me an int. What is the solution?

Thanks!
~hJ~

View Replies !

---

Insert A Float Using Stored Procedure
does anybody know how to do this? I need to know what variable type to use in the createParameter function and what to set the receiving variable as. I'm having trouble getting variable types to match. VBScript uses double, but SQL uses float...which seems to be incompatible.

Any suggestions?

~hJ~

View Replies !

---

Insert With A If Statement. But Yet Not Using A Stored Procedure. Is That Possible?
my table:

FoodID Integer PRIMARY KEY,
FoodName varchar(255),
FoodDesc text

How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?

Thanks in advance to the people that replied to my request! Thanks so much...

View Replies !

---

&#34;UNICODE INSERT STORED PROCEDURE&#34;
I am inserting unicode data(arabic) in SQL server using insert statement as follows:

INSERT INTO tablename (col1,col2) values (N'value1,value2)

WHERE col1 is of NVARCHAR datatype.

I want to run the above statement using stored procedure This procedure will take 2 parameters please help me in solving this problem.

View Replies !

---

&#34;UNICODE INSERT STORED PROCEDURE&#34;
I am inserting unicode data in SQL server using insert statement as follows,

INSERT INTO tablename (col1,col2) values (N'value1,value2)

WHERE col1 is of NVARCHAR datatype.

I want to run the above sql statement using stored procedure This procedure will take 2 parameters please help me in solving this problem.

View Replies !

---

Stored Procedure To Run Query And Then Insert
I have a query that select rows from the employees,salary_head and salary_group tables
this is the query
SELECT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
FROM dbo.employees INNER JOIN
dbo.salary_head INNER JOIN
dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
i also have a table called payrollers1 with the following fields
payroll_id int auto
payperiod_id int
employee_id
level_id
designation_id
banks_id
bankaccount_no
salarygroup_id
Amount
I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table.
As for the payperiod_id i want the Stored procedure to look up the max payperiod value.
I am totally new to stored procedure and do not know how to write this code.
Can somebody help me with this code.

View Replies !

---

Stored Procedure To Insert Data
I have a table that has data and is indexed by the Visit #. Each month I receive new data from a text file that I have to import into this table. On all occasions the text file will have one of the existing Visit #'s contained in it.

I wrote a simple stored procedure that has the INSERT/SELECT statement but the problem I am having is when I execute the Stored Procedure and I run into a record from the text file that already exist in my table the stored procedure errors and stops at that point.

How do I write a stored procedure that will ignore that text record and continue reading the text file and insert records that do not exist?

View Replies !

---

Stored Procedure Insert Containing A SubSelect
Im not sure the best way to go about doing this

i want to pass 2 values to the procedure

one passed value will be a direct insert value

the other passed value will be used within a subselect
and the result from the subselct will be the second inserted value.



Code:



Create procedure InsertSubSelect
(
@Value1 nvarchar(50),
@Value2 nvarchar(50)
)
AS

INSERT INTO Mytable (field1, field2)
VALUES (@Value1,
(select Value2
from Mytable2
where Value2 = @Value2 ) )

Go

View Replies !

---

How To -one Stored Procedure Select From Insert Into With Same SN
hi need help do this
i have table that generate Serial
i get the code from this link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2734123&SiteID=1
 
and i must to get the same sn + date
so whan i do this


Code Snippet
select * from tbl_1a
WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
select * from tbl2_2B
WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
 
i get the current state  DATE + SN
 
 
 i need to insert into other 2 table this Serial+date  like this
--need help fix this code
 



Code Snippet
DECLARE @sn
DECLARE @date
set @date =Getdate()
-- insert 1 new records tb sn
INSERT INTO ConfirmationNumber([ConfirmationDate])
SELECT GETDATE()
---select last value sn
set @sn = SELECT top 1 SequenceNumber FROM vw_ConfirmationNumber ---- get the last sn
order by SequenceNumber desc
--insert into tb1
insert into tbl_1a (fld1,fld2,sn,date)
select from tbl_2a fld1,fld2,@sn,@date
--insert into tb2
insert into tbl2_2B (f1,f2,date)
select from tbl2_1B f1,f2,@sn,@date
 
 


TNX

View Replies !

---

Insert Image Using Clr Stored Procedure
I tried to insert a row using a clr stored procedure where a field was a varbinary(max) and the data was a jpg file. The data was truncated to 8000 bytes. A similar T-SQL sp did not truncate the data even though the parameters were identical. With the clr sp I tried varbinary, variant and image for the parameter type. The variant gave an exception. The others worked but the data was truncated.

I used sqlpipe.executeandsend. Someone asked elsewhere if the pipe had an 8000 byte limit but was told it had not.

Any ideas?

Thanks, John

View Replies !

---