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


ADVERTISEMENT

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

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

Translate This Stored Procedure Call To DDL Statement

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

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

If Statement In Select Of Stored Procedure

Dec 19, 2000

Hi,

Can you use an IF statement in a Select statement within a stored procedure?

If so, how?

Mark

View 1 Replies View Related

Need To Run Stored Procedure In Select Statement

Aug 21, 2000

I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

View 4 Replies View Related

Stored Procedure In A Select Statement

Apr 23, 2007

Hi All,

Can i run a stored procedure with in a SELECT statement
like below?

Select * from EmployeeDetails where EmployeeName in (.. Some Stored procedure to return me some set of Employee Names)

Is there a better way of doing it?
Thanks in advance

vishu
Bangalore

View 2 Replies View Related

Stored Procedure - Using Value From A Select Statement

Jul 30, 2007

Hello,

I have written a stored procedure which has in it a select statement. This returns a single record (I used the "top 1" line to ensure I only get a single record). I want to use a value returned by this statement (not the primary key) further on in the stored procedure - so assigning it to a variable would be handy.

Is this possible? If so, how do I do it?

Thanks.

View 3 Replies View Related

Trying To Use Stored Procedure In A Select Statement

Jul 7, 2006

Hi there, I am trying to return data from a select statement that is running a stored procedure.

I am running on SQL 2000 SP4. I have created a 'loopback' linked server as described in a technet article. (It's pointing at itself) I can then use the following select statement to get data back in the select statement from a stored procedure.

select * from openquery(loopback,'exec DWStaging.dbo.PokerStaging')

I am trying to get data back from my own Stored Procedure, but continue to get the following error:

Server: Msg 7357, Level 16, State 1, Line 1
Could not process object 'exec DWStaging.dbo.PokerStaging'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=exec DWStaging.dbo.PokerStaging'].

If I try the same syntax with the sp_who stored procedure, it works and I get data back fine. But, If I try it with the sp_who2 stored procedure I get the same error as above.

select * from openquery(loopback,'exec sp_who') --> Works fine
select * from openquery(loopback,'exec sp_who2') --> Doesn't work

Does anyone know what the difference is between the Stored Procedures that work with this syntax and those that don't? Is there a way I can change my stored procedure such that it would cause it to work?

PS: The following code was used to create the linked server, and then a security pass though as a certain account was addedDECLARE @provstr varchar (2000)
SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME
EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL',
@provider = 'SQLOLEDB', @provstr = @provstr



View 4 Replies View Related

Stored Procedure In SELECT Statement ?

Nov 8, 2006

Hello,

I want to recover rows returned by a stocked procedure into a select statement, something like this:

select * from (exec sp_helpuser) us where UserName like 'dbo'

This syntax is false. Is there a way to do this?

Thank you

View 6 Replies View Related

How To Put A Select Statement And Get Results In A Stored Procedure

Feb 2, 2008

Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear         varchar(4),@TheMonth      varchar(2)
AS
SELECT   
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode = 1)) AS    Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode = 0)) AS    Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode IS NULL)) AS    Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS    Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode = 1)) AS    Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode = 0)) AS    Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode IS NULL)) AS    Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS    Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode = 1)) AS    Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode = 0)) AS    Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode IS NULL)) AS    Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS    Total_All_Faxes
FROM   CampaignResponse AGO

View 2 Replies View Related

Stored Procedure In SELECT Statement?? Urgent

Sep 26, 2001

I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2

I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.

SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2

Can I use stored proc in this way in SQL-server?

View 2 Replies View Related

Can A Stored Procedure Be Executed From Within A Select Statement?

Dec 2, 2005

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.

View 2 Replies View Related

Results Of SQL SELECT Statement Into Stored Procedure

Jul 23, 2005

I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James

View 2 Replies View Related

Select Statement As Input Variable In Stored Procedure?

May 6, 2015

Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.

ie.

exec sp_SomeFunc 'select * from table1 where id=1'

It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?

View 4 Replies View Related

Problem With The SELECT Statement Of The Stored Procedure In String Comparison.

Dec 5, 2006

 
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I  pass a parameter to it. But if I hard
code it, it gives me the right result.
The I check if the field value of 'Email' is exactly equal to  the parameter '@Email'.
The field 'Email' is varchar, and CID is integer.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn
Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code.
CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com'
return
Can anyone tell me the reason for it.
Thanking you in advance
 
Tomy

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

Stored Procedure Version Of SELECT Statement - Can't Understand Why It's Not Working

Apr 25, 2006

Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:

<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"

DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList>&nbsp;<asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

View 3 Replies View Related

SQL Server 2012 :: Stored Procedure Argument For Select Where Value IN Statement

Feb 17, 2014

I have a stored procedure that ends with

Select columnname from tablename order by ordercolumn

We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.

The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:

Select @userCount = COUNT(*) from permissions where UserID = @userID and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (dbo.sp_FoldersOfFile(@fileID))

The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.

View 9 Replies View Related

T-SQL (SS2K8) :: Stored Procedure And SELECT Statement Return Different Results

Dec 4, 2014

I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:

USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

When I do this in the same SSMS window:

exec mobile.GetAllMobileDeviceUsers

SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION

[Code] ....

I get two result sets. The first is 422 rows; the second is 467 rows. Why?

View 4 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement

Jan 7, 2014

I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).

The structure of the periods table is as follows:

[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL

The stored procedure is currently:

USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] ....

What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.

For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23

So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.

View 1 Replies View Related

How Do I Call A Select Statement Properly When The Field Is A Yes/no?

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

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

Stored Proc Call, 'table Name' As String, And T-sql Statement

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

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







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