T-SQL (SS2K8) :: Returning Stored Procedure Results Into CTE Or Temp Table?

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


ADVERTISEMENT

T-SQL (SS2K8) :: Stored Procedure Returning Inconsistent Results?

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

T-SQL (SS2K8) :: Second Resultset Of Stored Procedure Into Temp Table

Jun 6, 2014

I'm working on building a report and asked a developer which table some data comes from in an application. His answer was the name of a 3500 line stored procedure that returns 2 result sets. I could accomplish what I'm trying to do using the second result set, but I'm not sure how to put that into a temporary table so that I could use it.

Here's my plan according to the Kübler-Ross software development lifecycle:

Denial - Ask the developer to make sure this is correct (done)
Despair - Look hopelessly for a solution (where I am now)
Anger - Chastise developer
Bargaining - See if I can get him to at least swap the order that the resultsets are returned
Acceptance - Tell the users that this can't be done at present.

View 3 Replies View Related

Storing The Stored Procedure Results In A Temp. Table

Mar 28, 2008

All,

I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...

"Insert exec cannot be nested"

I suspsect this is because I have a Insert Exec statement inside my stored procedure...

Is there any way to come over this issue ?

syntax of my code...

create table #Temp1 (ID int)

insert into #Temp1
EXEC SP1

when I try to run the above code I get the error "Insert exec cannot be nested"

SP syntax :

Create Procedure SP1
as
Begin


create table #Temp2
statements.....


Insert into #temp2
exec SP<Name>


staments.. cont...

END

View 5 Replies View Related

T-SQL (SS2K8) :: Load Values From Stored Procedure Into A Temp Table?

May 8, 2014

I would like to know if the following sql can be used to obtain specific columns from calling a stored procedure with parameters:

/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP @parm1, @parm2

If the above does not work or there is a better way to accomplish this goal, how to change the sql?

View 1 Replies View Related

Stored Procedure Not Returning Results

May 15, 2007

Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables:  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]


-----------------------------------------------------------------


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]


--------------------------------------------------------------


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


-----------------------------------------------------------


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 And here is the stored procedure that I wrote (doesn't return results):  SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)

AS

BEGIN

SET
NOCOUNT ON

IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN

DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName

SET @ProjID = @DeptCode + '-' + '%'

SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END

END Can someone see what's wrong? Thanks. 

View 7 Replies View Related

Select Script Returning Different Results When Used In A Stored Procedure.

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

SQL Server 2012 :: Stored Procedure Not Running To Completion And Not Returning Results Set

May 27, 2014

I have stored procedure

ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date

--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)

SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC

DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value

It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.

View 3 Replies View Related

T-SQL (SS2K8) :: Call Stored Procedure And Obtain Specific Results

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

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

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

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

T-SQL (SS2K8) :: Returning Results That Fall Within Current Financial Year?

Jun 3, 2014

I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.

Perhaps using the datediff() function, or other functions as required to achieve what I need?

View 3 Replies View Related

T-SQL (SS2K8) :: Prevent SELECT Query From Returning Results Using LOCKS

Dec 2, 2014

I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.

I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??

Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.

View 9 Replies View Related

Temp Table In A Stored Procedure

Jul 23, 2005

I need to select and mark 150 records at a time in a large table.What I'm trying to do is...Select top 150 xxxx into #temp from largeTableupdate largeTable set marked = 1 where xxxx in #tempThis is very simplified, the real procedure is quite large.The Error I am getting isInvalid object name '#temp'.

View 2 Replies View Related

Temp Table In Stored Procedure??

Aug 17, 2007

I'm pretty new with temp tables, i have this code that works fine as a normal query . but i want to put it in a stored procedure, so i can use it more then one. What do i need to change in the code for this to work. I also wanted to add to parematers to it. I know how to do parameters in stored procedures, but im more worried about getting this code in a stored procedure. any help will be greatly appreciated.




Code Snippet

