Select + Insert Into + Stored Procedure. Please Help Me.

Oct 14, 2006

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 6 Replies


ADVERTISEMENT

How To -one Stored Procedure Select From Insert Into With Same SN

May 5, 2008

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

Select, And Insert Functions In A Stored Procedure

Feb 2, 2006

I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1

View 6 Replies View Related

'Insert Into..' Select Outputput From Stored Procedure

Dec 11, 2007



In a stored procedure, I've created a temp table, and am trying to populate it using:
insert #myTable
exec p_someProc

p_someProc returns 2 record sets, the first is the one I want to stuff into #myTable, the second is a single field.
I think that is the reason that I'm getting:
"

Insert Error: Column name or number of supplied values does not match table definition.

"

#myTable has the same structure as the first record set. Is that second recordset causing the error, if so, how to I either incorporate it into the temp table or just ignore it?

View 5 Replies View Related

SQL 2012 :: How To Write Insert Into With Select And Where In Stored Procedure

Sep 25, 2015

I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.

I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:

ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT

[Code] ....

i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement.

View 7 Replies View Related

Select From Pivot Stored Procedure And Insert Into Table

Mar 10, 2008

hi need help
how to "select from pivot stored procedure and insert into table"
this line
INSERT INTO [nili].[dbo].[tb_pivot_edit]
tnx



Code Snippet
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)


DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080401', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN

SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 30
BEGIN

SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM (

SELECT ID,new_unit,mhlka_id,mhlka,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
INSERT INTO [nili].[dbo].[tb_pivot_edit]
([Fname]
,[new_unit]
,[mhlka_id]
,[mhlka]
,[fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5]
,[fld6]
,[fld7]
,[fld8]
,[fld9]
,[fld10]
,[fld11]
,[fld12]
,[fld13]
,[fld14]
,[fld15]
,[fld16]
,[fld17]
,[fld18]
,[fld19]
,[fld20]
,[fld21]
,[fld22]
,[fld23]
,[fld24]
,[fld25]
,[fld26]
,[fld27]
,[fld28]
,[fld29]
,[fld30]
,[fld31])

END

View 1 Replies View Related

General Stored Procedure, For Insert, Update, Select, Delete?

May 7, 2007

Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin

exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out


end
 
Is this a good approach design, or its bad???
 
Thanks all

View 5 Replies View Related

Stored Procedure - SELECT INSERT Statement - Good Practice?

Jan 9, 2004

I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.

I would like to only import data since the last import so I was thinking of my logic to be like this:

INSERT INTO Table_2
(Field1, Field2, ... , FieldN)
VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))

Does this make sense? Or do you all suggest a different mannger of accomplishing this?

View 8 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SELECT Followed By INSERT All In One Stored Proc

Mar 27, 2008

Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.

Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".

The common field in both SELECT and INSERT is phonenumber.

quote:PROCEDURE [dbo].[usp_select_and_insert]

@name varchar(20),

AS

SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name
INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');

GO

Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)

View 1 Replies View Related

Stored Procedure Insert

Nov 7, 2006

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

INSERT INTO With Stored Procedure

Feb 27, 2007

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

Insert Stored Procedure Help

Oct 31, 2007

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

Help With Insert Stored Procedure

Nov 17, 2007

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

Using Insert Stored Procedure With C#

Dec 5, 2007

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

Two Insert In One Stored Procedure

Jan 28, 2008

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

Insert Using Stored Procedure

Apr 9, 2008

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

Using Insert Into ... Stored Procedure...

Feb 21, 2006

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

INSERT INTO ... From A Stored Procedure

Sep 17, 2004

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

INSERT INTO.. A Stored Procedure!

Aug 30, 2006

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

How To Select Max And Min Value In Stored Procedure

May 25, 2008

Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly? 
DECLARE @Value1 DECIMAL(7,2)
DECLARE @Value2 DECIMAL(7,2)
SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date

View 9 Replies View Related

Stored Procedure - Select LIKE

May 4, 2006

I have the stored procedure snipet below and it does not return anything.
What am I doing wrong here?  I works ok if I need all the records for the dataset and don't use the WHERE and LIKE.
CREATE PROCEDURE dbo.search
@Field      VARCHAR (20),   @KeyWord      VARCHAR (200),  @errCode          INT                 OUTPUT
ASBEGIN
DECLARE @guid    uniqueidentifier DECLARE @subject      NVARCHAR SELECT  @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord
SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
 
