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.
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12) AS BEGIN declare @str nvarchar(2000) declare @a varchar(10)
create table #t (paycode varchar(7), hrs varchar(255)) insert into #t exec sp_executesql @str select @a = hrs from #t where paycode='ST' drop table #t print @a
if i call exec sp_execute @str, it will output this PAYCODE HRS ------- -------- ST 08: 30 OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure. HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure. I hope i'm being clear. Any help will be appreciated. Here is an example of how i am finding my number Employee is the name of my table and Number is the name of my column.
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
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
I have a store procedure that works fine when tested in SQL Management Studio and Visual Studio but when I actually running the page in a browser, it does not work. There is no result generated. Below is my store procedure.1 ALTER PROCEDURE [dbo].[spSearch] 2 -- Add the parameters for the stored procedure here 3 @schoolID int = NULL, 4 @scholarship varchar(250) = NULL, 5 @major varchar(250) = NULL, 6 @requirement varchar(250) = NULL 7 --@debug bit = 0 8 AS 9 BEGIN 10 -- SET NOCOUNT ON added to prevent extra result sets from 11 -- interfering with SELECT statements. 12 SET NOCOUNT ON; 13 14 -- Insert statements for procedure here 15 16 Declare @SQL as NVarchar(4000); 17 Declare @Params as NVarchar(3000); 18 Set @SQL = N'SELECT * FROM [scholarship] WHERE [sectionID] = @schoolID'; 19 Set @Params = N'@schoolID int,@scholarship varchar(250),@major varchar(250),@requirement varchar(250)' 20 21 If @scholarship IS NOT NULL 22 Set @SQL = @SQL + N' AND [scholarship].[schlrName] LIKE + ''%'' + @scholarship + ''%''' 23 If @major IS NOT NULL 24 Set @SQL = @SQL + N' AND [scholarship].[Specification] LIKE + ''%'' + @major + ''%''' 25 If @requirement IS NOT NULL 26 Set @SQL = @SQL + N' AND ([scholarship].[reqr1] LIKE + ''%'' + @requirement + ''%''' 27 If @requirement IS NOT NULL 28 Set @SQL = @SQL + N' OR [scholarship].[reqr2] LIKE + ''%'' + @requirement + ''%''' 29 If @requirement IS NOT NULL 30 Set @SQL = @SQL + N' OR [scholarship].[reqr3] LIKE + ''%'' + @requirement + ''%''' 31 If @requirement IS NOT NULL 32 Set @SQL = @SQL + N' OR [scholarship].[reqr4] LIKE + ''%'' + @requirement + ''%''' 33 If @requirement IS NOT NULL 34 Set @SQL = @SQL + N' OR [scholarship].[reqr5] LIKE + ''%'' + @requirement + ''%'')' 35 --If @debug = 1 36 --PRINT @SQL 37 Exec sp_executesql @SQL, @Params, @schoolID, @scholarship, @major, @requirement 38 END 39
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet] GO /****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[usp_sysconf]
In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl? Any help here is appreciated.
How do I assign a temp value to a parameter in a stored procedure? I am trying like so: Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar = "CQ2"AS SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO What is the correct way to assign a temp value for testing purposes in SQL Server 2000 Query Analyzer? When I try: Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar AS Set @partNumber = "10-AF40-N04B-JZ"SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO I get error: Invalid column name '10-AF40-N04B-JZ'.
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'.
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
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
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.
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
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.
Hello evry1. i m new to SQL Server2000. plzz tell me where i m wrong in this SP . this procedure is not giving any error but it is not showing any result. plzz help me n give me any idea if u know how i can solve my problem best regards sadaf n here is the procedure
begin select * from [profile] where [user_name] like '%@user_name1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@gender1 is not null and len(@gender1) > 0) begin select * from [profile] where gender = @gender1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@email_address1 is not null and len(@email_address1) > 0) begin select * from [profile] where [email-address] like '%@email_address1%' and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@relegion1 is not null and len(@relegion1) > 0) begin select * from [profile] where relegion = @relegion1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@political_view1 is not null and len(@political_view1) > 0) begin select * from [profile] where political_view = @political_view1 and place like '%@place%' and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@passion1 is not null and len(@passion1) > 0) begin select * from [profile] where passion like '%@passion1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@sports1 is not null and len(@sports1) > 0) begin select * from [profile] where sports like '%@sports1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@activities1 is not null and len(@activities1) > 0) begin select * from [profile] where activities like '%@activities%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@books1 is not null and len(@books1) > 0) begin select * from [profile] where books like '%books1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@music1 is not null and len(@music1) > 0) begin select * from [profile] where music like '%@music%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if(@tv_shows1 is not null and len(@tv_shows1) > 0) begin select * from [profile] where tv_shows like '%@tv_shows1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@movies1 is not null and len(@movies1) > 0) begin select * from [profile] where movies like '%@movies1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@cuisines1 is not null and len(@cuisines1) > 0) begin select * from [profile] where cuisines like '%@cuisines1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@intrested_in1 is not null and len(@intrested_in1) > 0) begin select * from [profile] where intrested_in = @intrested_in1 and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@education1 is not null and len(@education1) > 0) begin select * from [profile] where education like'%@education1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@college_university1 is not null and len(@college_university1) > 0) begin select * from [profile] where institution like '%@college_university1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@occupation1 is not null and len(@occupation1) > 0) begin select * from [profile] where occupation like '%@occupation1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@industry1 is not null and len(@industry1) > 0) begin select * from [profile] where industry like '%@industry1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@job_desc1 is not null and len(@job_desc1) > 0) begin select * from [profile] where job_desc like '%@job_desc1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end if (@career_intrest1 is not null and len(@career_intrest1) > 0) begin select * from [profile] where career_intrest like '%@career_intrest1%' and place = @place1 and latitude=@latitude1 and longitude=@longitude1 and signup_name != @signup; end GO
i made a new stored procedure and execute it. it works great but where can i see the result ? i want to see the number of Count(*) in a window inside the SQL Server Managment studio, can i ?
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?
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"
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?
I have a stored procedure (a) which calls another stored procedure (b).
Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements
If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.
But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.
Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable
- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.
The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.
----------------- UPDATE -------------- I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.
If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.
This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with
insert into query_log exec remoteServer.master.dbo.##up_fetchQuery
I get execution error
Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure '##up_xportQueryLog'. Database name 'master' ignored, referencing object in tempdb.
When I try
insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure '##up_xportQueryLog'. Database name 'tempdb' ignored, referencing object in tempdb. with
insert into query_log exec remoteServer..dbo.##up_fetchQuery
or
insert into query_log exec remoteServer...##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure '##up_xportQueryLog'.
I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?
Hi All, I have to execute one of the stored procedure within another stored procedure. And have to store it into some table variable... How to do that.pls give me the syntax... Thanks and reagards A