USE fssrc
SET NOCOUNT ON
SELECT cr.sales_entity_code 'Territory' ,
sp.name 'SE',
Date = CONVERT(char(12),DATEADD(day, (qh.cycle_day-1), p.start_date),6),
qh.entity_code 'Customer',
c.name 'Name',
c.address2 'Address2',
c.post_code 'PostCode',
q.question_code 'Question Code',
q.description 'Question',
qt.description 'Response Type',
qh.response 'Response'
INTO #results
FROM question_history qh,
customer_relationship cr,
sales_person sp,
period p,
questions q,
customer c,
question_type qt
WHERE cr.customer_code = qh.entity_code
AND qh.period_code = p.period_code
AND sp.sales_entity_code = cr.sales_entity_code
AND qh.question_code = q.question_code
AND cr.customer_code = c.customer_code
AND q.type_code = qt.type_code
go
SELECT distinct #results.*
FROM #results
ORDER BY #results.DATE, #results.territory, #results.se
DROP TABLE #results

View 2 Replies View Related

Returning Table From Stored Procedure ????

Apr 9, 2008

How to return an entire table from stored procedure ????

thanx in advance

View 4 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

Stored Procedure With Input Going To A Temp Table

Feb 11, 2001

Could someone help me get this stored procedure to work? I want to give the stored procedure a long list of departments and have them added to a temp table. This only gets the first dept. in the temp table. I'm confused. Open to other suggestions, but want to use a 1col temp table to hold the depts.

After this is done, an SQL query is run using the temp table.
Input for test:
--csi_crystal_xxxx "pc9xp,pc8,pc7,pc6,pc6543,pc945678"
--select * from ##CrystalGetCosts

create procedure csi_crystal_xxxx

@DeptResp varchar(4000)
AS
SET NOCOUNT ON
DECLARE @SQL varchar(8000)
DECLARE @Dept varchar(10)
DECLARE @iLen int
DECLARE @iPtr int
DECLARE @iEnd int

If Exists (Select name, Type From [tempdb]..[sysobjects]
where name = '##CrystalGetCosts' And Type = 'U')
Drop table ##CrystalGetCosts

CREATE TABLE ##CrystalGetCosts (Dept_Resp_No varchar(10))
Set @iLen=Len(@DeptResp)
Set @iPtr = 1
While @iPtr < @iLen
BEGIN
SET @iEND = charindex(',',@DeptResp,@iPtr)
Set @Dept= Substring (@DeptResp,@iPtr,@iEnd-1)
INSERT INTO ##CrystalGetCosts Values (@Dept)
Set @iPtr = @iEnd + @iLen
END

View 1 Replies View Related

Stored Procedure Not Deleting The Temp Table

Mar 14, 2008

Hi
I have a search page having four fields. Giving any one of the field as input should retrieve search results in Gridview.
In GridView1 i have child Gridview for displaying details related to Gridview 1.

now i have to write storedprocedure for getting values in two grids.
there is one matching column in two tables i.e CNo.from first select statement we have to capture CNo and basing on that retrieve second table values.

I have a stored procedure for that but my problem is
i stored CNo in temp table i.e @MyTable .after doing select statements from two tables
i want to delete @MyTable. But iam not able to. so pls help me

My stored procedure code is here:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN

DECLARE @MyTable table (CNo varchar(255))



INSERT @MyTable

Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))



--Now do your two selects

SELECT *

FROM customer c

INNER JOIN @MyTable T ON c.CNo = T.CNo


Select *

From refunds r

INNER JOIN @MyTable t ON r.CNo = t.CNo


END

The output of storedprocedure is like this:

Iam getting all the columns of two tables but the CNo column is repeating twice in both the tables.
so please some one help me.
The CNo colum shouldnot repeat.

Thankyou

View 4 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

Stored Procedure Returning Info About A Table

Sep 28, 2007

