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