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
ADVERTISEMENT
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
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
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
May 21, 2006
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)
@a = Select * from foo ---Obviously mine is much more robust
How do I execute @a to actually Select * from foo?
View 3 Replies
View Related
Oct 8, 2004
Two questions - one slightly off topic:
1) How do I write a stored procedure in MS SQL so that it returns a results set?
2) How do I get this into ASP.NET so that I can put the values into controls?
Jags
View 1 Replies
View Related
Sep 30, 2006
I need to return the results of a stored procedure into a temporary table. Something like this:
Select * into #temp from exec (the stored procedure). It appears that I can not do this.
The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)
insert into (...) exec (the stored procedure.
Does anyone have any ideas how I could do this.
View 4 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
Aug 1, 2006
I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================
ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here
@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...
AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?
AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...
RETURN @WinnerID
END
It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page
Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content>
View 3 Replies
View Related
May 27, 2015
How can I return results from this SP?
Alter
Procedure sp_Blocking
as
SET NOCOUNT
ON
truncate
table blocked
[Code] ....
View 4 Replies
View Related
Mar 11, 2015
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
View 6 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
Apr 17, 2014
I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:
DECLARE@return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:
1. How can I have the stored procedure return distinct rows?
2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".
when I change the sql above to:
DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName
That is when I get the error message.
A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.
View 3 Replies
View Related
Oct 1, 2014
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
View 9 Replies
View Related
Jul 23, 2005
I'm sure this is an obvious question but much of SQL is new to me.I have a stored procedure and I want to use the results of a selectstatement elsewhere in the stored prcedure. The select statement willreturn at most one record and only one column, here's an example :select top 1 Sequence from MyTablewhere ParentId=4 and Sequence > 200 order by sequenceCan I put this result in a variable? Do I have to use SELECT INTOTempTable and refer to TempTable and then drop the table at the end ofthe stored procedure? I have read a little about cursors, are thesethe way to go?I'm confused as to what my options are, any help or links to help willbe appreciated.Thanks for reading.
View 1 Replies
View Related
Oct 8, 2007
Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me.... Thank
View 4 Replies
View Related
May 18, 2004
Hi,
Can anyone tell me how to access the results from a select statement within the stored procedure?
This is to implement auditting functionality, before updating a record i want select the original record and compare the contents of each field with the varibles that are passed in, if the fields contents has changed i want to write an audit entry.
So i'd like to store the results of the select statement in a variable and access it like a dataset. e.g
declare selectResult
set selectResult = Select field1,field2,field3 from table1
if selectResult.field1 <> @field1
begin
exec writeAudit @var1,@var2,var3
end
Many thanks.
View 4 Replies
View Related
Aug 29, 2005
I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen. So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info. Any idea y? They both should be bringing back information from the same table not one from my live DB and one from my TEST DB. Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows: CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement: SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid
View 2 Replies
View Related
Jan 10, 2006
Hi.
I am new to SQl server. I would like to write a query with the follwing logic:
---------
Select department from table1;
then insert each result row into a table with in the same query.
---------
View 2 Replies
View Related
Aug 21, 2007
Is there a way to select the results of an SP into a variable.
For example:
DECLARE @X varchar(1000)
SELECT @X= sp_who2
How do I do this?
View 3 Replies
View Related
Apr 29, 2015
I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
[Code] ....
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
View 8 Replies
View Related
Nov 20, 2007
I have a script with a number of different Left Joins. When I run it in Query Analyzer the Left Join works as a normal join and suppresses some rows. However when the same code is used in a stored procedure the correct results are produced when the stored procedure is executed.
Can anyone tell me what is causing this?
SELECT USR.ROLEUSER
,PER.NAME
,EMP.PAYGROUP
,USR.ROLENAME
,OPR.EMPLID
,OPR.OPRDEFNDESC
,OPR.ACCTLOCK
,OPR.ROWSECCLASS
,EMP.JOBCODE
,JCD.DESCR
,EMP.DEPTID
,EMP.DEPTNAME
FROM PSROLEUSER USR
JOIN
PSOPRDEFN OPR
ON
USR.ROLEUSER = OPR.OPRID
LEFT OUTER JOIN
PS_PERSONAL_DATA PER
ON
OPR.EMPLID = PER.EMPLID
LEFT OUTER JOIN
PS_EMPLOYEES EMP
ON
OPR.EMPLID = EMP.EMPLID
LEFT OUTER JOIN
PS_JOBCODE_TBL JCD
ON
JCD.SETID = 'RBLTT'
AND EMP.JOBCODE = JCD.JOBCODE
AND JCD.EFFDT =
(SELECT MAX(EFFDT)
FROM PS_JOBCODE_TBL
WHERE JCD.SETID = SETID
AND JCD.JOBCODE = JOBCODE
AND EFFDT <= GETDATE())
WHERE OPR.ACCTLOCK = 0
AND USR.ROLENAME = 'RBL MANAGER'
AND EMP.PAYGROUP NOT IN ('RBA', 'RMA')
View 2 Replies
View Related
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
Aug 19, 2015
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
View 17 Replies
View Related
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
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
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
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
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
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
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
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
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