i want to have a user defined function or stored procedure(i don't know which one should be used, anyway!) which returns false if the table has no records, with the table name comes with a parameter.

could you give a little code sample?

thanx

View 4 Replies View Related

Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?

Jan 10, 2007

I hvae a stored procedure that has this at the end of it:
BEGIN
      EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
 
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?

View 2 Replies View Related

Error In Stored Procedure While Working With Temp. Table

May 31, 2007

Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as :
String or binary data would be truncated.The statement has been terminated.
The procedure i created is as :
ALTER PROCEDURE fetchpersondetails
AS
CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300),
StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200),
Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
 
Insert into #tempperson
SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName,
dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress,
dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress,
dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone,
dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2,
N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email,
dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category,
dbo.tblCountry.countryNameFull AS countryaddress
FROM dbo.tblperson INNER JOIN
dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN
dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
 
please let me know the solurion of this error. 
 

View 2 Replies View Related

How To Store The Output Of Stored Procedure To A Temp Table

Jan 28, 2008

Hi all,

I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.

Simplified SP is as...


Create procedure usp_test
as
begin

select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end

I'm expecting something like this...

declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>

select * from @table1

I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.

Thanks,

View 5 Replies View Related

Stored Procedure Returning An Empty Data Table

Nov 20, 2007

I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports.  More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data.  In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time.  Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data.  Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases.  Might this be causing the problem?Robert W.Vancouver, BC 

View 7 Replies View Related

Creating Stored Procedure With Temp Table - Pass 6 Parameters

Sep 9, 2014

I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:

ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as

Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table

Once I have created the Temp table following query I need to run

SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)

The above query might return null result or a date .

In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"

View 3 Replies View Related

Creating A Dynamic Global Temp Table Within A Stored Procedure

Sep 7, 2006

hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?

View 16 Replies View Related

Transact SQL :: Store Resultsets Of Stored Procedure Returning More Than One Resultset In Two Different Table?

Apr 20, 2015

I have on stored procedure which returns mote than one resultset i want that to store in two different temp table how can achieve this in SQL server.

Following is the stored procedure and table that i need to create.

create procedure GetData as begin select * from Empselect * from Deptend 
create table #tmp1 (Ddeptid int, deptname varchar(500),Location varchar(100))
Insert into #tmp1 (Ddeptid , deptname ,Location )
exec GetData

create table #tmp (empid int , ename varchar(500),DeptId int , salary int)
Insert into #tmp (empId,ename,deptId,salary)
exec GetData

View 9 Replies View Related

SQL Server 2012 :: Avoiding Temp Table With String Of IDs Passed Into Stored Procedure

Sep 26, 2014

Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.

Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] .....

The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] ....

The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?

View 9 Replies View Related

Transact SQL :: Getting Data From External Assembly Into Temp Table Inside Stored Procedure

Oct 8, 2015

I have a Custom .net assembly which retrieves some data, basically just a set of data containing, ID, value,

I need my stored procedure to return this data as a table.

My question what/how is fastest way to do this, my assembly can return anything you want, a datatable, a hashtable, list of(class) etc..

View 3 Replies View Related

Problem In Executing Stored Procedure With Temp Table Returns Result Sets

Mar 23, 2006

Hi,
we are facing problem in executing a stored procedure from Java Session Bean,

coding is below.

pst = con.prepareStatement("EXEC testProcedure ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pst.setString(1, "IN");
//
rs = pst.executeQuery();
rs.last();
System.out.println(" Row Number "+rs.getRow());
rs.beforeFirst();
while(rs.next())
{
System.out.println(" Procedure is "+rs.getString(1));
}

same sp working perfectly with SQL Server 2000

am getting a error message

com.microsoft.sqlserver.jdbc.SQLServer
Exception: A server cursor cannot be opened on the given statement or statements
. Use a default result set or client cursor.



If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.

SP :

create proc testProcedure
@countrCode varchar(3)
as

select countryname INTO #TMPCOU from country where countryCode = @countrCode
SELECT COUNTRYNAME FROM #TMPCOU

Its really very urgent. Please help me...!

Rgds,

Venkatesh.

View 2 Replies View Related

How To Stored A Stored Procedure's Results Into A Table

Jul 23, 2005

Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related







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