How To Call A Sp From A SQL Statement??
Oct 2, 2006
Hi,
I need to call a stored procdure from a SQL statement that I am running from query analyzer.
My SQL statement will select from a master table of bills. I have a stored procedure that calculates the amount due on each bill. The sp does not use the master table in the calculation.
How do I form the SQL statement to call the sp, which needs one of the master table columns as a parm?
i.e.
SELECT *, EXEC sp_abc columns name AS whatever
FROM tblMaster WHERE......
TIA!
Dave
View 3 Replies
ADVERTISEMENT
Dec 4, 2006
Hello,
i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work.
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn)
DOES NOT WORK:
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn)
Thanks in advance.
View 3 Replies
View Related
Nov 28, 2005
Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName, LastName, (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table. Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.
View 2 Replies
View Related
Jan 8, 2002
How many characters can be used in a query?
Say I have a query that inserts into a varchar(4000). I need to do ~100 of them. Can all this data be passed at once. Or would I need to break it up?
Thoughts?
Dano
View 1 Replies
View Related
Apr 17, 2004
Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.
USE NORTHWIND
GO
CREATE TABLE tbA (
Item int NOT NULL,
Value int NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tbA (Item, Value)
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2
GO
CREATE PROCEDURE usp_SquareIt
@iItem int
AS
declare @iValue int
SELECT @iValue = Value FROM tbA
SELECT @iValue * @iValue AS Result
GO
SELECT Item,
EXECUTE usp_SquareIt Item AS Squared ---- can this be done
FROM tbA
GO
DROP TABLE tbA
GO
DROP PROCEDURE usp_SquareIt
GO
Any thoughts?
Mike B
View 1 Replies
View Related
Feb 21, 2007
Is it possible to call a stored procedure in a select statement?
For example:
SELECT Buyer, Country, (exec the sp_test*) as ItemList.....
*sp_test is a stored procedure with buyer and country as the input parameters and output a concatenated item numbers (example: F12345,A1023,C40165).
View 20 Replies
View Related
Jan 30, 2008
Hi,
to fully support SQL server 2005, I'd like to use DDL statements instead of deprecated stored procdure calls, I'm having trouble "translating" this one :
sp_fulltext_catalog 'catalogName', 'start_incremental';
The only option available with ALTER FULLTEXT CATALOG are REORGANIZE and REBUILD.
Any idea ?
Thanks for your help
View 1 Replies
View Related
May 20, 2015
I have a function like below
CREATE FUNCTION [dbo].[UDF_GetCode]Â
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)
[code]...
This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.
View 3 Replies
View Related
Jun 16, 2006
I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
TIA,
barkingdog
View 1 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Mar 19, 2008
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
View 7 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Jan 8, 2008
I've written a T-SQL stored procedure that I want to call other stored procedures. The opening part of it looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spArchive] @dtArchiveBefore DateTimeASEXEC spArchiveAHD @dtArchiveBeforeGOEXEC spArchiveContract_History @dtArchiveBeforeGOEXEC spArchiveContracts @dtArchiveBeforeGO But I'm getting this error repeatedly: Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@dtArchiveBefore".I've searched for this term but can't figure out what's wrong with my code.Any ideas?Robert W.
View 4 Replies
View Related
Aug 27, 2001
Hi, There,
Do you know when should I use extend sp to call a dll file, and when should I use sp_OACreate to call dll. It seems only dll created by C++ can be called from extend sp, how about VB created dll?
Thank you very much
Tony
View 1 Replies
View Related
Jun 23, 2008
MS SQL 2005
I developed several SP that update tables
If I execute them one by one in the SQL Sever Management Studio, they work ok
Now,
I want to execute them all inside another SP
I wrote it (attached code)
Execute it and give me a message that runs successfully but that is not true
May anyone of You tell me the right syntax to do it?
[Code]
USE REPORTES
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEPROCEDURE dbo.Astral_sp_Acumula
AS
--
EXECUTE [REPORTES].[dbo].[Astral_sp_AcCpasArtC]
GO
EXECUTE [REPORTES].[dbo].[Astral_sp_AcDevArtC]
GO
EXECUTE [REPORTES].[dbo].[Astral_sp_AcVtasArtV]
GO
-- More sp executions
[Code]
Thanks
JG
View 2 Replies
View Related
Apr 3, 2006
i have made a .sql file in which i want to call another .sql file
plz tell me the syntax for it
View 5 Replies
View Related
Sep 21, 2006
Hi all,
I have 2 tables like that
CREATE TABLE tab1 (
id int IDENTITY (1, 1) NOT NULL ,
master varchar (50) NULL
) ON PRIMARY
GO
CREATE TABLE tab2 (
id int IDENTITY (1, 1) NOT NULL ,
student_name varchar (50) NULL ,
master_id int NULL
)
Now i want to create a Sp
which is
create proc inner_sp(@s1 varchar(50))
as
insert into tab1(master) values (@s1)
and i want to call it from another sp
create proc outer_sp2(@s2 varchar(50),@s1 varchar(50))
as
declare @r int
exec inner_sp @s1
set @r=Scope_Identity()
insert into tab2(student_name,master_id) values(@s2,@r)
exec outer_sp2 'Vivek1','Test'
But in table tab2 master_id column contain NULL value....
Please fix it...
Ujjwal
View 5 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
 UPDATE TABLE1
  SET TABLE1.FIELD2 = 1
 ELSE
  UPDATE TABLE2
  SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Jun 27, 2006
is it possible to call a SQL job from an ASP.NET application? if so, how? I'm using VS.net2003 and am programming in C#I've been searching for a while, but haven't found anything. It doesn't help that one of my keywords when searching is "job(s)."
View 1 Replies
View Related
Sep 14, 2006
I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.
View 4 Replies
View Related
Dec 18, 2003
Well I seem to be a gomer when trying to understand XML. Could some one please help me? I am trying to write a script or DTS package that will read 4 XML files (Do not call list) compare each phone number to the numbers I have in my DB and if there is a match put the contactID in to another table. Or at the very least get the XML phone numbers into the database. This is what the XML doc looks like
- <list type="full" level="state" val="AL">
- <ac val="205">
<ph val="2025896" />
<ph val="2061994" />
<ph val="2061995" />
<ph val="2062028" />
View 3 Replies
View Related
Apr 16, 2005
can someone point me in the right direction? I know how to call a stored procedure, but I can't seem to find code examples on how to call user defined functions.
View 2 Replies
View Related
Jan 4, 2000
Hi SQL Colleagues:
Is it possible for me to call a DTS package from within a stored procedure? If it is not possible to do so directly, would I at least be able to call the package through a job?
Thanks!!!
Don
View 1 Replies
View Related
Aug 23, 2000
Hello,
Somebody in my society has realised a function logwrite in a DLL logfil32.dll
With Sybase we can use it without problem.
It write in a file for example 'c:logmetrixloglogmetrix.txt' the text 'test logwrite'
I add logwrite in the extended stored procedures of mzster
and i tried to do :
execute master..logwrite 'c:logmetrixloglogmetrix.txt','test logwrite'
In the file logmetrix.txt 'test logwrite' wasn't be added. WHY??
Thanks to tour answers
Axel
View 2 Replies
View Related
Feb 6, 2007
Hello,
How do you call 2 columns from a table? mine wont work?
Quote: select message, id_by, id_from, name from message, members where message.id_by = message.id_by
output is ( I can't get the names );
Quote: message / by / to
“hello” / mark / jen
“hi” / shane / bill
message table; Quote:
message | by | to
“hello” | 1 | 3
“hi” | 2 | 4
members table; Quote:
id | name
1 | mark
2 | shane
3 | jen
4 | bill
View 1 Replies
View Related
Jul 18, 2006
hi, if i have query like following
select * from o_customer,o_address
where
o_customer.name = o_address.custname
is it same as
select * from o_customer
inner join o_address on o_customer.name = o_address.custname
if there are same, which one is prefer in term of performance ..
thank you for guidance
View 2 Replies
View Related
Dec 1, 2006
Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:
There are three tables: TAB1 TAB2 TAB3
There is a 1 to many relationship between TAB2 and TAB1.
For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2
I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1
Therefore I need to have a select block first and store it somewhere(?)
select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))
Then I delete all those rows in TAB1 that has a match in TAB3
delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))
now i delete those rows in TAB2 from the result set of the first sql block.
thanks for any help.
View 6 Replies
View Related