Thanks,
Zath

View 9 Replies View Related

Using Stored Procedure In SELECT

Jan 12, 2001

In Informix you can use a stored procedure as part of a SELECT statement:

SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date)
FROM order_table

business_day_diff is a stored procedure that uses a custom calendar to compute
business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.

Does anyone know of a straightforward way to accomplish the same purpose?

View 2 Replies View Related

Can You Do A Select On A Stored Procedure?

Aug 12, 2007

Say I have the following stored procedure that takes in 1 parameter, and to call it I would do this: exec stored_procedure_name 'param'

And it returns a result with 3 fields: column1, column2, column3

Now I'm only interested in getting the results for column1, is there a way to call the stored procedure to only return column1?

I tried this: select column1 from (exec stored_procedure_name 'param')

It doesnt' work...

View 2 Replies View Related

Use Value From One Select Within A Stored Procedure

Dec 21, 2004

Hello!

How do you use the value of one select statement within another select statement inside the same stored proc?

Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!

I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.

Any suggestions?

View 7 Replies View Related

Using SELECT TOP In Stored Procedure

Oct 13, 2005

How could I use SELECT TOP in stored procedure
For ex I want convert this line to Stored procedure

"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"

I dont want use ROWCOUNT
because its not working good it will not show some data of some fields
for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:

this is my current code


CREATE PROCEDURE [dbo].[SP_Articles]
(
@Articles int
)
AS
SET ROWCOUNT @Articles
SELECT tblArticles.*
FROM tblArticles
WHERE published = 1 ORDER BY Adate DESC;
SET ROWCOUNT 0
GO

and I use this code to call that from ASP

strSQL = "EXECUTE SP_Articles @Articles = " & intArticles

rsArt.CursorType = 1
rsArt.Open strSQL, strCon

so just tell me how to use TOP in stored procedure
Thanks

View 9 Replies View Related

SELECT From A Stored Procedure

Jan 30, 2004

Hi everybody,
How can I select records from a SP?
For example:
My SP is the following:

CREATE PROCEDURE [dbo].[spExample]
AS
Declare @SELECT_Text AS varchar(500)
Set @SELECT_Text='SELECT * FROM dbo.ExampleTable'
exec(@SQL_Text)
GO

I want to select from it:

SELECT *
FROM dbo.spExample

It doesn't work. Then how can I use the result of the SP???

View 2 Replies View Related

Select From A Stored Procedure?

Mar 16, 2004

Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?

In other words, if I have a stored proc whose last command is:

selectt.[Region_Code],
t.[Country_Code],
sum([Total]) as [Total]
from#tmp t
group by[Region_Code],
[Country_Code]
order by[Region_Code],
[Country_Code]

Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!

View 5 Replies View Related

Stored Procedure With Many Select

Sep 19, 2013

I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.

View 4 Replies View Related

Select Into Stored Procedure

Jul 20, 2005

HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks

View 6 Replies View Related

Select Vs Stored Procedure

Jul 14, 2006

I have code simililar to the following in a stored procedure:

CREATE PROCEDURE GetGroupMembers
(
@GroupID INT
)

AS
SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID
FROM THonorsMembers LEFT OUTER JOIN
TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN
VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID
WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL
ORDER BY TMembers.MemberID

RETURN
GO


When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.

Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.

View 3 Replies View Related

Select From Stored Procedure

Jan 8, 2008



I have a complex stored procedure that returns one or more nvarchar values given a syntax similar to this:

sp_MyProcedure 'Param1', 'Param2'

My issue is that the end goal is to get these values into a SQL query that I can call directly from something like Excel (no VBA or anything can be used). So far, I have come up with this:

********************************************************************************************************
CREATE TABLE [dbo].[#tmpTable] ([RetValue] NVARCHAR(30))

INSERT INTO [#tmpTable]
EXEC sp_MyProcedure 'Param1', 'Param2'

SELECT * FROM AnotherTable

WHERE (AnotherTable.ID IN (select * from [#tmpTable])) AND (AnotherTable.Year>2007)

drop table [#tmpTable]
********************************************************************************************************

That works, but it is incredibly sloppy once I have need to make 4 or 5 temp tables like that and then select from all of them inside the same query. So does anyone have any suggestions? Can I use a function to help out?

Thanks in advance,
JimmyR

View 7 Replies View Related







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