Transact SQL :: Call Stored Procedure In Select Query?

Nov 9, 2015

I have a SELECT query that also needs to call a sproc with a column name passed in as a parameter.

Unfortunately I cannot use a TVF, as the sproc code references a TVF on a linked server.

What is the best option?

SELECT
u.UserID,
u.Username,
u.Address,
EXEC dbo.[GetResult] u.UserID AS 'Result'
FROM dbo.UserTests u

View 3 Replies


ADVERTISEMENT

SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement

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

Transact SQL :: Select And Update Query In Same Stored Procedure

May 6, 2015

I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.

Create Procedure [dbo].[myProcedure]  As    BEGIN    BEGIN TRAN T1    SET NOCOUNT ON    SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate();    UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id;    SELECT * FROM #TempTable;    DROP Table #TempTable;    COMMIT TRAN T1    END

The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.

private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) {                List<ProcessData> Data = new List<ProcessData>();  SqlCommand Command = new SqlCommand(StoredProcedure, Connection);  Command.CommandType = System.Data.CommandType.StoredProcedure;    try                {                    Command.CommandTimeout = CONNECTION_TIMEOUT;   

[code]....

The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.

View 3 Replies View Related

Transact SQL :: Call Cobol From Stored Procedure?

Jun 17, 2015

In DB2 stored procedures are calling external cobol code/program, I want convert DB2 stored procedure to MS SQL stored procedure, on that case, How to call call cobol from MS SQL stored procedure.

View 7 Replies View Related

Transact SQL :: How To Call SSIS Package From Stored Procedure

May 21, 2015

I have ssis package which is credated by VS-2010.

I want execute this SSIS package from the stored procedure (SQL server 2005).

View 3 Replies View Related

Call A Stored Procedure From A SELECT Statement

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

Call Stored Procedure Within SELECT Statement

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

Call A Stored Procedure In The Select Statement

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

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

MSSQL Stored Procedure Query Using SELECT TOP

Dec 14, 2006

Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.

View 2 Replies View Related

Transact SQL :: Stored Proc Call By Adding Right Login

May 14, 2015

I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.

View 20 Replies View Related

SELECT Query Stmt Inside Stored Procedure

Nov 21, 2005

Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr

View 1 Replies View Related

Stored Procedure That Return Dataset(Select Query)

Oct 31, 2006



Hi all,

I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.

Thanks and have a nice day to all.

View 1 Replies View Related

How To Use A Stored Procedure Inside Select Query (sql Server Version 8)

Sep 29, 2007



Hi,
Please help me in this problem...
i am new to sql server..
i am using sql server version 8...(doesnot support function with retun values..)
so i have created a procedure...
-----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
set @myDate = dateadd(dd, 1, @myDate)
end
select @myDate
end
go
[/code]
--------------------------------------------------------
i can able to execute this procedure separately.... working well...
but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
please help me... how to fix this problem...

View 16 Replies View Related

How To Call A Stored Procedure Using C#

Aug 29, 2007

I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
(  @MarketCode  nvarchar(20),  @MarketName  nvarchar(100),  @LastUpdateDate  nvarchar(2),)
ASINSERT INTO Market(  MarketCode  MarketName  LastUpdateDate)VALUES(  @MarketCode  @MarketName  @LastUpdateDate
)

View 5 Replies View Related

How To Call A Stored Procedure In Asp.net

Mar 31, 2006

I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav

View 2 Replies View Related

Call For BCP Within A Stored Procedure

Aug 7, 2001

Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks

View 2 Replies View Related

How Can I Use Stored Procedure To Call Dll

Aug 7, 2001

Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.

View 1 Replies View Related

How Can I Use Stored Procedure To Call Dll

Aug 7, 2001

Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.

View 2 Replies View Related

Call SQL Stored Procedure

Nov 9, 2005

Greetings,

Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.

Many thanks in Advance

View 1 Replies View Related

Stored Procedure Call

Jul 4, 2006

hi,

i created a stored procedure and below is the code segment of it;

create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS


Declare @SQL_INS VarChar(1000)


SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'

Exec (@SQL_INS)

Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.

View 5 Replies View Related

Call Stored Procedure From Another SP

Dec 11, 2007

Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks

View 3 Replies View Related

How To Call Stored Procedure In VC++

Oct 6, 2007



Respected Sir/MAm

i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....

Could you please help me for the correct syntax code with example.


Thank You.
Upali

View 4 Replies View Related

Stored Procedure's Call

Apr 7, 2008

Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure

The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you

View 8 Replies View Related

Asynch Call DTS Or Stored Procedure 1.1

Jul 10, 2006

Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks

View 3 Replies View Related

Call Stored Procedure In Loop

Oct 26, 2006

I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent  method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close();   If so exactly how do I do this? How do I reset the parmaters for the proc?  I haven't done this before where I need to loop through passing parameter to the same proc. thanks    

View 2 Replies View Related

Call Function From Stored Procedure

Jan 19, 2007

Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible.  So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures.  When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno.  I want to put that current batchno into the Update statement and update the record with the current batchno.  Can someone point me in the right direction?  Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View 2 Replies View Related

How To Call A Stored Procedure From A Web Page With VB

Feb 13, 2007

Dear Masters;
How can I call a stored procedure with VB code?
Thanks

View 2 Replies View Related

How To Call Stored Procedure To Table??

Nov 12, 2007

Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you

View 2 Replies View Related

Make A Call To AS/400 Stored Procedure From DTS

Feb 15, 2000

Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.

View 1 Replies View Related

Error Within Stored Procedure Call

Feb 20, 2003

I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):

-- Decalre variables
DECLARE @object INT
DECLARE @hr INT
DECLARE @property VARCHAR(255)
DECLARE @return VARCHAR(255)
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)

-- Create object
EXEC @hr = sp_OACreate 'SQLActiveXTest.SQLActiveXTestClass', @object OUT

-- Call method
EXEC @hr = sp_OAMethod @object, 'getDesc', @return OUT

-- Destroy object
EXEC @hr = sp_OADestroy @object

View 1 Replies View Related

Using A Variable To Call A Stored Procedure Name

Jan 31, 2006

Hello;

I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.

I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.

So how do I refer to a procedures name using a variable?

Here's my code;


Function LieuBen()

MyCalendar = CurrTSCalendar

Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")

End Function

Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String

Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"

Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command

ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"

' Connect to the data source.
objConn.Open ConnectionString

' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn

objConn.MySProc MyCalendar, objRs

objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing


End Function

View 1 Replies View Related

Call Stored Procedure From View

Sep 7, 2006

Hello all,
does anyone know if it's possible to call a stored procedure from a view.

Thnx,
Patrick

View 9 Replies View Related







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